Exporting Access Query to Excel (into a temp file)

[ecrespol]

I'm trying to find a way to export my query results to a new blank workbook (some might call this an excel instance). So far all I can do is export the result into an excel file then open it using the following;

Private Sub cmdEXPORT_EXCEL_Click()

DoCmd.TransferSpreadsheet acExport, 8, "qryBASE_DATA_QUERY2", "C:\WINNT\TEMP\TEMP_MASTER_EXP_EXPORT"
Application.FollowHyperlink "C:\WINNT\TEMP\TEMP_MASTER_EXP_EXPORT.XLS", , True

End Sub

What I would like to do is, everytime the user clicks the export button it opens a new workbook in excel (even if they do multiple exports). Then when the user goes into excel they can then save these temp workbooks wherever they wish.

I hope this makes sense.

 

[Ikon]

I'm trying to find a way to export my query results to a new blank workbook (some might call this an excel instance). So far all I can do is export the result into an excel file then open it using the following;

Private Sub cmdEXPORT_EXCEL_Click()

DoCmd.TransferSpreadsheet acExport, 8, "qryBASE_DATA_QUERY2", "C:\WINNT\TEMP\TEMP_MASTER_EXP_EXPORT"
Application.FollowHyperlink "C:\WINNT\TEMP\TEMP_MASTER_EXP_EXPORT.XLS", , True

End Sub

What I would like to do is, everytime the user clicks the export button it opens a new workbook in excel (even if they do multiple exports). Then when the user goes into excel they can then save these temp workbooks wherever they wish.

I hope this makes sense.

The way to make a new workbook is this:

Sub newBook()
  Dim myExcel As Excel.Application
  Dim myBook As Excel.Workbook
  Dim mySheet As Excel.Worksheet

  Set myExcel = CreateObject("Excel.Application")

  myExcel.Visible = True

  Set myBook = myExcel.Workbooks.Add(1)
  Set mySheet = myBook.Worksheets(1)

  ' All your code here
  ' ----- 


  Set myBook = Nothing
  Set mySheet = Nothing
  Set myExcel = Nothing

End Sub

Then just refer to the workbook object mySheet to copy your data into.

Obviously you need to reference the Microsoft Excel x.x Object Library in your project.

I hope this helps.

Best regards
- Mike


Edit: I know this is for an old post but hopefully it helps someone else who needs it.