G
Guest
Hi guys,
Can you help me with this. Below is some code that I got from somewhere
(apologies but I can't remember where now), which I've tried to adapt to do
what I want.
I'm trying up update a column of figures within Excel from a COLUMN of
figures within an Access Query. I use the unaltered version of this function
alot which updates a column within Excel via a single row of figures in an
Access Query.
As I say, I'm trying to get this code to loop through the (58) records that
are in a single column in Access.
I currently get Error 3265 'Iten not found in this collection'. What's wrong?
Function fColumnToExcel(strQuery As String, strPath As String, intSheet As
Integer, fldColumn As Integer, fldRow As Integer) As Variant
On Error GoTo E_Handle
Dim db As DAO.Database, rs As DAO.Recordset
Dim c As Integer, r As Integer, i As Integer
Dim RsSql As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim xl As Object
Dim Workbook As Object
Dim Sheet As Object
Set db = DBEngine.Workspaces(0).Databases(0)
RsSql = "SELECT * FROM " & strQuery & ";"
Set rs = db.OpenRecordset(RsSql, dbOpenDynaset)
Set xl = CreateObject("Excel.Application")
Set Workbook = xl.Workbooks.Open(strPath)
Set Sheet = xl.activeworkbook.sheets(intSheet)
c = fldColumn
r = fldRow
i = 0
' Loop through the query's records and copy the records
' to the Excel spreadsheet.
Do Until i = rs.RecordCount
CurrentField = rs(i)
Sheet.cells(r, c).Value = CurrentField
r = r + 1
i = i + 1
Loop
xl.Application.activeworkbook.Save
Set Sheet = Nothing
xl.Quit
Set xl = Nothing
sExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit
End Function
Many thanks!
Lee
Can you help me with this. Below is some code that I got from somewhere
(apologies but I can't remember where now), which I've tried to adapt to do
what I want.
I'm trying up update a column of figures within Excel from a COLUMN of
figures within an Access Query. I use the unaltered version of this function
alot which updates a column within Excel via a single row of figures in an
Access Query.
As I say, I'm trying to get this code to loop through the (58) records that
are in a single column in Access.
I currently get Error 3265 'Iten not found in this collection'. What's wrong?
Function fColumnToExcel(strQuery As String, strPath As String, intSheet As
Integer, fldColumn As Integer, fldRow As Integer) As Variant
On Error GoTo E_Handle
Dim db As DAO.Database, rs As DAO.Recordset
Dim c As Integer, r As Integer, i As Integer
Dim RsSql As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim xl As Object
Dim Workbook As Object
Dim Sheet As Object
Set db = DBEngine.Workspaces(0).Databases(0)
RsSql = "SELECT * FROM " & strQuery & ";"
Set rs = db.OpenRecordset(RsSql, dbOpenDynaset)
Set xl = CreateObject("Excel.Application")
Set Workbook = xl.Workbooks.Open(strPath)
Set Sheet = xl.activeworkbook.sheets(intSheet)
c = fldColumn
r = fldRow
i = 0
' Loop through the query's records and copy the records
' to the Excel spreadsheet.
Do Until i = rs.RecordCount
CurrentField = rs(i)
Sheet.cells(r, c).Value = CurrentField
r = r + 1
i = i + 1
Loop
xl.Application.activeworkbook.Save
Set Sheet = Nothing
xl.Quit
Set xl = Nothing
sExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit
End Function
Many thanks!
Lee