Link to home
Create AccountLog in
Avatar of mytfein
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(strSql As String, _
                                  strExcelPath As String)

Set objExcel = CreateObject("Excel.Application")
 
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").CopyFromRecordset rst


With objExcelActiveWs
     With .Cells
          .Select
          .EntireColumn.AutoFit
     End With
End With


objExcel.ActiveWorkbook.Save
'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
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

For object variables you have to use Set.
Set rst = nothing
etc
ASKER CERTIFIED SOLUTION
Avatar of TextReport
TextReport
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of mytfein
mytfein

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
Avatar of mytfein

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