Solved

API CALL comdlg32.dll

Posted on 1998-09-10
16
721 Views
Last Modified: 2008-01-16
I am outputting the query's result as an xl format. At this instance I want to give the user to be able to select the directory to save to.
This is what I am using right now under a command button.

DoCmd.OutputTo acOutputQuery, "QryGL Entries_XL_output", acFormatXLS, "c:\GL Entries.xls", 0
MsgBox "The file 'GL Entries.xls' is created at 'C:\'", 64, "File Done"
Instead of c:\ the user should be able to select wherever HE/SHE decides to.
I would prefer the solution ASAP.
Thanks.
Devtha
0
Comment
Question by:devtha
  • 11
  • 3
  • 2
16 Comments
 
LVL 6

Author Comment

by:devtha
ID: 1961830
I also need the function that will do the job along with the right declaration.
Thanks
Devtha
0
 

Expert Comment

by:lanzerstorfer
ID: 1961831
Try following code:

Option Compare Database
Option Explicit

Type WLIB_OFFICEGETFILENAMEINFO
    hwndOwner As Long
    szAppName As String * 255
    szDlgTitle As String * 255
    szOpenTitle As String * 255
    szFile As String * 4096
    szInitialDir As String * 255
    szFilter As String * 255
    nFilterIndex As Long
    lView As Long
    flags As Long
End Type

Declare Function MSAU_OfficeGetFileName Lib "msaccess.exe" Alias "#56" (gfni As WLIB_OFFICEGETFILENAMEINFO, ByVal fOpen As Integer) As Long


Function SelectDir() As String

On Error GoTo Err_SelectDir
   
    Dim stDir As String
    Dim ofn As WLIB_OFFICEGETFILENAMEINFO
   
    ofn.hwndOwner = hWndAccessApp
    ofn.szAppName = "AppName" & Chr$(0)
    ofn.szDlgTitle = "Search Directory" & Chr$(0)
    ofn.szOpenTitle = "Select" & Chr$(0)
    ofn.szFile = "output.xls" & Chr$(0)
    ofn.szInitialDir = "D:\" & Chr$(0)
    ofn.szFilter = Chr$(0)
    ofn.nFilterIndex = 0
    ofn.lView = 0
    ofn.flags = &H20
   
    If (MSAU_OfficeGetFileName(ofn, True) = 0) Then
        stDir = RightTrim(ofn.szFile)
        SelectDir = stDir
    End If
   
Exit_SelectDir:
    Exit Function
   
Err_SelectDir:
    MsgBox "Error in stp_SelectDir (" & Err.Number & "): " & Err.Description
    Resume Exit_SelectDir
   
End Function

Public Function RightTrim(strToTrim As String) As String

    On Error GoTo Err_RightTrim
   
    Dim posOfNull As Integer
   
    posOfNull = InStr(1, strToTrim, Chr$(0))
    RightTrim = Left$(strToTrim, posOfNull - 1)
   
Exit_RightTrim:
    Exit Function
   
Err_RightTrim:
    MsgBox "Error in RightTrim (" & Err.Number & "): " & Err.Description
    Resume Exit_RightTrim

End Function

0
 
LVL 6

Author Comment

by:devtha
ID: 1961832
Could you please step through the code. Specially the declaration section and also would like to know that if I can use this in VB, as you are declaring the LIBRARY

Declare Function MSAU_OfficeGetFileName Lib "msaccess.exe" Alias "#56" (gfni As WLIB_OFFICEGETFILENAMEINFO, ByVal fOpen As Integer) As Long  



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:lanzerstorfer
ID: 1961833
I think this code only works satisfied within MS-Access, because the resource of the dialog in defined in msaccess.exe. If you use the code in VB, msaccess.exe must be in memory in order to perform the function properly (stupid, isn't it?)

Months ago I wrote a similar function in win32sdk. There I had to deliver a dialog template, a HOOK-function and a special callback-function in order to get a directory search dialog-box. I am not quite sure if these techniques are available in VB.

Perhaps, an other way to display a directory search dialog-box is to call the SHBrowseForFolder API-function.


0
 
LVL 6

Author Comment

by:devtha
ID: 1961834
Your function works.. but there is a limitation....
However I would be glad to get your input on SHBrowseForFolder API-function. Please provide input..
Thanks
Devtha

0
 
LVL 6

Author Comment

by:devtha
ID: 1961835
Sorry I waited 5 days. If no one comes up with the answer I may think to give you some points.
0
 
LVL 1

Accepted Solution

by:
lina10 earned 100 total points
ID: 1961836
hello!
why don't you use the common dialog ocx? it is easy to use-it has properties to set on opening. in code i use:
                act1.ShowOpen
                filename = act1.filename
hope it helps
Lina
0
 
LVL 6

Author Comment

by:devtha
ID: 1961837
Lina,
     The functionality is desired in Access 8.
Perhaps you can share more thoughts on OCX and direct me in that
direction.
Thanks
0
 
LVL 1

Expert Comment

by:lina10
ID: 1961838
Hi! Just checked and it works:
place the common dialog ocx on your form, set some initial properties and write those few lines of code:
Dim fName As String

    act1.ShowSave
    fName = act1.FileName
    If fName <> "" Then
            DoCmd.OutputToacOutputQuery,"QryGLEntries_XL_output", acFormatXLS, fName, 0
    End If
it gives the user the option to give the file any name(and you  can set the default filenamename)
Lina
0
 
LVL 6

Author Comment

by:devtha
ID: 1961839
Hi Lina,
        I tried inserting activex control and then selecting "common dialog" pops up a message that tells me I do not have a license. To obtain....
Where do I get a license?
Thanks.

0
 
LVL 6

Author Comment

by:devtha
ID: 1961840
Hi Lina,
        I tried inserting activex control and then selecting "common dialog" pops up a message that tells me I do not have a license. To obtain....
Where do I get a license?
Thanks.

0
 
LVL 6

Author Comment

by:devtha
ID: 1961841
Hi Lina,
        I tried inserting activex control and then selecting "common dialog" pops up a message that tells me I do not have a license. To obtain....
Where do I get a license?
Thanks.

0
 
LVL 6

Author Comment

by:devtha
ID: 1961842
Hi Lina,
        I tried inserting activex control and then selecting "common dialog" pops up a message that tells me I do not have a license. To obtain....
Where do I get a license?
Thanks.

0
 
LVL 6

Author Comment

by:devtha
ID: 1961843
Hi Lina,
        I tried inserting activex control and then selecting "common dialog" pops up a message that tells me I do not have a license. To obtain....
Where do I get a license?
Thanks.

0
 
LVL 1

Expert Comment

by:lina10
ID: 1961844
Hi!
here are the steps i take to add the ocx:in form's design view, i click the 'activeX' in the toolbox - and a list of controls opens.
i then choose the common dialog. now the list closes and the mouse pointer is in the shape other than usual. i click and drag the mouse on the form-and the ocx appears on the form.
if this doesn't work for you then i'm sorry i never had that lisence problem.
Lina
0
 
LVL 6

Author Comment

by:devtha
ID: 1961845
Very good.. I got it to work on another machine.
Just curios if I mention showOpen or Showcolor instead of showsave it will open the same dialog as Open and Color pallet respectively .Am I correct?
Thanks So Much
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question