Svgmassive
asked on
import data from a closed workbbok
i am using the adapted code below to import data from a closed workbook.I am using 2013 with the xlsb extension. using the xlsb extension cases it to fail.I am importing about 20 workbooks at a time.can we modify the code to make it work ? thanks
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OL EDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OL EDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OL
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OL
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OL
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OL
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If
And also, if you are importing just xlsb files then they will only import into application.version >= 12.0
This might also be your problem.
This might also be your problem.
I am interested to see the solution for this thread. As I have similar issue with Ado which will post the question in a new thread
ASKER
the sourceflle is the path and work book, what ever ou want it to be ,the last line is cnn.Open szConnect
Just to be clear, you say that you are having problems with a particular file extension, but yet you don't want to post details of how this is being handled in your sourcefile variable??
ASKER
Dim szConnect As String: Dim cnn As New ADODB.Connection:
szConnect = "Provider=Microsoft.ACE.OL EDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
szConnect = "Provider=Microsoft.ACE.OL EDB.12.0;D ata Source=C:\Users\TOYS\Deskt op\ALL_TES T\2014\Jul _2014\7_1_ 2014\Book1 .xlsb;Exte nded Properties=Excel 8.0;
cnn.Open szConnect,,,,,,It fails right here
this should be more than enough there is nothing more
szConnect = "Provider=Microsoft.ACE.OL
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
szConnect = "Provider=Microsoft.ACE.OL
cnn.Open szConnect,,,,,,It fails right here
this should be more than enough there is nothing more
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please post all of your code.
Thanks