Import data from an other sources like XML

Epicor ERP 10
1

Hi,
My user create submission in an excel file. The excel file are done to calculate the corect submission price.
I want to import some of the excel file to Epicor in a submission. I want the process create the submission, the line and the price break.

I was thinking a kind of queue running somewere and grab a XML file I generate with the excel file and import it on Epicor.

Is someone can guide me in there?

Thanks

2

there’s a million ways to skin this cat, but I think you’d be quite able to do this with the tool of your choice depending on what you’re familiar with.

I’d probably utilize a UD table for it and build a BPM on that table.

3

By “Submission”, are you talking a Quote?
Depending on the requirement, you can use DMT, Service Connect, REST, VBA scripting, UD table and run some login in Epicor, etc. I would not bother with an intermediary XML file though.

1 Like
4

Yes a Quote

5

What I know about BPM it’s triggered if we call a busibess object or a data access. Since the trigger in my case is an action in the Excel file how can I don that?

6

The import action will be done in the excel file. Kind of a export button.

  • DTM I thing need an action from the user, it’s more for mass importation.

  • Service Connect, we don’t have it, I think it’s expensive?

  • I don’t know REST can you say more?

  • VBA scripting, you mean connect directly to the epicor DB? It’s good Idea, do you know where I can find example of code.

  • UD Table I don’t know what you mean there?

7

Since you are starting from scratch, it may be in your best interest to grab a consultant to help you out. Also, NEVER connect directly to the DB and make changes (you mentioned it in the VBA scripting). Use the APIs available instead.

8

Fine can you help me on this as consultant?

9

You could achieve all this rather simply through VBA Script, DMT and something like Windows Task Scheduler/SQL Server Job.

Add a button on your Excel form with a VBA Script to export the file into CSV format to some network share monitored by Task Scheduler/SQL Job.
DMT can be automated to run the file into Epicor.
Error Logs can be picked up by the Task Scheduler/SQL and emailed back to the end-user (or Admin person).

You don’t need to use Service Connect, REST, etc. for something like this.

What I just described, you could search the internet as there are plenty of examples. As for the DMT part, there are many examples of how to run DMT through a command shell/powershell without any user intervention:

DMT Command Line Interface

2 Likes
10

Good point @tomdomurat!

11

I like the way to use VBA and use BO to interact directly with Epicor. But the fact are that VBA don’t allow reference to Epicor BO dll.

12

The link given by Tom is dead. I hope this link to the DMT github will last longer.

You can call these PowerShell commands from VBA with something like this:

'@Description("Run PowerShell commands from VBA.")
Public Sub PowerShellRun(ByVal command As String)

    On Error GoTo ErrorHandling
    
    Dim PSOptions As String
    PSOptions = " -NoLogo -NoProfile -WindowStyle hidden -NonInteractive"
    
    Dim PSCommand As String
    PSCommand = "powershell.exe" & PSOptions & " -Command """ & command & """"
    
    Debug.Print PSCommand
    
    Dim shellReturn As Long
    With CreateObject("Wscript.Shell")
        shellReturn = .Run(PSCommand, 1, True) ' strCommand, intWindowStyle, bWaitOnReturn
    End With
    
ErrorHandling:
    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical, "ERROR on DMT Upload!"
        Err.Clear
    ElseIf shellReturn <> 0 Then
        MsgBox "PowerShell returned error #: " & shellReturn, vbCritical, "ERROR on DMT Upload!"
    End If
End Sub