Macro excel to PDF and more

June 1, 2016 at 12:34:34
Specs: Windows 7
I'm needing to create a macro so that excel auto saves as a PDF given a range of cells (so that it'll only save pg 1 of the document and not the whole sheet). I've got this portion figured out but I also would like a specific cell to be the filename that gets saved. Also, is it possible to have the PDF automatically open after saving? I know you can do that in excel when not using a macro, but is it possible with doing the macro also?

This is what I have so far...

Sub SaveAsPDF()
Dim strFileName As String
strFileName = Application.GetSaveAsFilename(FileFilter:="PDF files (*.pdf), *.pdf")
If strFileName <> "False" Then
Sheets("Sheet1").Range("A1:E50").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName
End If
End Sub

I know NOTHING about VBA's or macros and have derived this from many google/youtube searches. Hopefully this isn't too much to ask...


See More: Macro excel to PDF and more

Report •


#1
June 1, 2016 at 15:34:59
Try this...

Sub SaveAsPDF()
Dim strFileName As String
Dim myShell As Object
'Use A1 value as filename
    strFileName = Range("$A$1")
'Export Range
    Sheets("Sheet1").Range("A1:E50").ExportAsFixedFormat _
           Type:=xlTypePDF, Filename:=strFileName
'Open PDF
    Set myShell = CreateObject("WScript.Shell")
    myShell.Run strFileName & ".pdf"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 1, 2016 at 15:41:32
When I put that in, it brought up an error. When I click debug, it highlighted this line.

myShell.Run strFileName & ".pdf"


Report •

#3
June 1, 2016 at 17:18:25
Macros can throw lots of different errors. What did the message say?

BTW Unless you changed it, the code uses the value in A1 as the filename. Is the value in A1 a value that can be used as a valid filename?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 1, 2016 at 18:30:24
BTW...This code does not check to see if the file already exists. It will simply save the file and overwrite any existing file with the same name. There are ways to check for the filename before saving if need be.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 2, 2016 at 07:35:51
Yup, the A1 was my problem. Thank you! How would I have it check to see if there is already a file with the same name before saving? Also, how does it know where it is saving it to?

Report •

#6
June 2, 2016 at 09:09:02
This is a file that will be sent out to multiple people to use under their user name on separate computers. Can I set where is saves? Or if not, can I just have it open and then individuals can save from the PDF? I greatly appreciate your help!

Report •

#7
June 2, 2016 at 10:42:18
You have brought up multiple issues which may require different answers.

Let's start with this:

This is a file that will be sent out to multiple people to use under their user name on separate computers.

In order for them to use this code, they will have to have macros enabled on their system. This is not something that you can do "remotely" via code since that would be a major security issue. Imagine if I could send you a workbook that included a macro with malicious code along with code that enabled macros on your system without your
permission. That would not be a good thing.

Now, there are tricks that can be used within a workbook to prompt the user to enable macros. One such trick is to hide all worksheets except for one worksheet with a text box that says something like "This workbook is of no use unless macros are enabled. Please close this workbook, enable macros and then re-open this workbook."

Included in the workbook could be a Workbook_Open macro that (assuming macros are enabled) unhides all the important worksheets and hides the worksheet with the text box. With that method, the user will have to make the conscious decision to enable macros if they want to use the workbook.

How would I have it check to see if there is already a file with the same name before saving?

My first question back to you is: Where to want to look for the file? One method for determining if a file exists is to have code that tries to open a file, but with an error handling routine within the code. If the "fileopen" instruction fails because the file does not exist, then the error handler instructions can tell the code what to do next. The thing is, you need to tell the "fileopen" instruction where to look for the file. More on that later, after I address another issue.

Can I set where is saves?

Yes, assuming you know the directory structure of the user's computer. For example, this code sets the path for the export of the PDF and then opens it form the same location. By using CurDir the code uses the default directory that the system is using to save files. You could also build your own string to use as the path, e.g.

strFilePath = "C:\users\ExcelFiles\username\etc"

The following code set the path for saving the file and then for opening it from the same place.

Sub SaveAsPDF()
Dim strFileName As String
Dim strFilePath As String
Dim strPdfPath As String

'Set File Path to default user directory
    strFilePath = CurDir()
    
'Use A1 value as filename
    strFileName = Range("$A$1")
    
'Build path to PDF
    strPdfPath = strFilePath & "\" & strFileName
    
'Export Range
    Sheets("Sheet1").Range("A1:E50").ExportAsFixedFormat _
           Type:=xlTypePDF, Filename:=strPdfPath
           
'Open PDF
    Set objShell = CreateObject("Shell.Application")
    objShell.Open (strPdfPath & ".pdf")
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
June 2, 2016 at 14:07:43
Will this code look for other files in the folder with the same name? What will it do if there is already a file with the same name? Will it pull up an alert box or something?

I'll just have to leave the code to save to the default as I won't know what the structure of the user's computer is.


Report •

#9
June 2, 2016 at 14:10:03
I've put it in, but when I run it, it'll just keep saving the new one over the old one...

Report •


Ask Question