Code Module to open multiple .csv files then save them as .xls

[ST4RCUTTER]

This seems fairly simple but I am doing this wrong. I am somewhat experienced in Excel VBA but perhaps because I am making the call from Access I am missing a step here. I have a number of files (usually 5 but the number may vary) that are saved in the .CSV format. I want to open these and then resave them as .XLS files so I can create a linked table in an Access database more easily. I experience problems when linking directly to the .CSV files.

I am trying to open the file, save as XLS, then close the file. This is repeated for as many files as are in the folder. Here is my non-functioning code:

Sub Resave_source_files()

Call GetData("ECM.csv")
Call GetData("ECM_pending.csv")
Call GetData("MNT.csv")
Call GetData("MNT_pending.csv")
Call GetData("Keystone.csv")
' release static workbook setting in GetData macro
Call GetData("ReleaseWB")
End Sub

Sub GetData(sFname As String)
Static wB As Workbook
Dim wbT As Workbook
Dim sPath As String

If sFname = "ReleaseWB" Then
Set wB = Nothing
Exit Sub
End If
sPath$ = "C:\folder1\folder2\folder3\"
If LCase(Dir(sPath & sFname, vbNormal)) = LCase(sFname) Then
Workbooks.Open Filename:=sPath & sFname

If wB Is Nothing Then
Set wB = ActiveWorkbook
Else
Set wbT = ActiveWorkbook
wbT.Save As Filename:=sPath & sFname & ".xls"
wbT.Close savechanges:=False
End If
End If
End Sub

Thanks!

 

[namliam]

I want to open these and then resave them as .XLS files so I can create a linked table in an Access database more easily. I experience problems when linking directly to the .CSV files.

Honestly... You dont want to do this... Opening the CSV and saving it as Excel may cause all kinds of problems. Working with the RAW csv file and importing into the DB or linking them is MUCH prefered for MANY reasons!

Believe me doing it this way is not only a round about way of doing this, but also will cause all kinds of problems you dont want to know about.

I suggest resolving your CSV issues rather than this issue. What problems are you experiencing with the CSV linking?? And why link them? And not import them into your DB? This makes accessing the data faster and easier.

Greets from Amsterdam

 

[chergh]

Got to agree with what Namliam has said.

However if you do want to do it the way you have outlined then try:

wbT.SaveAs Filename:=sPath & sFname & ".xls", FileFormat:=xlnormal

 

[ST4RCUTTER]

The problem here is that the data source is limited to .csv exports so I can't export the data as space delimited or any other format. To add insult to injury the field called "description" often includes several commas which can incorrectly offset all columns improperly. I don't have a solution to this problem but saving the file as an Excel file type avoids this issue altogether.

 

[namliam]

If a field contains delimiters of the file it should be enclosed by " to indicate a field.

If that is not the case Excel will just like Access break up the field into seperate fields, unless the file is also fixed width.
You dont need space delimited or any other format, you can import comma seperated into access just like any other file