troubleshooting Question

From access to excel : COPYFROMRECORDSET DAO - compile error invalid use of property

Avatar of mytfein
mytfein asked on
Microsoft Access
5 Comments1 Solution628 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
TextReport

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros