automation to copy records from excel to an existing excel

  • Thread starter Thread starter chris_quinn26
  • Start date Start date
C

chris_quinn26

I have the following code that i found online. What i am wanting it to
do is automation to copy records from excel to an excel. Please show
me how to change this code to bring recordsets from another excel file,
versus from a access table.

Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("Customers", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End Sub
 
Just create a linked table (use File|External Data|Link) connected to
your "source" worksheet and use this instead of "Customers" as the
source of the recordset.
 
I do not want to link the table in access because I will lose all my
conditional formatting and formulas. So, i really need to be able to
copy from one excel file to another.
 
If you want to copy from one Excel file to another, it's an Excel
question. Ask in an Excel newsgroup, get the code working in Excel VBA
and then if necessary port it to Access.
 
Back
Top