Openning delimited file and saving an Excel file

[jimvg]

Hi,

I need to open a semicolon delimited file.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

Dim strDelimitedSourceFilename As String = "C:\Delimited.txt"
Dim strExcelOutputFilename As String = "C:\Report.xls"

xlApp.Workbooks.OpenText(Filename:="C:\BeverlyReports\DelimitedDA.txt", Origin:=437, StartRow:=1, DataType:=Excel.XlTextParsingType.xlDelimited, TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, TAB:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True)

xlSheet.SaveAs(strExcelOutputFilename)
xlApp.Quit()

This saves Report.xls empty.

I have found specific documentation on PIAs lacking. Does anyone have book or website suggestions?

Jim

 

[PhilBayley]

Jim,

Do you need it as an excel or do you just want the information from it? I have some code that will handle the file for you but doesnt do the excel bit

Phil

 

[jimvg]

Hi Phil,

I already convert a text report into a delimited file, but now I need to covert the delimited file to an Excel file.

Jim

 

[jimvg]

FYI...

Here is the VB code to open a basic delimited text file in Excel.

xlApp.Workbooks.OpenText(filename:=strDelimitedSourceFilename, DataType:=Excel.XlTextParsingType.xlDelimited, Comma:=True)

And saving in Excel format...

xlApp.ActiveWorkbook.SaveAs(Filename:=strExcelOutputFilename, FileFormat:=Excel.XlFileFormat.xlWorkbookNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False)

Jim