Format Transferred Excel Spreadsheet as Table using Access VBA

[Matt063727]

I need help with my code to format an Excel Workbook from Access 2013.

I have 4 queries that I am exporting to Excel from Access.

My goals are to:
choose the file path (check)
Export the queries into separate sheets (sort of)
Format the data as a table (help!)

I want to do all this from a single button click in Access using VBA

I have successfully exported the queries using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", filename, True
 

but they show up as a group when I look at the exported file.
I am not sure if this is a problem for later or not.

I've tried recording a MACRO in excel and editing that to get my formatting done, but I've heard that I should try to avoid "Select" and don't know enough about the library to get around that.

I've scoured the web and found a start, but this only formats cells A1 and A2. I have added the Excel Object Library.

Public Function formatTable(filename As String)
    'MsgBox "In format", vbExclamation, ""
 
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objXLSheet As Excel.Worksheet
 
 
    'open the Excel spreadsheet with the exported data
    Set objXLApp = CreateObject("Excel.Application")
    'for testing, make the application visible
    objXLApp.Visible = True
    Set objXLBook = objXLApp.Workbooks.Open(filename)
    MsgBox "here 1 "
    objXLBook.Sheets("Query1").ListObjects.Add(xlSrcRange, Range("$A$1"), , xlYes).Name = "Table1"
    MsgBox "here 2"
    objXLBook.Sheets("Query1").ListObjects("Table1").TableStyle = "TableStyleMedium9"
 
 
    objXLApp.PrintCommunication = False
 
    'save the changes
    objXLBook.Save
    objXLApp.Quit
    Set objXLApp = Nothing
 
 
End Function
 

I also want to autofit the whole column

The data will always have the same number of columns but the number of rows will change.

Please help me learn how to use VBA smartly and format my output.

I appreciate the help.

[NeoPa]

Hi Matt.

The first point is to make sure you have one clear question in each thread. Things get messy and confusing very quickly when multiple people try to answer multiple questions all in the same thread. Not good.

When I mention clarity that requires precise specifications of exactly what you're looking for. Not so much a list of general ideas.

First let's start with some general advice about how to get more familiar with doing Excel VBA in Access. The simple point there is to develop it in Excel first. Excel has a great deal of help on how to go about doing things. Once you have that code (& understanding) you can take it as it is and put it into Access. It mostly won't work just like that unfortunately.

Getting it to work in Access means that you have to find all the places where Excel code uses defaults (EG. Range() defaults to ActiveSheet.Range().) and make the defaults explicit. Compiling is very much your friend here. Try it and the compiler will find and flag all references it doesn't understand.

NB. Obviously, compile it and test it as much as possible in Excel before you even start to think of porting it across to Access. Not to do it that way would be asking for trouble, and reliably getting it.

As a bonus for now, autofitting the data in a column can be done this way. I'm using column A as I have no information which column you need and what you may have in code that refers to that.

With Columns("A")
    .ColumnWidth = 255
    Call .AutoFit()
End With

Where multiple columns are required then the standard format for the Range() function can be used. EG. Columns("A:G")

[ADezii]

Here is a simple Code Segment that should clearly illustrate how to AutoFit a single Column in an Excel Spreadsheet, using Automation, via Early Binding. In the example AutoFit is applied to Column 'B' of Worksheet 'Shhet2' in 'C:\Test\Test.xlsx'.

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim sht As Excel.Worksheet
 
Set appExcel = New Excel.Application
 
Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xlsx")
Set sht = wkb.Worksheets("Sheet2")
 
sht.Columns("B").AutoFit
 
wkb.Close True
appExcel.Quit
Set appExcel = Nothing
 

P.S. - Hello NeoPa, still as sharp as ever!

[NeoPa]

Hi Armund.

Good to hear from you :-)

Notice in my code sample I called the AutoFit() second after first stretching the column to maximum width. That's to ensure AutoFit() uses the full width necessary and doesn't find a narrower option that works only if some of the data is wrapped.

[ADezii]

@NeoPa:
Always nice to hear from an old friend! As far as stretching the Column Width to Maximum, I assumed that AutoFit would always adjust to the full width necessary. I learned yet another trick from you, thanks.

[NeoPa]

Always a pleasure.

TBH it's not often that comes up but I fell foul of it a while ago so now just use that trick as standard - both in code and when using the interface.

Stay well my friend :-)