Changing a csv format to xls and saving/closing

Originally Posted by Jen_DPS

Hello.

I am trying to write a macro/program that will save a csv activeworkbook in xls and close it.

The workbook will always be a different name.

The files do not have an extension upon download and formatting.
ie stm0107 (Then they need to be saved as xls. Which is done manually at this time.)

Below are some trial and error attemtps. Thank you for your suggestions.

Sub CloseWorkbook()
Dim Filename As String
'Filename = Workbook.Name
MsgBox (ActiveWorkbook.Name)
Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename = ActiveWorkbook.Name(".xls")

'Filechange = Replace(Filename, "", "xls")
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub

Thank You J

Hi,

the 'save' that I used is

Please Login or Register  to view this content.

which seemed to work (well, for the first 7,000 file it has been ok)

Hope this has pointers to help you

---

Originally Posted by Jen_DPS

Thank you for the response,

I'm not sure how to apply the suggestion. I'm still unclear as to how to address the csv file in the code with no extension, to a file with an xls extension.

I have tried to add an extension on the csv file before opening the file in excel but it does not work.

The files come from a server with no extension. (ie csv or txt).
I need to save them as filename to filename.xls.

Again thank you and all suggestions are greatly appreciated.

Hi,

Once you have the file open in Excel, then simply 'File, SaveAs' and select the 'Microsoft Office Excel Workbook (.xls)' option.

If you want to do this as VB code then Record a macro, and do the SaveAs, then stop the record and Edit the macro to see the required code.

it should give something like

Please Login or Register  to view this content.

Does this help?

---

Originally Posted by Jen_DPS

Hello!

The last suggestion did work BUT...
I get a message the next time I try to run the code.

"A file named C:\Weeklys\thisFilename.xls already exists in this location. Do you want to replace it."

I do not want to replace it. I just want to run the next file and save it.

Code:
Sub CloseWorkbook()
'Dim Filename As String

Filename = "thisfileName"

ActiveWorkbook.SaveAs Filename:="C:\Weeklys\" & Filename & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub

I must not be handling the file name properly.

I intend to add this macro to a program to that I can run through an entire Folder that opens, formats and saves all of the files in that folder.

Thank you again for your input. It has been of great assistance.

Hi,

you need the line

Filename = "thisfileName"

to pick up the filename from the file that you opened, or the name you wish to save as,

probably

Filename = ThisWorkbook.name

hth
---

Originally Posted by Jen_DPS

Oh, here is the code.

Sub CloseWorkbook()
Dim Filename As String

Filename = ThisWorkbook.Name

ActiveWorkbook.SaveAs Filename:="C:\Weeklys\" & Filename & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub

Hi,

I didn't check it all, but this works

Please Login or Register  to view this content.

hth
---