Need some help with code exporting queries to specific excel sheets?

[BigMike]

Thank you! That looks like something I can handle. I can't do it until I'm back in the office on monday, but I'll try it then and let you know how it works out.

 

[BigMike]

I named a filepath to a specific workbook when I ran the code successfully before, so I tried using your second way and I got "Compile error: User-defined type not defined". I put brackets [] around the items that were highlighted in the code below. [Public Sub ExportQueries...As String] was highlighted yellow and [objFileDialog...FileDialog] was highlighted blue.

Public ApXl As Object 'Represents the Excel objext
Public xlWBk As Object 'represents the workbook
Public blnWkBkOpn  As Boolean 'indicates if the workbook has already been opened - for use when saving multiple queries
Option Compare Database
[Public Sub ExportQueries(strRecordSource As String, strSheet As String, blnLastQuery As Boolean, Optional strWkBookPath As String)]
'strRecordSource Name of Record source (table or Query) to export
'strSheet Name of worksheet to export to
'blnLastQuery True=last export so close workbook, False = more to export so DONT close the workbook
'strWkBookPath optional path of workbook to use. If left out of call, a new workbook will be added
' ______________________________________________________________________
    Dim rst As DAO.Recordset
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim blnNewBk As Boolean 'indicates True new workbook or False existing workbook
    Dim [objFileDialog As Office.FileDialog]
    Dim strSaveFile As String
    Set objFileDialog = Application.FileDialog(msoFileDialogSaveAs)
 
 
    blnNewBk = Len(strWkBookPath) = 0
 
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler

 

[Isskint]

FYI The yellow shows the line the error occurred and the blue the item causing the error.

Check you have a reference to the current Office object library. From the Visual Basic Editor,;
Tools menu, click References
In the References dialog box, click Available References
The active (selected items) are listed at the top. If you do not see Microsoft Office xx.x Object Library (where xx.x is your version of Office), then scroll down the list and select.

 

[BigMike]

Works perfectly! I really appreciate all the help you have given me. There is no way I would have figure any of this stuff out by myself, so thank you very much, Isskint!

 

[BigMike]

Actually, I just now thought of something. Instead of having the code close down the excel program in the end, would it be easier to have the command button open excel, export the queries, and just leave excel open so the user can "save as" and close it manually? If this can be done, then ignore my question below. If not, then please keep reading.

I click my command button, it opens up and exports everything to excel, but the "save as" window pops up behind the excel workbook window. Can I get the "save as" window to pop up in front of the excel window?

 

[Isskint]

Just remove the code from If blnLastQuery to End If. This will prevent the spreadsheet closing.

OR

Never pass True into the routine for blnLastQuery, always set that as False

 

[BigMike]

Alright it worked well. Again, thank you for all of your help! It is greatly appreciated.