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,084,738
Messages
5,379,524
Members
401,611
Latest member
CandaceR68

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top