merdealors
Programmer
hI,
I'm running this code from an Excel Macro. I'm just querying a file and copying records from it based on an SQL
select. I'm getting truncated records (not the entire 256 columns). Any thoughts. Thanx.
Public Function ProcDataMIN(ByRef xlwb As Workbook, ByRef xlws As Worksheet, _
ByRef currentrow As Long, filename As String, fpath As String, _
SearchValue1, override, xCount) As Long
Dim adoXL As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim filepath As String
Dim sqlstr As String
Dim rngstr As String
rngstr = "A" & currentrow
filepath = fpath & "\"
sqlstr = "Select * from [Sheet1$] Where [F1] > 40000 AND [F7] = " & SearchValue1
Set adoXL = New ADODB.Connection
With adoXL
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & filepath & filename & ";" & _
"Extended Properties=""Excel 8.0; HDR=No;"""
.Open
End With
Set adoRS = New ADODB.Recordset
adoRS.Open sqlstr, adoXL
xlws.Range(rngstr).CopyFromRecordset adoRS
xlws.Range("E" & currentrow & ":E" & currentrow + xCount - 1).Value = "M"
xlws.Range("F" & currentrow & ":F" & currentrow + xCount - 1).Value = override
currentrow = currentrow + xCount - 1
adoRS.Close
Set adoRS = Nothing
Set adoXL = Nothing
ProcDataMIN = currentrow
End Function
I'm running this code from an Excel Macro. I'm just querying a file and copying records from it based on an SQL
select. I'm getting truncated records (not the entire 256 columns). Any thoughts. Thanx.
Public Function ProcDataMIN(ByRef xlwb As Workbook, ByRef xlws As Worksheet, _
ByRef currentrow As Long, filename As String, fpath As String, _
SearchValue1, override, xCount) As Long
Dim adoXL As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim filepath As String
Dim sqlstr As String
Dim rngstr As String
rngstr = "A" & currentrow
filepath = fpath & "\"
sqlstr = "Select * from [Sheet1$] Where [F1] > 40000 AND [F7] = " & SearchValue1
Set adoXL = New ADODB.Connection
With adoXL
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & filepath & filename & ";" & _
"Extended Properties=""Excel 8.0; HDR=No;"""
.Open
End With
Set adoRS = New ADODB.Recordset
adoRS.Open sqlstr, adoXL
xlws.Range(rngstr).CopyFromRecordset adoRS
xlws.Range("E" & currentrow & ":E" & currentrow + xCount - 1).Value = "M"
xlws.Range("F" & currentrow & ":F" & currentrow + xCount - 1).Value = override
currentrow = currentrow + xCount - 1
adoRS.Close
Set adoRS = Nothing
Set adoXL = Nothing
ProcDataMIN = currentrow
End Function