Link to home
Create AccountLog in
Avatar of GeneBat
GeneBatFlag for United States of America

asked on

Excel Automation Not Working for Existing Sheet

Hi Experts –

I have some Access 2003 code that I’m using to populate an existing Excel spreadsheet that I need some help on completing. Here’s the code:
Public Sub AccessToExcel()
    
    Dim appXL As Object
    Dim wb As Object
    Dim wks As Object
    Dim rs As DAO.Recordset
    
    Dim sSql As String
    
    sSql = "tmpCVExportExcel"
    
    Set rs = CurrentDb.OpenRecordset(sSql)
    
    wb.Workbooks.Open fileName:="C:\Test\scrap-rate-trend2.xlsx"
    
    wks.Sheets("ChartData").Range("A2" & rs.RecordCount).CopyFromRecordset rs

    
    'save and clean up
    wb.Save
    wb.Workbooks.Close
    Set wb = Nothing
    Set rs = Nothing

End Sub

Open in new window


tmpCVExportExcel is a table in my Access Database.

Thanks,
GeneBat
Avatar of Mike77
Mike77

Hello,

Your SQL statement has to be a query :

Try this instead of sSql = "tmpCVExportExcel" :

sSql = "SELECT * FROM tmpCVExportExcel"

I would also recommend a rs.close between the set wb = nothing and set rs = nothing lines.
Avatar of GeneBat

ASKER

I forgot to put that it needs to clear the existing sheet data first then import starting at cell "A2".
Avatar of Norie
The first thing you'll need to do is create an instance of Excel, then open the workbook and set a reference to it.

Then copy the data from the recordset to the appropriate location on the worksheet.

Finally close/quit everything and clean up.
Option Explicit



Public Sub AccessToExcel()
    
    Dim appXL As Object
    Dim wb As Object
    Dim wks As Object
    Dim rs As DAO.Recordset
    
    Dim sSql As String
    
    sSql = "SELECT * FROM tmpCVExportExcel"
    
    Set rs = CurrentDb.OpenRecordset(sSql)
    
    Set appXL = CreateObject("Excel.Application")
    
    Set wb = appXL.Workbooks.Open(FileName:="C:\Test\scrap-rate-trend2.xlsx")
    
    wb.Sheets("ChartData").Cells.ClearContents

    wb.Sheets("ChartData").Range("A2").CopyFromRecordset rs

    'save and clean up
    wb.Close True ' save changes
    Set wb = Nothing
    
    appXL.Quit
    Set appXL = Nothing
    
    rs.Close
    Set rs = Nothing

End Sub

Open in new window

Avatar of GeneBat

ASKER

Hi : imnorie

Will that ClearContents wipeout my Headers because I would like to keep those if possible.
That's why I'm starting at "A2"

GeneBat
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of GeneBat

ASKER

Thanks works as expected!
GeneBat