VBA to CreateBackup=True not working for SaveAs .CSV - Any ideas Why?

[mardeebo]

I don't know if I have a server problem, a macro problem, or an excel version problem. Perhaps you can help me figure this out.

Let's say I have a workbook called "DataLoad.xls" with two tabs "Variables" and "CSV_Out".

The user makes a number of updates to the Variables tab that are reflected in a table format on the CSV_Out tab. User then clicks a Macro Button to do the following:

1. Copy the CSV_Out tab to a new workbook
2. SaveAs the CSV_Out file as LOAD.CSV to a different location
3. Automaticially create a backup of the .CSV in the same location as Step 2.

Here's some code that does those steps:

Sheets("CSV_Out").Select
Sheets("CSV_Out").Copy
ActiveWorkbook.SaveAs Filename:= _
"\\server\Data Loads\LOAD.csv", FileFormat:=xlCSV, _
CreateBackup:=True
ActiveWorkbook.Save
ActiveWindow.Close

The macro is NOT creating a "Backup of LOAD.xlk" when i try to save this to the server. If I replace the path name with my local "My documents" folder, it DOES create a "Backup of Load.xlk" file.

I can't figure out why it'll create a backup on my computer but not on the server.

I verified that I can create .xlk files on the server by simply opening my DataLoad.xls file and doing a SaveAs with the option "always create a backup" checked. I do get a "Backup of DataLoad.xlk" saved on the server.

Do you have any ideas why I can't create a backup automatically of a CSV file on the server but I can when it's a .xls file?

We are on Office 2010 but in this case, the DataLoad.xls file opened/saved as an Excel 2003 .xls file.

Thanks to anyone who read through this whole thing....

 

[pschommer]

I was so excited when I came across your post as I have the exact same problem. I copy a sheet to its own workbook and export it as a CSV. Even with CreateBackup set to TRUE, as you have in your example, no backup is made. I realize this is nearly a 5-year-old post, but I thought I would check in to see if you figured anything out.