How to get Access VBA to export query into Excel file

[sagain2k]

I have an Access macro that currently exports an Access query into a new Excel workbook with a given path and name. Not getting any response from the Access forum so I'm trying here too. How can you tell it to export (insert) into an exisitng Excel workbook instead? Here's the current code that from within Access creates an Excel file with the Query data in it:

DoCmd.OutputTo acQuery, "qryCourseScheduleRpt", "MicrosoftExcel(*.xls)", _
"q:\qryResults\EngCourseSched.xls", True, ""

What I want is to be able to have a template Excel workbook that any query can be exported into (and that template Excel workbook will have macros associated with it that can then format the imported query data.

Thanks for any suggestions!

 

[SydneyGeek]

I don't have Access on this machine, but try the TransferSpreadsheet method. I regularly use it to pull data INTO Access from Excel, and I think it can go the other way.
If that doesn't work, and Access insists on completely replacing the file, you could consider having a the code in a second spreadsheet and pointing it at the exported data.

Denis

 

[sagain2k]

Thanks for the input. I already can use Excel VBA to import data into an Access table via Transfer Spreadsheet no problem. And can create a "new" occurrence of a named Excel file from exporting an Access Query. The problem is how to not have it replace the existing file of the same name but just insert the data into it. Since it always replaces that file, there's no way to then have a macro (attached to that workbook) run---Access is always creating a brand new occurrence of the file, replacing the previous one. It would be great if exporting the query to the new Excel Workbook it could then run an Excel macro.

I don't know how to "Transfer" a table into an Excel workbook/sheet, doing the reverse of importing a worksheet into a table. That would be a solution.

I'll have to keep trying different things...thanks again.

 

[SydneyGeek]

Ok, here's another idea.

Use whatever method you like to get the query into Excel. Don't worry if it replaces the existing file (Data.xls).
You also need another workbook (Code.xls) which contains the macros for sorting, filtering, and/or formatting the Data file.
Then the routine becomes:
1. Run the export
2. Call the Excel routine from Access, to format the output.
3. Display a message: if desired, open the Data file to view the final result.

Here's an edited version of a routine I've used in the past, cobbled together with your starting point --

Option Explicit

Const fPath = "C:\My Documents\" 'adjust to suit
Const fname = "Formatter File.xls" ' Code file
Const fName2 = "Exported Data.xls" ' Data file to overwrite

Function OpenExcel()

On Error GoTo ErrorHandler

   Dim appExcel As Excel.Application
   Dim bks As Excel.Workbooks
   Dim sht As Excel.worksheet
   Dim strSheet As String
   Dim MyFile As String
   
   MyFile = fPath & fname2 'Point to the Data file

   DoCmd.OutputTo acQuery, "qryCourseScheduleRpt", "MicrosoftExcel(*.xls)", _
MyFile, True, "" 

   'Open the Code file and run the processing macro
   MyFile = fPath & fName ' point to the Code file
   Set appExcel = GetObject(, "Excel.Application")
   appExcel.Workbooks.Open (MyFile)
   Set sht = appExcel.ActiveWorkbook.Sheets(1)
   sht.Activate
   'appExcel.Application.Visible = True
   With appExcel
    .Run "OpenAndProcess" 'Change to suit name of Excel routine
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err = 429 Then
'Excel is not running; open Excel with CreateObject
      Set appExcel = CreateObject("Excel.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Denis

 

[dk]

Hi,

This may also be of use:-

Export an Access table/query to a specified Excel workbook and worksheet

HTH
Dan

 

[Norie]

I answered your post in the other forum:

Exporting Access Query data into existing Excel Workbook?

 

[Norie]

This short Access VBA code will export every query in the current database to the Excel file AllQueries, with each worksheet tab having the name of the query:

Sub ExportAllQueries()

Dim qdf As QueryDef

Dim db As Database

    Set db = CurrentDb
    
    For Each qdf In db.QueryDefs
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qdf.Name, "AllQueries.xls"
    Next
End Sub