200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Dear Experts,
I have used macro to be able to convert excel file to pdf file.
Sub MacroforPDF()
ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, _
Filename:="C:/Users/pingj/Desktop/Test.pdf", _
OpenAfterPublish:=True
End Sub
However, facing error during the macro run, kindly suggest.
Thanks and Regards,
Akash Sharma
Hello Akash,
When I copied your code to a workbook of mine it came up all red, meaning wrong syntax. But when I typed the same code again - actually I copied and pasted each parameter one by one - my Excel approved. Of course, the code couldn't run on my Windows computer because you used the slash as path separator instead of the back-slash which is required by Windows.
I don't think that is the error you encountered. Instead, I think that your code was written on and for a Mac and probably contains an invisible character peculiar to Mac which I inadvertently deleted when I copied and pasted the arguments. However, if you do have a syntax error I suggest that you just do as I did: just write everything again.
After I fixed the syntax error the code still couldn't tun. I got an error saying that the variable xlTypePDF wasn't declared. xlTypePDF is an enumeration probably defined in the PDF add-in which contains the method ExportAsFixedFormat. This method isn't part of a standard Excel installation. You will need to download and install it.
I didn't do that. Therefore I didn't actually test your code. However, I checked the code with this site and think it is correct. Interestingly, the sample code at this link is wrong. For one, it lacks commas between the arguments. More significant is the use of the DisplayFileAfterPublish parameter. It isn't listed as one of the available parameters for this method but my Excel didn't complain about it. Perhaps it is a parameter from an older version of Excel, no longer listed but still working, or belongs to VB which is sometimes different from VBA. Since this parameter is optional you can test your code without it.
The code below should (not tested) work on a Windows computer if the PDF add-in mentioned above is installed.
Sub MacroForPDF()
' 11 Nov 2018
Const Fn As String = "Test" ' change as required
Dim Path As String
Path = Environ("USERPROFILE") & "\Desktop\"
ActiveSheet.ExportAsFixedFormat _
Type:=x1TypePDF, _
Filename:=Path & Fn & ".pdf", _
OpenAfterPublish:=True
End Sub