Differences between Excel files when using VBA to automatically save workbook

Chiznuggets

New Member
Joined
Jun 26, 2019
Messages
8
Hello,

I have written/recorded a long VBA code to reformat data. I have two versions of this code. One version automatically saves the file to a specific folder and reformats the data while the other just reformats the data.

Here's the code I used to save the workbook:
Code:
ActiveWorkbook.SaveAs ("C:\Users\Work\Desktop\June Monthly Reports\" & varEventName & " " & varDate & " " & varReportType & ".xlsx")

The files that I use the "save as" code on end up about 10,000 bytes smaller than the files I use the other code on. When I open the excel file that I use the "save as" code on in google sheets none of the formatting carries over. (All borders, column and row resizing, bolding not present.) While the "non-save as" code carries the formatting over to google sheets. Could this be due to the smaller file size?
FKShJCa


Just to be clear I'm using the two VBA codes on identical data sets and saving them in identical file locations. In addition, the formatting for the "save as" code does show up properly in the excel file itself.
FKShJCa


Any ideas would be greatly appreciated!:)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Chiznuggets

New Member
Joined
Jun 26, 2019
Messages
8
Solved

The problem was that the ActiveWorkbook.SaveAs code changed the file extension but didn't change the file format. Don't ask me what the difference between the two is but the mismatched caused problems. The file was still in it's original .csv format while the extension had been changed to .xlsx. Adding the code
Code:
 ActiveWorkbook.SaveAs FileFormat:=51
solved the problem. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,441
Messages
5,642,133
Members
417,258
Latest member
amk1979

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