Search code examples
vbaexcelsave-as

Save CSV file to another location without opening the new file

I dont have much VB experience, but i tries to do a simple thing.

I need a open excel worksheet to copy itself to another location. Then It should just continue with the existing worksheet and not open the copy that it has made.

I have used this code for now:

Sub savecsv ()
ActiveWorkbook.SaveAs "C:\test\testing.csv", FileFormat:=6
End Sub

This code saves the worksheet to a new location, but it also opens the new one. I have googled a bit and found out that i can use SaveCopyAs to obtain my goal, so i have tried to edit the above code to that, but then i get the error: "Compile Error: Wrong number of arguments or invalid property assignment"

Can someone help me with a code that works? And also a way to owerwrite the file it creates automaticly and not ask if i want to owerwrite the existing file.

Solution

  • The .saveCopyAs command requires only the file name, not the file type. So you can simply use

    ActiveWorkbook.saveCopyAs "C:\test\testing.csv"

    The error message you were getting was caused by the second argument you tried to pass. A copy is just a copy - no changes at all.

    In order to overwrite an existing copy without a warning, turn off alerts:

    Application.ScreenUpdating=False
Application.DisplayAlerts = False     ' so you can overwrite without warning
ActiveWorkbook.SaveCopyAs "C:\test\testing.csv"
Application.DisplayAlerts = True
Application.ScreenUpdating=True

    But here is a warning: the saveCopyAs command cannot change the file type. Since you want to have a macro embedded in your file, it must be a .xlsm type file. Changing the name does not override the behavior of the saveCopyAs command.

    And here is the other tricky thing. If you use SaveAs instead - you will close your existing file and open the new one. The macro can keep running - but if it tries to close the file (that it is in), it will now stop.

    The only workaround I can see for that would be to create an AddIn - this is code that runs "outside of" the workbook. You could have a menu item, a button on the ribbon, etc. to invoke any code in the addin - and it would give more flexibility to create a .csv copy of an open workbook.

    Might be more than you want to tackle though. Please let me know in the comments if that is what you need.