web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Excel Object Model Pro...
Microsoft Dynamics SL (Archived)

Excel Object Model Programming

(0) ShareShare
ReportReport
Posted on by Mark E 6,451

SL2011 FP1; I have an Excel import file that imports data into the Voucher screen.  The APSetup screen has the "Warn on Duplicate' options set to "Both", so if a voucher amount is a potential duplicate, the screen pops up.  Trying to program some code that will either suppress this popup box, or automate the pressing of the OK button.

Object model user guide has some references to do this, but it seems more related to VB6 or VB2005 than Excel VBA.

Any help would be appreciated.  Thanks.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ram Peru Profile Picture
    Ram Peru 2,830 on at

    Hello Mark,

    You can use the below two function to overcome the sub form display and warning messages using VBA code in Solomon Object model.

    For Sub form display:

    Private Sub sivApp_SubFormDisplay(ByVal FormName As String)

        If Trim$(FormName) = "Name of sub form" Then

            SIVApp.Controls ("name of the button").value = True

        End If

    End Sub

    For warning message:

    Private Sub SIVApp_Message(ByVal MessageNumber As Long, ByVal MessageText As String, ByVal MessageType As SWIMAPI.sivMessageType, MessageResponse As SWIMAPI.sivMessageResponse)

        If MessageNumber = “warning message #” Then

            MessageResponse = sivMsgRspOk

        Else

            MessageResponse = sivMsgRspOk

        End If

    End Sub

    Hope this will help,

    Thanks,

    Perumalsamy R

  • Mark E Profile Picture
    Mark E 6,451 on at

    Perumal,

    Thanks for your suggestions.  I have similar functions in the Excel VBA Project, but the problem seems to be that the import routine is not firing these functions (as far as I can tell).  I put a MsgBox in the Message function to see if it is being called (Call MsgBox(MessageNumber, vbOKOnly, "Message"), and the MsgBox does not pop up.

    As I understand it, I need to add a "Class" module with the "Private WithEvents SIVApp As SIVApplication" code for using Message and SubForm events (which I have done).

    Where in the code does the program know to execute these sub functions?

    Thanks,

    Mark

  • Ram Peru Profile Picture
    Ram Peru 2,830 on at

    Hello Mark,

    Did you include this code in Excel VBA (macro) project or SL Standard screen Customization?

    You can  try to declare the Sivapplication as like below.

    Private or public WithEvents SIVApp as SIVApplication

    Thanks,

    Perumalsamy R

  • Mark E Profile Picture
    Mark E 6,451 on at

    Your suggested code is within the Excel VBA program, included in Module1.

    I have already declared “Public WithEvents SIVApp As SIVApplication” within the Class1 module of the Excel VBA program.

    Thanks.

  • Ram Peru Profile Picture
    Ram Peru 2,830 on at

    Can you include the suggested code in screen code itself?

    Thanks,

    Perumalsamy R

  • Mark E Profile Picture
    Mark E 6,451 on at

    Was thinking about trying that, but I would not want that functionality to occur when a user manually enters the vouchers, only when the Excel import program runs.  How can I tell to not perform that function when a user is entering manually.  Can't do it with Userid, since the UserId is captured as part of the import process....

  • Ram Peru Profile Picture
    Ram Peru 2,830 on at

    Is the Excel import program running from custom screen? If yes, you have to include the suggested code in custom screen.

    If you did the Excel import program with in the Voucher & Adjustment screen, you have to initiate the Excel import program by clicking some command button in Voucher screen.

    If you are initiating the Excel import program by clicking command button, you have to set the flag value while clicking the command button. By doing this, you can control the manual entry & Excel import program.

    Does this make sense?

    Thanks,

    Perumalsamy R

  • Mark E Profile Picture
    Mark E 6,451 on at

    The Excel Import program is written entirely within an Excel workbook, using Object Model integration.  It is actually based on one of the "Import Assistant" tools provided within PartnerSource (mbs.microsoft.com/.../MSDSL2011ImportAssistants).  The AP Voucher Import tool works fairly well out of the box, but does not seem to work when the "Warn on Duplicates" option in APSetup is enabled.

    I was able to auto-respond "no" to the System Message 223 that asks if you want to review potential duplicates, but the import process still errors out.  If the duplicate checking option is set to "None", the import works great.

  • Mark E Profile Picture
    Mark E 6,451 on at

    So, no matter what I tried, the Excel import tool would fail if the APSetup option was set to anything other than "None" for duplicate checking.  Even though I was able to avoid the Potential Duplicates popup box, the import of Vouchers would fail.

    My only recourse was to directly update the APSetup table via VBA code to set the duplicate checking to "None" (0) at the start of the routine, then set it back to "Both" (3) when the process was completed or abandoned.

    Not the preferred approach, but it works.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans