Transfer Spreadsheet to new instance of Excel

[kvar]

This should be simple but it's making me crazy.
I just want to run a series of DoCmd.TransferSpreadsheet commands to export several queries to one Excel Workbook with seperate sheet names for each query.
However my problem is I want this to be a new instance of Excel that the user will then SaveAs after it is complete. I don't want to save it to a specific path first because the Db is on a shared drive and My Documents will have a different path for each user based on their user name. (corporate environment)
There must be some way to just have it open a new workbook without saving isn't there??

Thank you in advance!

 

[Rx_]

Under Apps, there exist an Excel forum.
Yes, there is a way.

My fav method is to create an Excel Object Variable. Create the entire Excel Workbook, format it, run some other cool stuff, then use a custome Save As code to save each users result in a custom folder with a custom date time format as part of the name.

 

[kvar]

In order to hard code the saving of a file, or use a template I would have to have a path and file name that each user would be able to access. That doesn't exist so I have to create a new file with no set name each time and let the user save it as they wish.
Even though all the users have access to the share drive where the Db sits some have it as N, some as Z, etc. There is no way to have a set path.

 

[Rx_]

My preference is ObjXL as Excel.Application - check to insure there isn't something already in scope first.

If objxl Is Nothing Then
    Set objxl = New Excel.Application
    objxl.EnableEvents = False
 Else
    Excel.Application.Quit
    DoEvents
    Set objxl = New Excel.Application
    objxl.EnableEvents = False
   End If
On Error GoTo PROC_Error
  objxl.Visible = False    ' during creation , hide Excel                                                            objxl.Workbooks.Add
  objxl.Worksheets.Add
  intWorksheetNum = 1

Good point about the template, since they might not be available on each user's PC. I don't use them. Just program in the template information provides more stabality.

You can programatically pop-up the Save As dialogue (with a recommendation) if there isn't enough consistency on the network between users.

http://btabdevelopment.com/export-a-table-or-query-to-excel-to-specific-worksheet/
This code will get you started.

To add Excel as an object variable:



Once Excel is in an object variable, it can be saved after it is created:

DirName = strNewReportPath
          If Dir(DirName, vbDirectory) = "" Then
''
'                   If MsgBox("Is it OK to create a new folder in X:\Regulatory Database Reports\" & _
                              UserLogin & "\Drilling Plan? (recommended yes)", vbOKCancel) = vbOK Then
            If MsgBox("Is it OK to create a new folder in " & UserPath & "? (Recommended Yes)", vbOKCancel + vbQuestion) = vbOK Then
                    DirName = UserPath
                    MkDir DirName
                    Err.Clear
                  Function", "line MkDir")
            Else
                MsgBox "Create new folder cancelled. Folder not created.", vbOKOnly, "Report Cancelled, must allow folder to be created"
                    Exit Function
            End If
          Else
              'MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
              ' comment out msgbox if it exist, don't bother letting the user know
                        End If