How to Export a query to a new spreadsheet

[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

 

[Allen Browne]

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."

 

[Jan T.]

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 –

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

 

[Noëlla Gabriël]

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

 

[Jason]

Don't know what this does but I se Allen's method.