VBA Save As Macro, Returning Run-time Error 5 on other machines

rhhvbanovice

New Member
Joined
Aug 5, 2009
Messages
3
Hey,
New to VBA, mostly recording macros then editing what I can. I am trying to have a macro save a file as a PDF and extract the file name from a cell within the worksheet. The code works great on my computer but when I try to use it on any other computer it Returns run-time error 5. Here is the code, anything jump out?

Public Sub SaveAs()
'
' SaveAs Macro
Sheets("Report").Select
ChDir "\\JGNETWORK\jgmain\Personal Financial Statements"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\JGNETWORK\jgmain\Personal Financial Statements\" & Range("B5").Text, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub


Thanks!
 

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
I'd bet that the following directoryis not accessible on the other users machines..

Code:
"\\JGNETWORK\jgmain\Personal Financial Statements\"
 

rhhvbanovice

New Member
Joined
Aug 5, 2009
Messages
3
I believe you are correct. The important thing, though, is that it generate the PDF with file name. Is there a location that I could enter that I could be sure would generate the file on all machines? Is "C:\" my only option?
 

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
If that's the only reliable storage device then.. yes... you can always check to see if the path exists, and then if not, place it in a local directory, such as c:\PDFS
 

rhhvbanovice

New Member
Joined
Aug 5, 2009
Messages
3
I tried not specifying a path and on my computer it defaulted to Documents, yet on the other computer it still returned Run-time Error 5. If the cell I am using as a the file name contains a function would that effect the macro?
 

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
Lose the following statement

Code:
ChDir [URL="file://\\JGNETWORK\jgmain\Personal Financial Statements"]\\JGNETWORK\jgmain\Personal Financial Statements[/URL]
 

Forum statistics

Threads
1,085,372
Messages
5,383,262
Members
401,820
Latest member
RustEE2020

Some videos you may like

This Week's Hot Topics

Top