VBA + Data Validation (list) + Save in .pdf

[cyapur]

Hello world !

I am trying to save automatically a set of feedback reports in .pdf(1 page each feedback report). Each report has a unique name and it gets his data from formulas, pivot tables, etc. The thing is, when I change the name all the data of the report changes (because it's another person, so it's another feedback report). If I want to save each report in pdf I have to do each per each. So, how can I do this with a macro in order to save ALL the reports in .pdf with just a few clicks?.

In a specific example, how can I save this (Files attached) in.pdf (with a macro) those three reports (f3.PNG) with their specific data (f1.PNG v/s f2.PNG)

Regards ! :D

PD: I know that I can do each per each in the example, but in the "real" example are like 150 reports.. haha D:

 

[Monty]

Hello Cyapur.

Hope this helps.

Sub CreatePDF()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "No PDF file Created"
    Resume exitHandler
End Sub

 

[vletm]

Use [ CODE ] & [ /CODE ] tags to embed your VBA Macros

 

[Monty]

Yes...Sorry i missed it..Will be taken care.

 

[cyapur]

Monty, thanks for your answer !

I have tried your code and modified a little, but it still not working.

My idea is the next:

The three reports (Chris1, Huss2, Philip3) have to be saved automatically in separated pdfs, each one with the name of the person. For example: Chris1.pdf; Huss2.pdf; Philip3.pdf in a specific destination. I have done an UserForm for this:



Everything works except the bottom save ("Guardar"). The big problem I have is the list. The bottom 'save' has to save each report (Chris1, Huss2, Philip3) from the list in separate pdfs (Chris1.pdf; Huss2.pdf; Philip3.pdf) in a specific destination (the destination is provided by the user, in the UserForm).

Anybody have any idea how to do this ?

Regards !

PD: Consider that each report has different values as showed in the files uploaded in the first post ! (For that is the list)

 

[vletm]

@cyapur
if 'automatic' ... why need to give path? this is not automatic!
Why those paths cannot be ready?
Cannot You send a sample file of Your Excel-file?
and
when/which case would that real automatic feedback do?
... after something save or what/which triggers it?

 

[cyapur]

@cyapur
if 'automatic' ... why need to give path? this is not automatic!
Why those paths cannot be ready?
Cannot You send a sample file of Your Excel-file?
and
when/which case would that real automatic feedback do?
... after something save or what/which triggers it?

I have uploaded the sample for you.

Hope you can help me !

Regards

 

[vletm]

There were also three questions ...
so try to continue.
This is not fully automatic,
because have to press [Save].
< NOTE > I didn't test all cases ...

 

[cyapur]

There were also three questions ...
so try to continue.
This is not fully automatic,
because have to press [Save].
< NOTE > I didn't test all cases ...

I didn't understand your questions actually. If with 'path' you mean the list of names, because If I need to check a specific report it will be easier with the list. What do you mean with "why those paths cannot be ready?"

The only automatic thing is convert each report in pdf file in a specific carpet, because it is very tricky to convert each report manually.

Regards !

PD: For what did you created another sheet? ("inits")?

 

[vletm]

Your 'automatic' system needs that someone would ALWAYS fill path, am I correct? That is far far from any 'automatic'! Path is like c:/data/chris
If those paths are READY written in 'init'-sheet then NO NEED TO FILL after that!
This version works also without those preset-paths ... then path would be 'activepath'.
Just fill or do not fill those paths (right side from name) to 'init'-sheet
and press [SAVE]-button ..
after that it would do all reports (better if there are those paths).
The report is that 'left-top-corner' ... okay?