Zugg Software :: View topic

Beginner Joined: 29 Aug 2003 Posts: 25 Location: USA

I managed to access Microsoft Outlook using something similar to the example given on the Support pages.

Now what I really want to do is have put data into an excel spread sheet and/or invoke an excel vb script to update the various related summary information.

From what I can tell the class hierarchy in excel is:

excell application
workbooks (collection of object workbook)
worksheets (collection of object worksheet)
range

etc.

The test script I am trying to run is as follows:

#VAR xl %comcreate( "Excel.Application")
#say "get workbook"
#var xlWb @xl.Workbooks("Andy1.xls")
#say "get workbook 1"
#var xlWs @xlwb.Worksheets("Exp")
#say "get workbook 2"

I get to message "get workbook" and then get the message:

Error parsing command:
OLE error 800A03EC

Clearly I'm missing something. Has anyone else managed a COM connection to excel?

Apprentice Joined: 16 Dec 2002 Posts: 103

I can help you. You have assumed, in your code, that the workbook object has Open as its default method. I don't think this is true... Try replacing #var xlWb @xl.Workbooks("Andy1.xls") with #var xlWb @xl.Workbooks.Open("c:foobarAndy1.xls"), making sure to replace foobar with a valid path.

#VAR XL %comcreate( "Excel.Application")
#VAR wb @XL.Workbooks.Open("c:Andy1.xls")
#VAR ws @wb.Worksheets("Exp")
#SAY @ws.Cells(1, 1).Value

Works for me. Returns the contents of the first cell. Anything more complicated than that, and I'd either make a macro in the same file and just execute it or use it as a guide for writing my zMud code.

Beginner Joined: 29 Aug 2003 Posts: 25 Location: USA

You are exactly right. I needed the Open method. Now the test case works fine.

The problem I now face is likely a limitation of excel. Basically it seems that that excel doesn't permit updating a spread sheet from two sources (open for update directly and updates from zMUD).

I have MS Access so I will see if I can accomplish what I am after with an access database.

Thanks for the insights.

Beginner Joined: 12 Mar 2003 Posts: 14

Please keep us posted on how the COM connection to access goes for you, I'm really interested in this and I may not be alone in that.

Beginner Joined: 29 Aug 2003 Posts: 25 Location: USA

I'll give you what I have so far.

Excel to zMUD is working quite well. Here is a sample visual basic / excel module:

Sub zMudTest()
Dim zMUD As Object
Dim n As Integer, i As Integer
Dim ses As Object
Dim zVar As Object
Dim skil As String, sk1 As String

Set zMUD = CreateObject("Zmud.Application")
Set ses = zMUD.CurrentSession

Set zVar = ses.getvar("skilLst", "Com")
skil = zVar.Value
sk1 = ses.ExpandStr("%pop(skilLst)")

Set zMUD = Nothing
End Sub

What I plan to do is have the excel code issue a game command which will display skills data. A trigger will capture the results and put them in a two level list called "skilLst". After a fixed delay the excel code will use something like the above snippet to retrieve the results.

This is not my prefered way of doing things since the approach is vulnerable to game or network delays that could cause the return of the results from the game to exceed the delay my program has built in.

Ideally the trigger that picks up the information coming back from the game would then pass that information back to excel and then kick off an excel script to post that information as required. The problem I am having with accessing excel from zMUD is that reads of information do not include changes since the last save of the excel document. Write fails with a message about the spread sheet being open for update.

If anyone has ideas how to circumvent this problem I would greatly appreciate hearing about it.

Going from zMUD to MS Access is working well. I haven't fully explored concurency issues but the following script works:

#VAR ac %comcreate( "Access.Application")
#var acWs @ac.DBEngine.CreateWorkspace("Andy", "admin", "", dbUseJet)
#var acDb @acWs.OpenDatabase("C:zMUDAndy.mdb")
#com acDb Execute "Insert into skill(SkillNm, Skill, SkillState) values('Astrology', 72.64, 'perplexed');"
#com acDB Close
#com acWs Close
#var acDb ""
#var acWs ""
#var ac ""

In verifying the above script I learned that I can update (insert into) a table that is open for viewing but the change doesn't not become immediately apparent. Once I closed and opened the form I could then see the change.

When it comes to visual basic I consider myself an interested amature. I'm also pretty green with zMUD coding (but enjoying the challenge). A discussion of using COM to interface to Microsoft products, particularly Excel, Access and Word would be very helpful.