Phil Chapman
asked on
Read EXCEL File
Reading EXCEL Files
Occasionally I receive a file that was created in some software packages where I can’t see either the first or last column using the code below.
If I open the file in excel and save the file then the missing column is displayed.
I’ve tried referencing
Reference Microsoft ActiveX Data Obect 2.6 Libary
Reference Microsoft ActiveX Data Obect 2.7 Library
Reference Microsoft ActiveX Data Obect 2.8 Library
With no difference
The attachmet is the Sub that reades the Excel File and writes the contents to a Tab Delimited File.
I suspect the problem may be in open syntex ( see below )
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=" & sExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""
Any help will be appreciated.
Thanks,
Phil
Occasionally I receive a file that was created in some software packages where I can’t see either the first or last column using the code below.
If I open the file in excel and save the file then the missing column is displayed.
I’ve tried referencing
Reference Microsoft ActiveX Data Obect 2.6 Libary
Reference Microsoft ActiveX Data Obect 2.7 Library
Reference Microsoft ActiveX Data Obect 2.8 Library
With no difference
The attachmet is the Sub that reades the Excel File and writes the contents to a Tab Delimited File.
I suspect the problem may be in open syntex ( see below )
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OL
"Data Source=" & sExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""
Any help will be appreciated.
Thanks,
Phil
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Set xlApp = CreateObject("Excel.Applic ation")
Set xlBook = xlApp.Workbooks.Open("C:\T emp\MyFile .xls")
strOutputFileName = "C:\TEMP\MyFile.txt"
xlApp.ActiveWorkbook.SaveA s strOutputFileName,-4158,fa lse 'this saves as tabdelimited file
xlApp.Quit
Set xlBook = xlApp.Workbooks.Open("C:\T
strOutputFileName = "C:\TEMP\MyFile.txt"
xlApp.ActiveWorkbook.SaveA
xlApp.Quit
ASKER
Markdmac,
It working ok but it's asking if I won't to save the chagnes how can I suspress this?
It working ok but it's asking if I won't to save the chagnes how can I suspress this?
ASKER
Markadmac,
I found the answer
Set xlApp = CreateObject("Excel.Applic ation")
Set xlBook = xlApp.Workbooks.Open(Input FileName)
xlApp.ActiveWorkbook.SaveA s OutPutFileName, -4158, False 'this saves as tabdelimited file
xlApp.Application.DisplayA lerts = False
xlApp.Quit
I found the answer
Set xlApp = CreateObject("Excel.Applic
Set xlBook = xlApp.Workbooks.Open(Input
xlApp.ActiveWorkbook.SaveA
xlApp.Application.DisplayA
xlApp.Quit
Glad you were able to find the answer for the prompt.
Regards,
Mark
Regards,
Mark
ASKER
I'm opening another quest how to save the file as a Tab Delimited File instead of a Excel file
Question:
Save Excel File as a Tab Delimited File