Help with macro to save file from .CSV to .XLS

[eilersca]

Hello:

I need some help completing my first macro. I am writing a Macro that charts the contents of a .CSV file and prints the chart. I would like to know if there is a way to save this file in the same directory from which it was opened and as an Excel file (.XLS) automatically (NO SAVE AS WINDOW).

Thanks for you help.

Chad

[NateO]

This should do the trick:

Sub Savin()
    ActiveWorkbook.SaveAs _
    Filename:=Application.WorksheetFunction.Substitute(ActiveWorkbook.Name, _
    ".csv", ""), FileFormat:=xlNormal
End Sub

[eilersca]

NateO,

I tried your code, but it still saves the file under My Documents.

For Example:

Original File P:EE OD DATA2002 DATA-TLAEE-07 OD DATA (2002)Ee-07-02MarMar,05,2002.CSV

Save File as P:EE OD DATA2002 DATA-TLAEE-07 OD DATA (2002)Ee-07-02MarMar,05,2002.xls

Also your code still shows the file as a CSV type; however, it does display my chart correctly.

Any other ideas?????

Thanks for trying.

[NateO]

You could try this, did the code above still assign a ".csv" extension? This should work unless Excel is under the impression that 'MyDocuments' is the path, if so, you'll need to start opening the file from the target directory.

Sub Savin()
    On Error GoTo can 'allows cancel on duplicate
    ActiveWorkbook.SaveAs _
    Filename:=ActiveWorkbook.Path & "" & _
    Application.WorksheetFunction.Substitute(ActiveWorkbook.Name, _
    ".csv", ".xls")
can:
End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-06 15:26 ]

[eilersca]

THANKS NateO!!!!

[NateO]

You're very welcome, good hunting.