PDA

View Full Version : Mac excel macro pdf export



johhnn
03-24-2018, 03:59 AM
Hi all,

Im having some troubles with using VBA on MAC(Yosemite 10.10.5). Im new to the coding and I need an macro for excel 2011 (14.1.0). Does anyone know a macro that does the following thing in excel:

-Export the excel sheet (with print range) to pdf.
-Export to a specific map.
-The file named based on the value of a cell.
-Open the PDF file on screen. (somehow the code openafterpublish didn't work in my code)

Much appreciated,

John,

Logit
03-24-2018, 09:52 AM
.
This macro works on my PC .. not certain if it will work on your MAC :





Sub Export_Worksheet_to_PDF()


Dim mypath As String, fname As String, dname As String
mypath = ThisWorkbook.Path
dname = Format(ActiveSheet.Range("J2").Value, "MM-DD-YYYY")
fname = CreateObject("Scripting.FileSystemObject").GetBaseName(dname)
ActiveSheet.Range("A1:J10").ExportAsFixedFormat 0, mypath & "\" & fname, OpenAfterPublish:=True


End Sub

johhnn
03-31-2018, 12:43 AM
Thank you for the response. I have tried your Macro, but unfortunatly I get an error. It probably has to do with me using a MAC..

Run-time error '429':
ActiveX component can't create object

After 'debugging' it highlights: fname = CreateObject("Scripting.FileSystemObject").GetBaseName(dname), do you have an idea how to fix this and make the macro work?

Logit
03-31-2018, 08:53 AM
.
I suspect it probably has to do with ("Scripting.FileSystemObject"). With Macs and PCs designed differently.

Sorry I am not familiar with MACs. Perhaps someone else on the FORUM can assist.