Exporting to Excel, not to a set filename / path

[MikeDuffield]

Morning all,

Currently using this code:

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
 On Error GoTo ErrMan
 
strExcelFile = "G:\whatever\whatever.xls"
strWorksheet = "WorkSheet1"
strDB = "[URL="file://\\whatever\whatever.accdb"]\\[COLOR=#000000]whatever\whatever[/COLOR].accdb[/URL]"
strTable = "maindatatbl"
Set objDB = OpenDatabase(strDB)
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
  "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
   "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
Dim xlWkbk As Object
Set xlWkbk = CreateObject("Excel.Application")
With xlWkbk
.Workbooks.Open "G:\whatever\whatever.xls"
.Visible = True
End With
Exit Sub
ErrMan:
 MsgBox "Exported file is already open - cannot export at this time", vbCritical, "Oops!"

How do I go about making it open a box for the user to choose where to export the data to?

I'm sure it's simple but I can't find a solution anywhere!

Thanks in advance,
Mike.

 

[Trevor G]

For the open excel dialog you need this command line, which starts at the application level

Application.Dialogs(xlDialogOpen).Show