Exporting Access Query to Excel while creating new sheets and formating

Question

I have a database in Access that in short is a long list of companies, the products they sell, and there fiscal sales for each on of their products. I would like to write some VBA that will:

1.) Allow me to export a Query to Excel. 2.) Create a new Sheet each time the Query is run 3.) Format the data presented.

I also can't seem to figure out how to paste the data from the query into the new sheet. If any kind soul will help me, it would be very much appreciated.

I know all of this is possible, as I've done a good bit of research on it. But I've run out of time and now just need to figure out where I've gone wrong. This is essential what I have seen so far: (Access VBA How to add new sheets to excel?), (Formatting outputted Excel files from Access using VBA?), (https://www.youtube.com/watch?v=9yDmhzv7nns).

Sub Mysub()
Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Dim qdfQUERY2014sales As QueryDef
Dim rsQUERY2014sales As Recordset

Set qdfQUERY2014sales = CurrentDb.QueryDefs("QUERY2014sales")
Set rsQUERY2014sales = qdfQUERY2014sales.OpenRecordset()

Set objexcel = CreateObject("excel.Application")


On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Workbooks.Open("C:\Users\MORTBANKER\Documents\test.xls")
wbExists = True

Openwb:
On Error GoTo 0
If Not wbExists Then
    Set wbexcel = objexcel.Workbooks.Add()
End If

CopyToWorkbook wbexcel
End Sub

Private Sub CopyToWorkbook(objWorkbook As Excel.Workbook)
Dim newWorksheet As Excel.Worksheet
Set newWorksheet = objWorkbook.Worksheets.Add()

 With newWorksheet
.Range("A1") = rsQUERY2014sales
.columns("A:A").HorizontalAlignment = xlRight
.rows("1:1").Font.Bold = True
End With
'Copy stuff to the worksheet here'
End Sub

score 1 · Accepted Answer · answered Jul 14 '15 at 01:04

You need to pass the recordset object into the companion sub and use the Excel.Application object's Range.CopyFromRecordset method to perform the actual operation.

Sub Mysub()
    Dim objexcel As Excel.Application
    Dim wbexcel As Excel.Workbook
    Dim wbExists As Boolean
    Dim qdfQUERY2014sales As QueryDef
    Dim rsQUERY2014sales As Recordset

    Set qdfQUERY2014sales = CurrentDb.QueryDefs("QUERY2014sales")
    Set rsQUERY2014sales = qdfQUERY2014sales.OpenRecordset()

    Set objexcel = CreateObject("excel.Application")
    objexcel.Visible = True


    On Error GoTo Openwb
    wbExists = False
    Set wbexcel = objexcel.Workbooks.Open("C:\Users\MORTBANKER\Documents\test.xls")
    wbExists = True

Openwb:
    On Error GoTo 0
    If Not wbExists Then
        Set wbexcel = objexcel.Workbooks.Add()
    End If

    CopyToWorkbook wbexcel, rsQUERY2014sales

    'need to save the workbook, make it visible or something.
End Sub

Private Sub CopyToWorkbook(objWorkbook As Excel.Workbook, rsQRY As Recordset)
    Dim newWorksheet As Excel.Worksheet
    Set newWorksheet = objWorkbook.Worksheets.Add()

     With newWorksheet
        .Range("A1").CopyFromRecordset rsQRY   '<-magic happens here!
        .columns("A:A").HorizontalAlignment = xlRight
        .rows("1:1").Font.Bold = True
    End With
    'Copy stuff to the worksheet here'
End Sub

You won't be getting field names; that will have to put in from another operation. If you know the field names you might want to store them in a variant array and stuff then into row 1 en masse. I've made the objexcel object visible but have not saved it or closed it.

If you would prefer to loop through the field names instead of dumping an array of strings into row 1, there is an excellent example of a loop in the link I posted above. — , Jul 14 '15 at 03:41
Thank you so much man. I have so much to learn and it is just amazing that people like you out there help newbies like me. — Byron LaFleur, Jul 14 '15 at 20:07

Exporting Access Query to Excel while creating new sheets and formating

1 Answers1