File saving to my documents rather than File path inserted.

[Yuliko84]

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

 

[mole999]

you need to code your path into the statement, you shouldn't need to actually redirect it via ChDir

so

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)

    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]

Thank You Thank You Thank you!
Worked without problems!

 

[Yuliko84]

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]

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]

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