How to Export a query to a new spreadsheet

  • Thread starter Thread starter Jan T.
  • Start date Start date
J

Jan T.

Using Access 2007 and Excel 2007.
I want to export a query to an Excel spreadsheet. This is easy to do
manually
in Access 2007 by right click on the query and chose Export to Excel.
A wizard
then pops up and I have to give it a file path where to save the file
and file format.
I also choose Export with formats ... and so on and Open file after
exporting...
My query appears in a new workbook. Very easy.

However I want to do this from an Access form with a click of a
button.
Somebody knows how to write this vba code?

Appriciate your help.

Jan
 
Use TransferSpreadsheet in a macro, or DoCmd.TransferSpreadsheet in VBA
code.

If you can't find it in the macro, click the ribbon button for "Show All
Actions."
 
Use TransferSpreadsheet in a macro, or DoCmd.TransferSpreadsheet in VBA
code.

If you can't find it in the macro, click the ribbon button for "Show All
Actions."

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







– Vis sitert tekst –
Click to expand...


Thanx!
That was super!! Excactly what I needed...
worked perfectly!
Jan
 
Hi,

in fact you just create an excel object, and from there you're in Excel VBA
code.
Here's an exmaple how to open an existing excel file and alter its titles:

Public Sub AddTitles(strFile As String, intRows As Integer, astrTitles() As
String)
On Error GoTo Err_AddTitles

Dim appExcel As Excel.Application
Dim bksBooks As Excel.Workbooks
Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet
Dim intArrRow As Integer


'Initiate all objects and variables
Set appExcel = GetObject(, "Excel.Application")
Set bksBooks = appExcel.Workbooks
Set wkbBook = bksBooks.Open(strFile)
Set wksSheet = wkbBook.Sheets(1)
wksSheet.Activate
ActiveCell.Rows("1:" & intRows + 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
For intArrRow = 0 To intRows - 1
Range(astrTitles(intArrRow, 0)) = astrTitles(intArrRow, 1)
Next intArrRow
appExcel.Visible = True


Exit_AddTitles:
Exit Sub

Err_AddTitles:
If Err.Number = 429 Then 'excel not running
Set appExcel = CreateObject("excel.application")
Resume Next
Else
Call gsgErrorHandling
Resume Exit_AddTitles
End If

End Sub
 
Back
Top