Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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,405

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

  • Mark E Profile Picture
    Mark E 6,405 on at
    RE: Excel Object Model Programming

    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.

  • Mark E Profile Picture
    Mark E 6,405 on at
    RE: Excel Object Model Programming

    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.

  • Ram Peru Profile Picture
    Ram Peru 2,830 on at
    RE: Excel Object Model Programming

    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,405 on at
    RE: Excel Object Model Programming

    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
    RE: Excel Object Model Programming

    Can you include the suggested code in screen code itself?

    Thanks,

    Perumalsamy R

  • Mark E Profile Picture
    Mark E 6,405 on at
    RE: Excel Object Model Programming

    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
    RE: Excel Object Model Programming

    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,405 on at
    RE: Excel Object Model Programming

    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

  • Suggested answer
    Ram Peru Profile Picture
    Ram Peru 2,830 on at
    RE: Excel Object Model Programming

    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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans