How can I append the output from multiple VBA codes into a single Excel file without overwriting the

[Abhayshukla0311]

I have two separate VBA codes:


1. One exports the results of an SQL query to an Excel file.




2. The other exports data generated within VBA to an Excel file.






I need to append both outputs into a single Excel file without overwriting the existing data. The new data should be added below the existing content, ensuring both sets of results are preserved.




Can you provide a VBA solution to achieve this?

[jojowhite]

to append data, use Excel automation.
create this Public Sub to a Module, then pass the recordset that you want to apppend
and the Path+Name of the workbook, and the name of the sheet to where the
data will be appended:

Sub AppendToExcel(ByRef rst As dao.Recordset, ByVal WorkBook As String, Optional ByVal Sheet As String = "Sheet1")
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlSheet As Object
    Dim LastRow As Long


    ' Open Excel
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(WorkBook)
    Set xlSheet = xlWorkbook.Sheets(Sheet)


    ' Find last used row
    LastRow = xlSheet.Cells(xlSheet.Rows.Count, "A").End(-4162).Row + 1 ' xlUp
    
    xlSheet.range("A" & LastRow).CopyFromRecordset rst
    
    xlWorkbook.Close True
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub

[Micron]

That's OK if column A is the deepest. If some other column is the deepest/longest then it will over-write in those rows, no? I think I would use Find instead. Sample:
lRow = .Cells.Find(what:="*", After:=Range("I5"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row

If I'm not mistaken, using Find in code affects the application's Find parameters so it's best to specify most if not all of them each time. I might be confusing that with something else so I suggest reading up on the method.

Also, pretty sure one needs a reference to Excel library in order to use automation of Excel from within Access and I didn't see that mentioned.
EDIT - that is contained in a With block, which is why the reference starts with .Cells

[Welshgasman]

I use this

Public Function GetLastRow(pstrSheet As String, Optional pstrColumn As String) As Long
' Return last used row for sheet and column passed in
Dim lngLastRow As Long
Dim sht As Worksheet


Set sht = Sheets(pstrSheet)
If pstrColumn = "" Then pstrColumn = "A"


lngLastRow = sht.Cells(ActiveSheet.Rows.Count, pstrColumn).End(xlUp).Row
GetLastRow = lngLastRow
Set sht = Nothing


End Function

so I can specify which column to check.

Eg: lngLastRowCopy = GetLastRow(ActiveSheet.Name, strColName1)

I have it in my Personal.xlsb file and a reference to it in any workbook I need it in.
Could just copy and paste as well.

[alansidman]

Use Power Query (Get and Transpose Data found on the Data Tab) to bring each into the PQ editor. Once in the Editor, you can join or append each to the other within the UI. No coding required. Once completed, close and load to Native Excel.