Using the Win32 Common Dialog Box API to return a file path to a Dynamics GP Modified form field

I came across this question late last night on the Microsoft Business Solutions Newgroup and I figured I could not let this one pass me by without giving it a shot. There are a few elements to this request though:

1) An existing GP form that needs to be modified to add a string resource to hold the path and file name, and a button that will launch the File Open dialog.

2) A VBA project that will incorporate all the elements above (modified form, file path string and push button) and will execute the code to retrieve the file.

3) A script that will store the file path for the user.

Well we know we are covered with points 1 and 3. We can achieve number 1 with plain and simple Modifier and we can achieve number 3 by either using the Dynamic User Object Store (DUOS) or instantiating the new UserInfo VBA object to store data in a SQL Server table, but how do we launch a File Open dialog? There are two known methods for this: Pass-through Dexterity and the Windows Common Dialogs Box library. This article will focus on the latter as the former will be a part of a follow up blog article.

The Windows Common Dialog Box library -- the DLL, not the control (OCX) -- provides other applications with the standard Windows dialog boxes for opening and saving files, and the ability to choosing colors and fonts, etc. All these methods reside in the Comdlg32.dll library file. It is grouped under the User Interface category of the API.

The following example will look at a trivial implementation of a path to a purchasing contract with a vendor. The idea here is that we will retrieve the path to the file and store along with our purchase order -- you may be thinking, why not use the OLE Container, hence a trivial in its use not in its implementation.

1.- We will start by opening Modifier and adding a String control for the path and a Push Button control to the POP_PO_Entry form.

NOTE: I changed the default name of the string control to sFilePath ( '(L) sFilePath' ), added a prompt, and linked my sFilePath string control to the promt created -- when adding the field to Visual Basic, it will inherit the caption of the prompt it's linked to --. Also changed the default field name for the push button control to PB_File, linked the button to the sFilePath field with the Link Lookup Tool menu option, and finally assigned various Object properties including the graphical button face of the button, as shown (click on figure to zoom in).



2.- Save the changes and return to Dynamics GP. You will need to grant security to the modified POP_PO_Entry window under Microsoft Dynamics GP > Tools > Setup > System > Alternate Modified Forms/Reports.



3.- Open the Purchase Order Entry screen to verify all changes have been successfully achieved with Modifier. Now you can proceed to add the form to Visual Basic, by pressing CTRL+F11 on your keyboard.



4.- You can now add the newly created controls to the Visual Basic project by pressing Shift+F11 on your computer's keyboard. Make sure to add the sFilePath string control and the PB_File push button control.

5.- Proceed to open the Visual Basic Editor. You can press ALT+F11 on your keyboard to quickly access the Editor or use the Tools menu on the Purchase Order Entry screen.



6.- Copy and paste the following code into the editor's window:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

Private Sub PBFile_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String

OpenFile.lStructSize = Len(OpenFile)
sFilter = "All Files (*.*) " & Chr(0) & "*.*" & Chr(0)
OpenFile.lpstrFilter = sFilter

OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)

OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Select File"
OpenFile.flags = 0

l_return = GetOpenFileName(OpenFile)

If l_return = 0 Then
  MsgBox "Operation cancelled"
Else
  PathtoContract.Value = Trim(OpenFile.lpstrFile)
End If

End Sub

NOTE 1: The above code is based on Microsoft's KB article 161286, but adopted to work with VBA. The hwndOwner and hInstance properties of the OPENFILENAME type definition inherit their values automatically from context of the Dynamics GP application and the calling form respectively, reason why they were not initialized in the BeforeUserChanged method.

NOTE 2: The sFilter variable can be initialized with any type of file extension, for example, if searching for text files, sFilter = "Text Files (*.txt) " & Chr(0) & "*.TXT" & Chr(0)

7.- Compile the project and return to Microsoft Dynamics GP. Close and reopen the Purchase Order Entry window. and press the file button. You will now get the File Open Common Dialog Box and can now choose a file. Once selected, the file and it's path will be returned to the string control added to store it's value after selected.





If you eventually want to store the path, you could make use of the Dynamic User Object Store (DUOS). Refer to the samples in the VBA Users Guide manual provided with your Dynamics GP application (Help > Printed Manuals). You can also follow this URL to download a working sample on how to store data in the DUOS.

My next post will examine the use of Pass-Through Dexterity to accomplish the same thing.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/