Automating Excel Add-In JE Import with VB

Hi Mark,
The short answer is that you can't control the Excel Addins via VBA. The only thing you can do is invoke/start the wizard.

A couple of anecdotes for you:

- The Addins were originally created as a marketing tool to slicken up the Lawson demo. After a few prospects turned into customers, they complained to Lawson, "hey, where's that Excel tool we saw in the demo???" So, the irony is that apparently Lawson really didn't plan this as a product...

- After the Addins were released as a product, I talked to the developer, and asked him your exact question, and asked him to imagine the value they could deliver if they could VBA-enable the addins. Essentially, you could write your interfaces/conversion in VB, and use the addins to pump the data in/out of Lawson. His response:"Gee, we didn't even think of that...that's a great idea!"

So where is this developer now? Still with Lawson? We have cash, does he work on the side?

We had a need to load journals via add-ins. One of our consultants provided a template which used VBA to mimic the upload function. It wasn't working perfectly, so I tweaked it a little bit and we are able to load both the 40.2 and the 40.1 records when doing jes. It's limited to 1000 records, but that's usually more than enough for our purposes.

Mark,

You can create very sophisiticated upload macros to Lawson using VBA/Excel and Lawson's Addin API.

I recently developed a GL40 upload macro that even allows the user to create a journal number if one is not provided.
I have also created upload spreadsheets for Inventory Control, Purchase Order, Billing. Etc.

If you need more info contact me offline aleksei_wolff@yahoo.com

Thanks.

Alex, are you saying you were able to control the Addins via VBA??? The only control/object method I see in VBA is to start the wizard...

You are not using the add-ins, VB mimics the add-ins. If you attended the add-ins class at CUE, the instructor gave everybody this link. Look at the self contained worksheets, they contain the VB programming. If you know a little VB it is very easy to modify the samples or create new ones.


http://solutions.lawson.com/lu/addins/main.htm

Regards
JC

Oh, I thought you were talking about something new and improved. Those have been around for a long time...but have been updated from writing to database tables (e.g., GLTRANSREL) to calling AGS.

The original question in this thread was how to automate the JE process using the Addins, the answer to which is "you can't".
But, as John and Alex point out, you can use Excel VBA to upload data via AGS ...

Whenever I attempt using the self-contained JE upload file, I get an error saying "" in the GL40.2 message. Did anyone else have this problem? If so, how did you fix it?

I haven't been able to control the add-ins via VB, but just like the Query Wizard, the Upload Wizard generates a web query, an AGS call string for an Upload.  You just click on the magnifying glass after you run the Upload and it gives you the query.

You'll be able to figure out what the URL parameters are for, then you can use some VB or whatever to loop through a spreadsheet and call that AGS URL with your parameters for each line.  It essentially is doing what the Upload Wizard is doing.

I just think of the Office Add-ins as URL generators that save me a lot of time, then I use that URL in other programming environments to make it part of a more automated solution.

Jeremy Zerr
Lawson Reporting

Jeremy~
I'd like to complete a query using the URL generated by the Query Wizard in a vba environment. Have you done something like this? When I attempt to connect though, it gives me an authetication error. Here's the code I'm working on:

public sub GetLawData(sUrl as String)
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    Call objHttp.Open("GET", sUrl, False)
    Call objHttp.Send("")
    Call MsgBox(objHttp.ResponseText)
   
    Set objHttp = Nothing
End Sub