File saving to my documents rather than File path inserted.

Yuliko84

New Member
Joined
Jun 12, 2015
Messages
4
Hi there,

This is my first post so apologies for any double-ups.

The current Macro I am using to save a sheet in PDF to a certain location seems to change the location based on my last saved location, even though it has a location where I would like to save it to indicated. Can anyone please assist if I am missing something?

Sheets("00373 Ballarat Dealer").Select
Range("A1:P181").Select
ChDir "I:\PARTS Master\6. Parts Support\Parts Reports\Dealer Business Plans\DEALER BUSINESS PLANS PDF - Do not remove"
'

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
you need to code your path into the statement, you shouldn't need to actually redirect it via ChDir

so

Code:
Dim FilePathHome as String
FilePathHome = "I:\PARTS Master\6. Parts Support\Parts Reports\Dealer Business Plans\DEALER BUSINESS PLANS PDF - Do not remove"

My current example for going to the desktop (though I am exporting excel)
Code:
    Desktfile = Environ("UserProfile") & "\Desktop\"    '& RN & "for " & FD & " updated " & TD & ".xlsx"
    DstFile = RN & "for " & FD & " updated " & TD

With ActiveWorkbook
        .SaveAs Filename:= _
                Desktfile & DstFile, FileFormat:=50, CreateBackup:=True
        '.Close SaveChanges:=False
    End With

'51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
 

Yuliko84

New Member
Joined
Jun 12, 2015
Messages
4
Actually - I may have typed too soon!
It is NOT working and now saving them in the I: drive only, and not following the path I have indicated.

Can you please confirm if I did this correctly? I have updated as per instructions, but I don't know how to fix this!

Sheets("00373").Select
Range("A1:P181").Select
FilePathHome = "I:\PARTS Master\6. Parts Support\Parts Reports\Dealer Business Plans\DEALER BUSINESS PLANS PDF - Do not remove"
'

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
 

Yuliko84

New Member
Joined
Jun 12, 2015
Messages
4
Actually - I may have typed too soon!
It is NOT working and now saving them in the I: drive only, and not following the path I have indicated.

Can you please confirm if I did this correctly? I have updated as per instructions, but I don't know how to fix this!

Sheets("00373").Select
Range("A1:P181").Select
FilePathHome = "I:\PARTS Master\6. Parts Support\Parts Reports\Dealer Business Plans\DEALER BUSINESS PLANS PDF - Do not remove"
'

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
 

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
If your doing
FilePathHome = "I:\PARTS Master\6. Parts Support\Parts Reports\Dealer Business Plans\DEALER BUSINESS PLANS PDF - Do not remove"

then
FilePathHome

has to be in here somewhere

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

possibly

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FilePathHome & "\" &sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,509
Messages
5,832,163
Members
430,113
Latest member
CoeurDeLion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top