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
cbishopcgrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can try something like this code (for sheet Main):
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 rs = CurrentDb.OpenRecordset("Query1") 'Replace Query1 with real query name
    Set appXL = CreateObject("Excel.Application")
    Set wb = appXL.Workbooks.Open(xlf)
    Set wks = wb.Sheets("Main") ' Sheet name
      
    wks.Range("C11").CopyFromRecordset rs
    
    wb.Save
    wb.Close
    appXL.Quit
    Set wb = Nothing
    rs.Close
    Set rs = Nothing

End Sub

Open in new window

You can change query and sheet names to sub's parameters and call it twice for different sheets
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbishopcgrAuthor Commented:
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!
0
cbishopcgrAuthor Commented:
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!
0
als315Commented:
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

0
cbishopcgrAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.