VBA in Access, opening up excel

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
Hi,
I am using VBA in access to run a query. After running the query I want to copy the resulting recordset and copy it to a workbook in excel. How do I write code to open excel through access and copy the recordset into it?

Any help would be much appreciated!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Probably the easiest thing would be to use Access' Tools-->Office Links-->Analyze it with Excel.

Hope that helps,

Smitty
 

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
Is there a way I can automate it with VBA? I need to run this frequently
 

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
Here is one way. Call this procedure and pass your recordset object to it. Hope it helps.

Code:
Sub CopyRecordSet(InRecSet As Recordset)
        
        Dim xlApp As Excel.Application, xlNewBook As Excel.Workbook, xlDestSheet As Excel.Worksheet
        
        Dim fld As DAO.Field
        
        Dim i As Integer
        
        Set xlApp = GetObject(, "excel.application")
        Set xlNewBook = xlApp.Workbooks.Add
        Set xlDestSheet = xlNewBook.Worksheets.Add
        
        i = 1
        For Each fld In InRecSet.Fields
            xlDestSheet.Cells(1, i).Value = fld.Name
            i = i + 1
        Next fld
        xlDestSheet.Range("A2").CopyFromRecordset InRecSet
End Sub

Edit: Changed a name in dim but not in code. Now corrected
 

Forum statistics

Threads
1,181,102
Messages
5,928,072
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top