API CALL comdlg32.dll
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
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
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
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(of
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
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
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(of
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
ASKER
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
Declare Function MSAU_OfficeGetFileName Lib "msaccess.exe" Alias "#56" (gfni As WLIB_OFFICEGETFILENAMEINFO
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.
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.
ASKER
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
However I would be glad to get your input on SHBrowseForFolder API-function. Please provide input..
Thanks
Devtha
ASKER
Sorry I waited 5 days. If no one comes up with the answer I may think to give you some points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lina,
The functionality is desired in Access 8.
Perhaps you can share more thoughts on OCX and direct me in that
direction.
Thanks
The functionality is desired in Access 8.
Perhaps you can share more thoughts on OCX and direct me in that
direction.
Thanks
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.OutputToacOutputQuer
End If
it gives the user the option to give the file any name(and you can set the default filenamename)
Lina
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.OutputToacOutputQuer
End If
it gives the user the option to give the file any name(and you can set the default filenamename)
Lina
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
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
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
ASKER
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
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
ASKER
Thanks
Devtha