How to convert Excel file type from csv to xls

[behnam]

Hi,



Can anyone help me with the vba code to change the file type of all files (two excel files called first.csv and second.csv) in a folder from csv to xls. I tried simply changing the extension for the filename name but this dosent work. Thanks.

[ranman256]

no, the code must open it in Excel, then SAVE AS to the xls format.
NOTE : you must add the Excel object library, in VBE menu,Tools, References.

   'import all files in folder
'------------------
Public Sub ImportAllXLFilesAndSheets(ByVal pvDir)
'------------------
Dim vFil, vTargT
Dim i As Integer
Dim sTbl As String, sSql As String
Dim tdf As TableDef
Dim colSheets As New Collection
Dim vSheet, vQry, oFolder, oFile

sTbl = "xlFile"    'linked xl file to import

On Error GoTo errImp
Set Db = CurrentDb

Select Case True
 Case pvDir = ""
    MsgBox "No source folder given", vbCritical, "Error"
 
Case Else
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(pvDir)
    For Each oFile In oFolder.Files
        If InStr(oFile, ".csv") > 0 Then         'only csv files
            vFil = pvDir & oFile.Name
                     'open the wb
            SaveCsvAsWB vFil
        End If

    Next  'file
End Select

Set colSheets = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "ImportAllXLFilesAndSheets()" & Err
Exit Sub
Resume
End Sub

'-----------------
Private Sub SaveCsvAsWB(ByVal pvFile)
'-----------------
Dim col As New Collection
Dim xl As Excel.Application

  '=========== YOU MUST HAVE 'EXCEL OBJECT LIBRARY' loaded via VBE menu, TOOLS, REFERENCES
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open pvFile
    .ActiveWorkbook.SaveAs pvFile & ".xls", xlExcel8
    .ActiveWorkbook.Close False
End With
Set xl = Nothing
End Sub

[Missinglinq]

...two excel files called first.csv and second.csv...

Also, note that these files are not 'Excel files,' hence the need to save them as Excel files!