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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top