Solved

API CALL comdlg32.dll

Posted on 1998-09-10
16
717 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA script to check for duplicate primary keys on lost focus 18 30
Filter a form 8 15
Trying to force an answer in a combobox 7 18
Switch 5 17
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 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