VBA save problem

[chippy36]

Hello All,

I have a VBA code that is written to save the cells from range "G66:K95" of the currently active sheet as a PDF file. The name of the sheet is used as the file name, and the file is saved in a subfolder within the same directory as the workbook.

It all works well EXCEPT, the file is not saved to a subfolder within the same directory as the workbook, instead it is saving to the last location that any file was saved to. Anyone got any ideas ? Thanks in advance


Save the selected cells as a PDF file with the sheet name as the file name
Dim sheetName As String
sheetName = ActiveSheet.Name
Dim savePath As String
savePath = ThisWorkbook.Path & "\Variations\" & sheetName & ".pdf"
ActiveSheet.Range("G66:K95").ExportAsFixedFormat Type:=xlTypePDF, _
fileName:=sheetName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

 

[Micron]

That should always save in the folder named Variations. Maybe provide some details about the expected path and more clues about what happens. You mean if you saved it in Test folder last it goes there but if you saved in Testing it goes there next time?
Wondering if you should be using fileName:=savePath and not fileName:=sheetName ?

 

[chippy36]

That should always save in the folder named Variations. Maybe provide some details about the expected path and more clues about what happens. You mean if you saved it in Test folder last it goes there but if you saved in Testing it goes there next time?
Wondering if you should be using fileName:=savePath and not fileName:=sheetName ?

Thanks for the reply, for context. I have the same workbook multiple times, let's say the workbook lives in Folder 1 and in Folder 2 and in Folder 3. Each Folder has a sub folder named variations. If I open the workbook in Folder 2 and run the macro, i want the generated pdf to be saved in the sub folder Variations, inside Folder 2. If i I then open the workbook from inside folder 3 and run the macro, i want the generated pdf tp save into the sub folder variations, inside Folder 3. At all times i want the PDF to be saved as the current sheet name

 

[chippy36]

Thanks for the reply, for context. I have the same workbook multiple times, let's say the workbook lives in Folder 1 and in Folder 2 and in Folder 3. Each Folder has a sub folder named variations. If I open the workbook in Folder 2 and run the macro, i want the generated pdf to be saved in the sub folder Variations, inside Folder 2. If i I then open the workbook from inside folder 3 and run the macro, i want the generated pdf tp save into the sub folder variations, inside Folder 3. At all times i want the PDF to be saved as the current sheet name

To add to that, if I open the workbook in Folder 1 and run the mac, it will save the pdf in the variations folder in folder 1. if I then close everything down and then open the workbook in folder 3 and run the macro, it will revert to saving the pdf in the variations folder in Folder 1

 

[Micron]

And this?

Wondering if you should be using fileName:=savePath and not fileName:=sheetName ?

Can't see enough of the code to be certain. Perhaps don't use ActiveWorkbook, use ThisWorkbook.

 

[chippy36]

Hi,

I’ll try to elaborate

We have 3 folders in a company, called Job1, Job2 & Job3

Inside each folder is a Folder called “Variations” and inside that folder is a workbook called “Calculator”, So we have Job1/Variations/Calculator.xlsm. Job2/Variations/Calculator.xlsm etc etc

Each time we get a new job we duplicate folder Job1, rename it to Job4 (or whatever is next) and in that folder there is already a folder called variations and a workbook called “Calculator”

What I am trying to achieve, is when I open the “Calculator” workbook from the Variation folder inside Job1, the resulting saved Pdf is saved in the same folder as where the workbook was opened from (or saved in ). So, open the workbook from Job1/Variations/Calculator.xlsm and the file will save inside Job1/Variations

Open the workbook from Job3/Variations/Calculator.xlsm and the file will save inside Job3/Variations.

The file is to save in the same folder as where the workbook being used is.

What is happening in practice, is the file is being saved in (on a PC, into the users “documents folder) and (on a Mac, into the last folder the Mac used to save anything)

The command [savePath = ThisWorkbook.Path & "\Variations\" & sheetName & ".pdf"] is basically being ignored

 

[AC PORTA VIA]

Where is your macro located? Personal workbook or somewhere else?

 

[chippy36]

In the Calculator workbook

 

[AC PORTA VIA]

Calculator gets duplicated each time you make new folder, correct?
in that case this should work

Dim sheetName As String
sheetName = ActiveSheet.Name
Dim savePath As String
savePath = ThisWorkbook.Path & "\" & sheetName & ".pdf"
ActiveSheet.Range("G66:K95").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=savePath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

 

[chippy36]

Dim sheetName As String
sheetNam

Calculator gets duplicated each time you make new folder, correct?
in that case this should work

Dim sheetName As String
sheetName = ActiveSheet.Name
Dim savePath As String
savePath = ThisWorkbook.Path & "\" & sheetName & ".pdf"
ActiveSheet.Range("G66:K95").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=savePath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Thank you that works a treat on the PC. Would you know why when I run it on a mac, I get - application-defined or object-defined error at the following piece of code

ActiveSheet.Range("G66:K95").ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=savePath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False