JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
I have the following code which I'm testing to read data into an array via ADO:
Line in bold has error "Run-time error '13': Type mismatch"
f_ADO_Out is a public function that reads data into an array
Realised the output from the function is transposed and because this is something I'm bolting into existing code, I prefer to transpose before using the data.
I'm passing valid arguments into the function and do not believe the inputs are the problem. I also tried to transpose within the function but I get the same error; thoughts on how to fix please?
TIA,
Jack
I have the following code which I'm testing to read data into an array via ADO:
Rich (BB code):
Sub ExistingCode()
Dim filepath as string: filepath = "D:\"
Dim filename as string: filename = "Test.csv"
'ADO test
Dim arr() As Variant
Dim arr2 As Variant
arr = f_ADO_Out(filepath, filename)
ReDim arr2(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1))
arr2 = Application.Transpose(arr)
End Sub
f_ADO_Out is a public function that reads data into an array
Rich (BB code):
Public Function f_ADO_Out(ByRef strPath As String, ByRef strName As String) As Variant
Dim objConnection As Object
Dim objRecordset As Object
Dim sql As String
Const adOpenStatic As Long = 3
Const adLockOptimistic As Long = 3
Const adCmdText As Long = &H1
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
'Create connection to source file
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPath & ";" & "Extended Properties=""text;HDR=No;FMT=Delimited;"""
'SQL query to set up a "table of data" to read into Excel
sql = "Select * FROM [@1]" 'sql = "Select * FROM [" & strName & "] "
objRecordset.Open Replace(sql, "@1", strName), objConnection, adOpenStatic, adLockOptimistic, adCmdText
f_ADO_Out = objRecordset.GetRows
Set objConnection = Nothing
Set objRecordset = Nothing
End Function
I'm passing valid arguments into the function and do not believe the inputs are the problem. I also tried to transpose within the function but I get the same error; thoughts on how to fix please?
TIA,
Jack