Export query to excel using the Save As prompt window.

[PuddinPie]

Hello,

I know this is probably an easy question to answer but I have not been able to find it yet.

I'm trying to export a query to excel but I would like to have the Save As window come up so that the user can choose where they are saving it and for the file name I would like to use the resault of a string set by a combo box.

Here's what I have so far and it works to save the excel file and use the combo box selection as a name.

Function QueryExportToExcel()
On Error GoTo Error_Handler
Dim stQueryToExport
stQueryToExport = [Forms]!frmAvailQueries.cboAvailableQueries
DoCmd.OutputTo acOutputQuery, stQueryToExport, acFormatXLS, "C:\" & stQueryToExport & ".xls", , , , acExportQualityPrint
Error_Handler:
If err.Number = 94 Then
MsgBox "Please remember to select something from the drop down list first."
Exit Function
End If
End Function

Thank you

 

[Alansidman]

Add an input box to your code to prompt the user for the path.

Here is how to do it.

http://office.microsoft.com/en-us/access-help/inputbox-function-HA001228856.aspx

Alan

 

[PuddinPie]

Is there not a different way? I would rather the EU be able to browse for there folder because its all networked and very few people actualy know correct paths.

 

[CBrighton]

I thought OutputTo automatically gave a file browser prompt if you didn't provide the location in the command?

I'm fairly sure it does if you call it via a macro rather than via VBA, but I've always used a specified location when calling it via VBA.


However it looks like you want to split it into a file browser window selecting the folder and a combobox selecting the file name?

If this is the case you may need to do a google search on how to use the windows file browser APIs in VBA and see if there's one which is specifically browsing for folders instead of files.


:edit:

Try this for folder browsing:

'----------------------------------
'- macro to get a folder
'--------------------------------------
Option Explicit
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long
'- API function
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
 
 
'=================================================
'- THIS RUNS THE WHOLE MACRO TO GET THE SELECTION
'=================================================
Sub BrowseFolders()
    Dim Msg As String
    Msg = "Please select a folder."
    MsgBox GetDirectory(Msg)
End Sub
'
'-------------------------
'- this does the API call
'-------------------------
Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
'---------------------------
'   Root folder = Desktop
    bInfo.pidlRoot = 0&
'---------------------------
'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
'---------------------------
'   Type of directory to return
    bInfo.ulFlags = &H1
'---------------------------
'   Display the dialog
    x = SHBrowseForFolder(bInfo)
'---------------------------
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function
'----------------------------------------------------

Source: http://www.mrexcel.com/forum/showthread.php?t=72939

:edit2:

Code looks like it's used like this (I've not tested it):

strFolder = GetDirectory("Select location to save to")