I am currently writing some code that can access a separate workbook through an ADODB connection. I have chosen this instead of other methods because of speed. Here is my code below:
Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' rs.Open "SELECT * FROM [SheetName$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' rs.Open "SELECT * FROM [SheetName$]", _
cn, adOpenStatic, adLockOptimistic, adCmdText
' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%'", _
cn, adOpenStatic, adLockOptimistic, adCmdText
' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field Name]", _
cn, adOpenStatic, adLockOptimistic, adCmdText
' optional ways of retrieving a recordset
' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet
On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If
'RS2WS rs, TargetCell
TargetCell.CopyFromRecordset rs ' optional approach for Excel 2000 or later
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Now, this code mostly works however when a row contains mixed data types, the query will skip some values. For example:
Original data:
3844774 12505604
3844794 12505604
4266113 3281271
4295817 1307HX
Returned data:
3844774 12505604
3844794 12505604
4266113 3281271
4295817
Notice how the last bit of data is skipped. This is true for multiple entries, but only those that contain letters (making it text). The original table has everything set to text as well. Any suggestions so that it doesn't skip these lines?
Thanks in advance!
That is because you are missing IMEX :)
See this link (Section Treating data as text)
http://connectionstrings.com/excel-2007
Quote from that link.
Treating data as text
Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
For XLS files see this link
http://connectionstrings.com/excel
HTH
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With