Solved

API CALL comdlg32.dll

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

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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