Export query to Excel and put data into table MS Access 2013 VBA

Question

I have already been able to export a query from MS Access to an Excel workbook and autoformat the column widths and other settings, but I cannot find out how to put this data into a table. I found the command to create a table which is this:

Sheet1.ListObjects.Add(xlSrcRange, Range("A1:D10"), , xlYes).Name = "myTable1"

but that is hardcoding the size of the table. Since I am exporting multiple queries, I want to have a modular function which will take queries of different column/row lengths and create tables for all of them without having to manually type the size. Here is some of my code:

Private Sub dumpQueries(path As String)

    Dim obj As AccessObject, dB As Object
    Set dB = Application.CurrentData
    For Each obj In dB.AllQueries
        testBool = InStr(obj.name, "Sys")
        If testBool <> True Then
            If obj.name = "example1" Or obj.name = "example2" Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, obj.name, path, True, editWorksheetName(obj.name)
            End If
        End If
    Next obj

End Sub


Private Sub formatFile(path As String)

Dim Date1 As Date, strReportAddress As String
Dim objActiveWkb As Object, appExcel As Object
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
appExcel.Application.Workbooks.Open (path)

Set objActiveWkb = appExcel.Application.ActiveWorkbook
With objActiveWkb
    Dim i As Integer
    For i = 1 To .Worksheets.count
        .Worksheets(i).Select
        Set sht = Worksheets(i)
        Set StartCell = Range("A1")
        .Worksheets(i).Cells.Select
        .Worksheets(i).Cells.EntireColumn.AutoFit
        .Worksheets(i).UsedRange
        LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
        LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column
        sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
    Next
End With

appExcel.ActiveWindow.TabRatio = 0.7
objActiveWkb.Close savechanges:=True
appExcel.Application.Quit
Set objActiveWkb = Nothing: Set appExcel = Nothing

End Sub

There is a lot more code but this is the relevent stuff. This is where I create the excel files and format them. Any idea how to put this data directly into a table?

Update: I fixed all the errors I was getting but it still doesn't create a table with all the data. I edited my code above to be completely updated.

Fixed this problem, but new one came up. Please go to VBA Run-time error 1004: Method Range of object _Global failed when trying to create tables in Excel 2013 if you can help.

Can you update your code with hardcoded lines that work - someone here can then show you how to change it so it's flexible. I don't really know what your ultimate goal is. — dbmitch
Follow this link, I fixed some of the problems but ran into a new one stackoverflow.com/questions/37755435/… — Michael

Parfait Parfait · Accepted Answer · 2016-06-10T19:38:11

Consider using QueryTables and specify the upper left corner destination and specific query. Below is Excel VBA code where you import via ODBC from external Access database:

Dim constr As String

constr = "ODBC;DRIVER=Microsoft Access Driver (*.mdb, *.accdb);" _
           & "DBQ=C:\Path\To\Database\File.accdb;"

With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                 Source:=constr, _
                                 Destination:=Range("$A$1")).QueryTable

     .CommandText = "SELECT * FROM [Table]" 
     .ListObject.DisplayName = "TableName" 
     .Refresh BackgroundQuery:=False 
End With

Export query to Excel and put data into table MS Access 2013 VBA

2 Answers