asked on

Export Access Query to an Existing spreadsheet tab starting at a specific location

I need to figure out the right code to export an Access Query to an Existing Excel Spreadsheet tab - starting at a specific location - I have attached the spreadsheet - the 2 tabs that I need to export to would be the Main and Detail - both to location C11 - and preferably with NO column headers from access - but that I can work around.
GL-Daily-Cash-Data-Porter.xlsx

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbishopcgr

ASKER

Wonderful....wonderful - been working on it a while - maybe easy for some - but I do not do much VB stuff - and was having a time figuring it out - should have done this a couple of days ago.....VERY HAPPY!

cbishopcgr

ASKER

I have a follow up question - not sure if I need to do a New Question - but let me know.

I put the code you gave me behind a button - worked perfectly - but two things would enhance this for me:

1. Export to each sheet on the spreadsheet Main and Detail under 1 button - but there is a seperate query for each of the sheets: Main = GL Main Query and Detail = GL Detail Query.

2. This I am sure is EASY - but just to display a message that the exort is complete.

Thanks!

als315

Try this code

Public Sub AccessToExcel()
    
    Dim appXL As Object
    Dim wb As Object
    Dim wks As Object
    Dim xlf As String
    Dim rs As DAO.Recordset
    
    xlf = "c:\tmp\GL-Daily-Cash-Data-Porter.xlsx" 'Full path to Excel file
    Set appXL = CreateObject("Excel.Application")      
    Set wb = appXL.Workbooks.Open(xlf)
    
    Set rs = CurrentDb.OpenRecordset("GL Main Query") 
    Set wks = wb.Sheets("Main") 
    wks.Range("C11").CopyFromRecordset rs
    rs.Close

    Set rs = CurrentDb.OpenRecordset("GL Detail Query") 
    Set wks = wb.Sheets("Detail") 
    wks.Range("C11").CopyFromRecordset rs
    rs.Close

    wb.Save
    wb.Close
    appXL.Quit
    Set wb = Nothing
    Set rs = Nothing

End Sub

Open in new window

cbishopcgr

ASKER

Again - thank you so much - this has been such a great help - this will save me and the people I work with so much time. I really appreciate your quick response and helping me solve something that I have been working on awhile.