Send a Query or Table to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to send a query or table into Excel. I know I can
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9 - but I don't
really want to make a file - I would like it to open directly in Excel.

Then once it opens in Excel I want to format the cells and size the screen,
can I do this with the VBA code from Access?
 
Use this code to create and open your spreadsheet substituting QName with
the name of your table or query.

I prefer to run the code once without formatting. Then when it is open,
create a macro in Excel to record all your formatting. Then copy and paste
that where I haved 'Do your formatting here' below. You will have to put
"xlsApp." in front of the code you bring from Excel to avoid error messages.

****Code Start****
Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object
On Error Resume Next
Set xlsApp = GetObject(, "Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\temp.xls"
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QName, strPath
DoCmd.SetWarnings True
Set wkbTemp = xlsApp.Workbooks.Open(strPath)
'...
'Do your formatting here
'...
xlsApp.Visible = True
**** Code End ****
 
John,

I was able to get this to do excatly what I needed, thanks.

However I am not getting the formatting to work properly - You I get a small
example?
 
Back
Top