Export MS Access Record Set to Excel

[nic6000]

Hi,

I wonder if anyone out there could possibly help me please.

I'm currently trying to output data to a number of Excel Workbooks from Access and am trying to achieve this by looping through stored filenames and query names in a table.

The macro starts by stepping into the first row in a table, picking up the filename/location & query to output - this works fine. But when It goes though to the second row it opens up the excel workbook but doesn't export the data and I receive the following error:

Runtime error 1004: method 'cells' of 'object'_Global' failed.

The values used to determine worksheet names, query names are definitely right as have tested them individually.

I have attached the code below and marked where the error is occurring.

Any help would be appreciated.

Thanks in advance.



Sub ExportData()
'Copy records to a named range
'on an existing Excel File
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strQuery As String
Dim strExcelWS As String
Dim strFileTemplateLoc As String
Dim strFileLoc As String
Dim strFileName As String
Dim strFile
Dim strDateStamp
Dim strFileExt
Dim strExcelRange As Range
Dim r1
Dim c1
Dim r2
Dim c2

strDateStamp = " (" & Format(Date, "DDMMYYYY") & ")"
Set db = CurrentDb
Set rst = db.OpenRecordset("tblTRANSLATE_EXPORTDATA")

rst.MoveFirst
Do Until rst.EOF

strQuery = rst![Query].Value
strExcelWS = rst![Worksheet].Value
r1 = rst![RowStart].Value
c1 = rst![ColumnStart].Value
r2 = rst![RowEnd].Value
c2 = rst![ColumnEnd].Value
strFileLoc = rst![ExportLocation].Value
strFileName = rst![ExportName].Value & strDateStamp & rst![ExportFileExt].Value
strFileTemplateLoc = rst![TemplateLocation].Value & rst![TemplateName].Value

FileCopy strFileTemplateLoc, strFileLoc & strFileName

Set objXL = New Excel.Application
Set objWkb = objXL.Workbooks.Open(strFileLoc & strFileName)

Set rst2 = db.OpenRecordset(strQuery)
With objXL
.Visible = True
Set objSht = objWkb.Worksheets(strExcelWS)
objSht.Select
objSht.Range(Cells(r1, c1), Cells(r2, c2)).CopyFromRecordset rst2 '''''' *****Error appears here****
End With

objSht.Cells(5, 1).Value = "Date Produced: " & Format(Date, "dd/mm/yyyy")

rst.MoveNext

objWkb.Save
objWkb.Close
objXL.Quit
Set objSht = Nothing
Set objWkb = Nothing

Loop

rst.Close
Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[SkipVought]

Hi,

Explicit object reference...

        Set rst2 = db.OpenRecordset(strQuery)
        
        objXL.Visible = True
        
        Set objSht = objWkb.Worksheets(strExcelWS)
        With objSht
            .Range(.Cells(r1, c1), .Cells(r2, c2)).CopyFromRecordset rst2 '''''' *****Error appears here****
            .Cells(5, 1).Value = "Date Produced: " & Format(Date, "dd/mm/yyyy")
        End With
    
        
        rst.MoveNext

BTW, no need to use the Select method if objects are propertly referenced.

Skip,

_[red][/red]

 

[nic6000]

Thanks Skip, its know working, you help is very much appreciated.

Nic