How do I save a CSV file as an Excel (.xls) file from VB

[BillDickenson]

This is really stupid, but I have to read a CSV file (name changes every month) then save it as the same Excel file. I need to do formatting and editing while I have it open.

For some reason, I cannot save it as an excel file. I can change the name, but it is clearly saving it as csv. I've tried a couple of different formats

Anyone see what I'm doing wrong ?

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim ifilename As String
Dim ofilename As String
Dim bIndicator As Boolean
Dim szExtension As String
Dim index As Integer
Dim szW1Name As String



bIndicator = False

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then
'Excel was not open
Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo 0

Set xlWB = xlApp.WorkBooks.Open(ifilename)
If xlWB.worksheets.Count > 1 Then
MsgBox "using the first sheet in the original file"
End If


For index = 1 To xlWB.worksheets.Count
szW1Name = xlWB.worksheets(index).Name
Next index

Set xlWS = xlWB.worksheets(szW1Name)

xlWS.Range("1:2").Delete
xlWS.COLUMNS("B:B").NUMBERFORMAT = "@"
xlWS.COLUMNS("K:K").NUMBERFORMAT = "@"
xlWS.COLUMNS("N:N").NUMBERFORMAT = "@"

'Tried this one
' xlWB.SaveAs filename:= _
' ofilename, FileFormat:=xlNormal, _
' Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
' CreateBackup:=True
'Now trying this one

xlWB.SaveAs ofilename, xlNormal
xlWB.Close
xlWB.Quit

End Function

 

[PHV]

Take a look at the 2nd argument (FileFormat) of the Workbook.SaveAs method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillDickenson]

Can you be a bit more specific ? I've no idea what you are suggesting. Thanks

 

[BillDickenson]

Never mind. I ended up using the -4143 and it works fine. Thanks