mytfein
asked on
From access to excel : COPYFROMRECORDSET DAO - compile error invalid use of property
Dear Experts,
can u pls explain why i'm getting a compile error at the statement that:
sets: rst = nothing
this logic is to open existing formatted spreadsheet and populate with selected columns.
'============ in a public module
Public objExcel As Excel.Application
Public objExcelActiveWkbs As Excel.Workbooks
Public objExcelActiveWkb As Excel.Workbook
Public objExcelActiveWs As Excel.Worksheet
'==========
Public Sub e005_CopyFromRecordSet(str Sql As String, _
strExcelPath As String)
Set objExcel = CreateObject("Excel.Applic ation")
Set objExcelActiveWkbs = objExcel.Workbooks
objExcelActiveWkbs.Open FileName:=strExcelPath
Set objExcelActiveWs = objExcel.ActiveSheet
objExcel.Visible = True
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql)
objExcelActiveWs.Range("A4 ").CopyFro mRecordset rst
With objExcelActiveWs
With .Cells
.Select
.EntireColumn.AutoFit
End With
End With
objExcel.ActiveWorkbook.Sa ve
'objExcelActiveWkb.Save
Set objExcel = Nothing
Set objExcelActiveWkb = Nothing
Set objExcelActiveWkbs = Nothing
Set objExcelActiveWs = Nothing
rst.Close
db.Close
===========> get compile error here - invalid use of property
rst = Nothing
db = Nothing
fld = Nothing
End Sub
can u pls explain why i'm getting a compile error at the statement that:
sets: rst = nothing
this logic is to open existing formatted spreadsheet and populate with selected columns.
'============ in a public module
Public objExcel As Excel.Application
Public objExcelActiveWkbs As Excel.Workbooks
Public objExcelActiveWkb As Excel.Workbook
Public objExcelActiveWs As Excel.Worksheet
'==========
Public Sub e005_CopyFromRecordSet(str
strExcelPath As String)
Set objExcel = CreateObject("Excel.Applic
Set objExcelActiveWkbs = objExcel.Workbooks
objExcelActiveWkbs.Open FileName:=strExcelPath
Set objExcelActiveWs = objExcel.ActiveSheet
objExcel.Visible = True
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql)
objExcelActiveWs.Range("A4
With objExcelActiveWs
With .Cells
.Select
.EntireColumn.AutoFit
End With
End With
objExcel.ActiveWorkbook.Sa
'objExcelActiveWkb.Save
Set objExcel = Nothing
Set objExcelActiveWkb = Nothing
Set objExcelActiveWkbs = Nothing
Set objExcelActiveWs = Nothing
rst.Close
db.Close
===========> get compile error here - invalid use of property
rst = Nothing
db = Nothing
fld = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
thx andrew, that worked...
can u pls check this open excel question (have this problem in this code, as well)
https://www.experts-exchange.com/questions/24234239/From-access-to-excel-COPYFROMRECORDSET-DAO-compile-error-invalid-use-of-property.html
can u pls check this open excel question (have this problem in this code, as well)
https://www.experts-exchange.com/questions/24234239/From-access-to-excel-COPYFROMRECORDSET-DAO-compile-error-invalid-use-of-property.html
?
ASKER
oh my god, Peter, i did not see that you responded first...
I am sooo sorry.... i got this new job and i am very frazzled...
what areas do you check, i believe i have another question about clearing arrays in access,
pls check in a few minutes, i will send you the link...
again my apologies, sandra
I am sooo sorry.... i got this new job and i am very frazzled...
what areas do you check, i believe i have another question about clearing arrays in access,
pls check in a few minutes, i will send you the link...
again my apologies, sandra
Set rst = nothing
etc