[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

[RobDog888]

Visual Basic .NET 2003-2008 And Excel 2000-2003
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