[FAQ's: OD] How do I convert a delimited file to an xls FileFormat?

[RobDog888]

To convert a csv or other delimited file you only need to open it in Excel and perform a .SaveAs and specify the format you wish it to be saved as and where.

To open the file we will use the .OpenText method. This method allows for the opening of several types of files.

Once opened we will .SaveAs and specify the location and fileformat desired.


Excel VBA Code Example:

Option Explicit
'Behind ThisWorkbook
Public Sub SaveAsXLS()
    Application.ActiveWorkbook.SaveAs Filename:="C:\Book1.xls", FileFormat:=xlWorkbookNormal
    Application.ActiveWorkbook.Saved = True
End Sub
 
'Usage:
'Call the sub from behind any worksheet or you can link it to a toolbar button etc.
Private Sub Command0_Click()
    Call SaveAsXLS
End Sub

VB 6 Code Example:

Option Explicit
'Add a reference to MS Excel xx.0 Object Library
Private Sub Command1_Click()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    
    'Create an Excel instalce and open the csv file.
    Set oApp = New Excel.Application
    'If its a comman separated values file then the Comma argument will be true
    'If its Tab delimited we will use the Tab argument and set it to True, and so forth.
    oApp.Workbooks.OpenText FileName:="C:\test.csv", Origin:=xlMSDOS, DataType:=xlDelimited, Comma:=True
    'Set a workbook variable to the opened csv file
    Set oWB = oApp.Workbooks("test.csv")
    'Save the csv as a xls file
    oWB.SaveAs FileName:="C:\test.xls", FileFormat:=xlWorkbookNormal
    'Tag the workbook as saved to surpress the "Do you want to save" prompt
    oWB.Saved = True
    'close and clean up resources
    oWB.Close
    Set oWB = Nothing
    oApp.Quit
    Set oApp = Nothing
End Sub