Solved

API CALL comdlg32.dll

Posted on 1998-09-10
16
715 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now