Save as CSV (modifying SAVE.AS)

[GarettJay]

I have the following formula.
It works for saving as a new workbook. How can I get it to work for saving as CSV?


I have tried adding ".csv", FileFormat:=xlCSV to it but no such luck thus far.


Cheers

 ActiveWorkbook.SaveAs Filename:="C:\User\Test.CSV" & Test1Str & " " & TestStr & " " & ActiveWorkbook.Name

 

[Domenic]

Make sure that you provide a proper path and filename, along with the fileformat, for example...

ActiveWorkbook.SaveAs Filename:="C:\User\Domenic\Documents\sample.csv", FileFormat:=xlCSV

 

[GarettJay]

Thanks. That worked. Here's a problem with this method however. It assumes you have saved the original excel. In one excel I have highlighted changes. I close that without saving and then the new excel keeps those changes. When I do this with a CSV. It keeps the original without macro changes of the original document. Any way to get around that?

 

[GarettJay]

Hmmmmmm When you insert a table and highlight data, the excel does not like it in CSV format and pretends it does not exist. Wonder how to get around this issue

 

[RoryA]

A CSV file is a text file, so it will not store tables or formatting.

 

[GarettJay]

True. I don't suppose you'd be able to save that

[COLOR=#333333]ActiveWorkbook.SaveAs Filename:="C:\User\Domenic\Documents\sample.csv", FileFormat:=xlCSV

[/COLOR]as a variable? Cheers

 

[RoryA]

Save what as a variable?

 

[GarettJay]

Oh sorry. I mean as in if you are working with multiple excels at once, won't .activeworkbook be liable to give you errors as it will get confused on which activeworkbook to save? How would I rearrange that to include workbook and sheet names. E.g Workbook S7 Sheet 1. I suppose that would mean I'd need a macro per excel though. I guess if its more reliable thats not an issue

 

[Domenic]

When you open your target workbook, you can assign it to a variable, and then use that variable to save it...

[FONT=Verdana]    [COLOR=darkblue]Dim[/COLOR] wkb [COLOR=darkblue]As[/COLOR] Workbook
    
    [COLOR=darkblue]Set[/COLOR] wkb = Workbooks.Open("c:\users\domenic\desktop\book2.xlsx")

    [COLOR=#008000]'etc
    '
    '[/COLOR]
    
    wkb.SaveAs Filename:="[FONT=Verdana]c:\users\domenic\desktop\[/FONT]sample.csv", FileFormat:=xlCSV[/FONT]