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,081,909
Messages
5,362,016
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top