Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

API CALL comdlg32.dll

Posted on 1998-09-10
16
Medium Priority
?
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 400 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

Industry Leaders: 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!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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