By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Format Transferred Excel Spreadsheet as Table using Access VBA

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", filename, True
  2.  
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function formatTable(filename As String)
  2.     'MsgBox "In format", vbExclamation, ""
  3.  
  4.     Dim objXLApp As Excel.Application
  5.     Dim objXLBook As Excel.Workbook
  6.     Dim objXLSheet As Excel.Worksheet
  7.  
  8.  
  9.     'open the Excel spreadsheet with the exported data
  10.     Set objXLApp = CreateObject("Excel.Application")
  11.     'for testing, make the application visible
  12.     objXLApp.Visible = True
  13.     Set objXLBook = objXLApp.Workbooks.Open(filename)
  14.     MsgBox "here 1 "
  15.     objXLBook.Sheets("Query1").ListObjects.Add(xlSrcRange, Range("$A$1"), , xlYes).Name = "Table1"
  16.     MsgBox "here 2"
  17.     objXLBook.Sheets("Query1").ListObjects("Table1").TableStyle = "TableStyleMedium9"
  18.  
  19.  
  20.     objXLApp.PrintCommunication = False
  21.  
  22.     'save the changes
  23.     objXLBook.Save
  24.     objXLApp.Quit
  25.     Set objXLApp = Nothing
  26.  
  27.  
  28. End Function
  29.  
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.
Apr 14 '17 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. With Columns("A")
  2.     .ColumnWidth = 255
  3.     Call .AutoFit()
  4. End With
Where multiple columns are required then the standard format for the Range() function can be used. EG. Columns("A:G")

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,418
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.
Expand|Select|Wrap|Line Numbers
  1. With Columns("A")
  2.     .ColumnWidth = 255
  3.     Call .AutoFit()
  4. End With
Where multiple columns are required then the standard format for the Range() function can be used. EG. Columns("A:G")
Apr 15 '17 #2

ADezii
Expert 5K+
P: 8,623
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'.
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wkb As Excel.Workbook
  3. Dim sht As Excel.Worksheet
  4.  
  5. Set appExcel = New Excel.Application
  6.  
  7. Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xlsx")
  8. Set sht = wkb.Worksheets("Sheet2")
  9.  
  10. sht.Columns("B").AutoFit
  11.  
  12. wkb.Close True
  13. appExcel.Quit
  14. Set appExcel = Nothing
  15.  
P.S. - Hello NeoPa, still as sharp as ever!
Apr 15 '17 #3

NeoPa
Expert Mod 15k+
P: 31,418
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.
Apr 15 '17 #4

ADezii
Expert 5K+
P: 8,623
@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.
Apr 15 '17 #5

NeoPa
Expert Mod 15k+
P: 31,418
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 :-)
Apr 16 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.