Save to Directory

[Tim]

I'm using the TransferSpreadsheet method to transfer a query to an
excel spreadsheet. But this method seems to force me to specify a
path and file name ahead of time.
I'd like to give that option to the user in a standard (select
directory) format. All I can find is code on retreiving a FILE, not a
directory. Any thoughts? My code is below for what it's worth.

Copied from examples found at
http://www.rogersaccesslibrary.com/TableOfContents3.asp

Sub ExportSpreadsheet()
On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook

Dim db As DAO.Database

Set db = CurrentDb
conPath = "c:\files\temp\"

'delete the spreadsheet
Kill conPath & "TestSpreadsheet.xls"

DoCmd.TransferSpreadsheet acExport, , "queCustomer_Reporting",
conPath & "TestSpreadsheet.xls", True
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"TestSpreadsheet.xls")
objXLApp.Visible = True

ProcDone:
On Error Resume Next

' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing



ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"Generic.xlt")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub

 

[Rob Oldfield]

http://www.mvps.org/access/api/api0002.htm