How do I get a common dialog box for Excel export 2

[SmallTime]

Hi all,

Could some give me some guidance on how to get a dialog box to ask for a location when exporting to excel. Here’s an example of what I’m doing at the moment;

eg DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, “QryTest”, "C:\test.xls", True

I’ve had a look at ‘How to use the Common Dialog API in an Access database’ on the Microsoft Knowledge Base and it seems to be just what I want, except the example they give is good for retrieving a file name and I don’t know how to amend the code to suit my purposes.

Just in case there’s anyone out there who could do the trick here’s the link:

http://support.microsoft.com/kb/303066/en-us

Kind regards and many thanks

 

[Remou]

A search for Browse Folder should help.

 

[mp9]

Search Access VBA help for FileDialog Object...?

http://www22.brinkster.com/accessory/

 

[SmallTime]

Thanks guys,

I did look high and low before asking the question, but as always, I was probably looking in all the wrong places. I'll continue the hunt this evening, starting with your suggestions.

Thanks

 

[TheAceMan1]

How are ya SmallTime . . .

According to my library the API [blue]SHBrowseForFolder[/blue] should do the trick. It opens a [blue]browse for folder dialog[/blue] with cancel button. Returns are the [blue]full path[/blue] of the selected dir or [blue]an empty string[/blue] if cancel is pushed.

Credit for the API goes to: [blue]KPD-Team 1998[/blue]

http://www.allapi.net

I've modified the code slightly so you can call it from any form/subform. Here goes . . .
[ol][li]In a new module in the modules window copy paste the following:

[blue]Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type

Const BIF_RETURNONLYFSDIRS = 1
Const MAX_PATH = 260

Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" _
                         (ByVal lpString1 As String, _
                          ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" _
                         (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" _
                         (ByVal pidList As Long, _
                         ByVal lpBuffer As String) As Long

Public Function GetFolder(frm As Form)
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo

    With udtBI
        .hWndOwner = frm.hwnd
        .lpszTitle = lstrcat("C:\", "")
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    lpIDList = SHBrowseForFolder(udtBI)
    
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        SHGetPathFromIDList lpIDList, sPath
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        
        If InStr(sPath, vbNullChar) Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If

    GetFolder = sPath
End Function[/blue]

[/li]
[li]From any form calls would look like:

[blue]   Me!TextboxName = GetFolder(Me)
      or
   [i]variable[/i] = GetFolder(Me)[/blue]

[/li][/ol]

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .

 

[SmallTime]

Very nice indeed.

got it to do just what I wanted with minor trickery on the form.

Absolutely beautiful.

Having done all that, I then played around with 'OutputTo'

What would the drawback be with using something like this
DoCmd.OutputTo acQuery, "QryTest", "MicrosoftExcel(*.xls)", "", True,

 

[TheAceMan1]

SmallTime . . .

Start a new post so all may benefit! . . .

See Ya! . . . . . .

 

[DougP]

Nice code TheAceMan1

have an'oth'a star

DougP, MCP, A+