HOt to let the user specify the location of an Excel file output from

[gurbaitis]

I found this free code, which works fine:


Private Sub ExportOneTable()

'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database

'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "MySpreadSheet.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\My Documents\MyDatabase.mdb"
strTable = "MyTable"

Set objDB = OpenDatabase(strDB)

'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing

End Sub

...However, it always exports to my mydocuments folder. Usually if I'm just exporting a single table I use:

DoCmd.OutputTo acOutputTable, "table_name", acFormatXLS, , False

and it allows the user to rename te file and/or save it in another location.

So, the $64,00 Question - how do I combine the 2 features - letting me set up an Excel Workbook with multiople sheets, AND allow the use to select where it goes?

 

[Bong]

I don't do much with Access but I think I see the problem. Your Excel file name is given with strExcelFile = "MySpreadSheet.xls". I assume that "c:\my documents" is the default. You could (at least in Excel you could) open a dialog to ask for the path. Then, say that dialog returns a string, strPth. Then, strExcelFile = strPth & "MySpreadSheet.xls"

_________________
Bob Rashkin

 

[gurbaitis]

Thanks. Yes, that would work, but it would require them to not only type in the whole path, but know where it is exactly to type it in - which I know they are going to balk at. But thanks for the input!