save prompt

[spinkung]

Hi All,

I have a button on a form which exports a query to excel.

i want to be able to give the user a prompt as to where the file should be saved instead of having it fixed in the code.

here's what im using...

'check file already exists and if so then delete it.
    pth = GetXcelPath & "Known Issues Export.xls"
    FileSystem.Dir pth
    If pth = Dir(pth) Then
        FileSystem.Kill pth
    End If
    
'output to excel
    DoCmd.OutputTo acOutputQuery, "query1", acFormatXLS, pth           'Enter table/query to be sent to excel
    
'open excel file
    OpenSpecific_xlFile (pth)

i want the user to be able to slect the save location. can anyone point me in the right direction??

Thanks.

 

[spinkung]

ok, so i've found this

Dim dlgSaveAs As FileDialog

Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
dlgSaveAs.Show

..how do i get the file they have saved into a variable so i can use it to pass the query to??

thanks.

 

[MStef]

Look at "DemoQToExNewPathA2000.mdb" (attachment, zip).
Open Form and try. Look at VBA.