Export Excel data into another Excel workbook using VBA ADODB Connection #excel #macro #shorts

Export Excel data into another Excel workbook using VBA ADODB Connection:

Please find the below code:

Sub ExportExcelDataintoAnotherExcel()

Dim conn As New ADODB.Connection
Dim rs As New Recordset
Dim r As Integer
Dim c As Integer

";Extended Properties=""Excel 12.0;HDR=yes;"";"

rs.Open "`Sheet1$`", conn, adOpenDynamic, adLockOptimistic

For r = 2 To Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
rs.AddNew
For c = 0 To rs.Fields.Count - 1
rs.Fields(c).Value = Sheet3.Cells(r, c + 1).Value
Next c
rs.Update
Next r
End Sub

[itsnumpty]

After many hours searching this simple solution is the only one that works - would suggest sharing on some forums to answer others who had similar issues as mine. Thank you, Akash!