Sunday, September 7, 2008

Using pass-through sanScript in VBA to return a file path to a Dynamics GP Modified form field

If you read my previous article on "Using the Win32 Common Dialog Box API to return a file path to a Dynamics GP Modified form field", you are now familiar with the technique and the beaty of it. Now Let's take a look at how you would replace Common Dialog Box with pass-through sanScript by creating a reference to the Dynamics Continuum Integration Library object file.

NOTE: this method of calling pass-through sanScript is not supported by Microsoft.

Replace the previous script in step 6 with the following:


Private Sub PBFile_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim CompilerApp As Object
Dim CompilerMsg As String
Dim CompilerErr As Integer
Dim CompilerCmd As String

Dim winMessage As String
Dim lpstrTitle As String
Dim sFilter As String
Dim l_path As String

Set CompilerApp = CreateObject("Dynamics.Application")

' add quote character as part of the filter -- Chr(34)
lpstrTitle = Chr(34) & "Select File" & Chr(34)
sFilter = Chr(34) & "All Files(*.*)*.*" & Chr(34)

CompilerCmd = ""
CompilerCmd = CompilerCmd & "local string filePath;" & vbCrLf
CompilerCmd = CompilerCmd & "local boolean l_result;" & vbCrLf
CompilerCmd = CompilerCmd & "l_result = getfile(" & lpstrTitle & ", 1, filePath, " & sFilter & ");" & vbCrLf
CompilerCmd = CompilerCmd & "if l_result then" & vbCrLf
CompilerCmd = CompilerCmd & " '(L) sFilePath' of window POP_PO_Entry of form POP_PO_Entry = Path_MakeNative(filePath);" & vbCrLf
CompilerCmd = CompilerCmd & "end if;"

CompilerApp.CurrentProductID = 0 ' DYNAMICS
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
CompilerError = CompilerApp.ExecuteSanscript(CompilerCmd, CompilerMsg)

If CompilerError <> 0 Then
MsgBox (CompilerMsg)
End If
End Sub


The Dexterity getfile() function creates a dialog box that allows the user to select a file. It returns a boolean value indicating whether the user clicked OK or Cancel in the dialog box. If the user clicks OK or Open, the file name and path will be returned to the variable named in the path parameter, in this case the filePath variable.

The files displayed in the dialog can be filtered so that only certain types of files are displayed. You can use a predefined filter, or create your own filter. See the Dexterity help file for more information on getfile.

The real trick is to get the code to execute in the context of the modified form and not the original form. To achieve this, we add the extension "!Modified" to our form as indicated in this code snippet:


CompilerApp.CurrentProductID = 0 ' DYNAMICS
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"


VBA is a very powerful tool, but more so when used with Dexterity. Please send your comments in and let me know what you are working on and what you would like to see posted on my site.

Download Package File Here. You will need to grant security to the modified form first.

Until next post!

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

6 comments:

David Musgrave [MSFT] said...

For more information on using Dexterity sanScript from VBA see the conference Materials from the 2008 Technical Airlift.

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/07/11/microsoft-dynamics-gp-technical-airlift-2008.aspx

Also have a look at the Hybrid section of the Modifier & VBA Samples page:

http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx


David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

Mariano Gomez said...

On behalf of Mark Polino:

Mariano Gomez follows up his previous posts with a look at using pass-through sanScript in VBA to return a file path to a Dynamics GP modified field form.

Mariano continues some nice dev posts with lots of screen shots and code.

Posted by Mark 9/07/2008 06:30:00 PM at http://msdynamicsgp.blogspot.com/2008/09/using-sanscript-in-vba-to-return-file.html

Anonymous said...

how can i use the same code to restrict the custom report priniting by the value entered in a gp window field or a vb field placed on top of the gp window.

Mariano Gomez said...

Take a look at the article "VBA - Calling a Report using Visual Basic for Applications" over at Developing for Dynamics GP. This should get you going in the right direction.

Anonymous said...

Hi Mariano, thanks for your post.
I was under the false impression one cannot access a field added using Modifier through pass-through sanscript.But this piece of Code achieves it.
CompilerApp.CurrentProduct & "!Modified" . I wonder why this is not documented in the Continuum API guide ?

Mariano Gomez said...

That's why you have bloggers like me and many others who are willing to share their knowledge beyond the manuals. Thanks for following the blog.

MG.-