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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mole999

Moderator
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

Moderator
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,091
Members
414,122
Latest member
eazyyexcel

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