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

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:

VB Code:

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:

VB Code:

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

Visual Basic .NET 2003-2008 And Excel 2000-2003

VB.NET Code:

Option Explicit On
Option Strict On
'Add a COM reference to MS Office Excel xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oApp As Excel.Application
        Try
            'Create an Excel instalce.
            oApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            'Open the csv file
            oApp.Workbooks.OpenText(Filename:="C:\Test.csv", _
                                    Origin:=Excel.XlPlatform.xlMSDOS, _
                                    DataType:=Excel.XlTextParsingType.xlDelimited, _
                                    Comma:=True, _
                                    StartRow:=1)
            'Optional
            oApp.Visible = True
            'Now lets SaveAs to convert it to an xls file format.
            oApp.Workbooks.Item("Test.csv").SaveAs(Filename:="C:\Test.xls", _
                                                   FileFormat:=Excel.XlFileFormat.xlWorkbookNormal)
            'Now lets close the workbook. No need to save the changes as we just did a saveas
            oApp.Workbooks.Item("Test.xls").Close(SaveChanges:=False)
            'Quit Excel
            oApp.Quit()
            oApp = Nothing
            MessageBox.Show("Done", "Done", MessageBoxButtons.OK)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, "Error", MessageBoxButtons.OK)
        End Try
    End Sub
 
End Class