Changing File Format Causing Change in Location of Saved File

[Chiznuggets]

Hello friends,

I'm trying to save an Excel file with VBA code to the location C:\Users\Work\Desktop\Google Drive.

I am able to successfully do this with

ActiveWorkbook.SaveAs ("C:\Users\Work\Desktop\Google Drive\" & varEventName & " " & varDate & " " & varReportType & ".xlsx")

However when I go to change the file format with

ActiveWorkbook.SaveAs FileFormat:=51

the saved file location changes to a different location, C:\Users\Work\Desktop\June Monthly Reports.

Any thoughts on how to fix this problem?

Just to be clear I know the second line of code that I provided is the problem because I stepped through the code one line at a time.

 

[mumps]

Try it all on one line:

ActiveWorkbook.SaveAs ("C:\Users\Work\Desktop\Google Drive\" & varEventName & " " & varDate & " " & varReportType & ".xlsx"), FileFormat:=51

 

[Fluff]

Not usre why you're doing it twice, why not just

ActiveWorkbook.SaveAs "C:\Users\Work\Desktop\Google Drive\" & varEventName & " " & varDate & " " & varReportType & ".xlsx", 51

 

[Chiznuggets]

Thank you for the quick replies.

The reason I don't have it all in one line is that I would like to change the name of the sheet. Here is how I have the code organized currently

ActiveWorkbook.SaveAs ("C:\Users\Work\Desktop\Google Drive\" & varEventName & " " & varDate & " " & varReportType & ".xlsx")

    
ActiveSheet.Name = ActiveSheet.Range("m2")
Columns("K:N").Select
Selection.EntireColumn.Delete




Range("a1").Select


ActiveWorkbook.SaveAs FileFormat:=51

This is how I've made the code change the name of the file and then change the sheet name separately.

 

[Fluff]

As you are not changing the name of the file or it's location, then change the sheet name & delete the columns first.