troubleshooting Question

Append rows from Access table to first empty row in Excel

Avatar of Stephen Byrom
Stephen ByromFlag for Ireland asked on
Microsoft Access
5 Comments2 Solutions914 ViewsLast Modified:
I am trying to export a table from Access to the end of an existing range in Excel.

here's what I have so far;
        Set objXL = New Excel.Application
            With objXL
                Set objWkb = .Workbooks.Open(GetMyPath() & "Call_Offs.xlsm", , , , "password")
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryUpLoadOrders", GetMyPath() & "Call_Offs.xlsm", False
                objXL.Visible = True
                For Each WS In objWkb.Worksheets
                    WS.Visible = xlSheetVisible
                Next WS
                AppActivate objXL.Caption
                Set objWkb = Nothing
                Set objXL = Nothing
            End With
        Exit Sub

Open in new window

If I put a named range at the end of the "transferspreadsheet" line it just overwrites the existing data.

I'm looking to tag the table rows onto the end of the existing data starting in column A in the sheet called "Orders" of the "Call_Offs" workbook, then clear the data in the MS Access Orders table ready to accept new orders next time.

As usual all help is appreciated.
Join our community to see this answer!
Unlock 2 Answers 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 2 Answers 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