Print Page | Close Window

ObjectBridge and Excel

Printed From: Rocket Software
Category: AccuTerm Knowledge Base (read only)
Forum Name: Advanced Features
Forum Description: Private escape sequences, host integration using the AccuTerm Server and ObjectBridge
URL: https://forum.asent.com/forum_posts.asp?TID=553
Printed Date: March 28 2024 at 6:13am
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: ObjectBridge and Excel
Posted By: jcsage
Subject: ObjectBridge and Excel
Date Posted: September 09 2005 at 5:30am
Hello,
I've written an application in Universe to populate an Excel spreadsheet using ObjectBridge commands.
I used the example code on this site, and everything works fine.

However :) I'd like to do a lot more, and I'm lost.
I have NO (none!!!) VB experience, which is why I chose to use ObjectBridge.

I’d like to do some PageSetup & formatting stuff (fonts, landscape, etc) but have no idea where to start.

Does anyone have any more code examples that include more than just the basics?

Thanks, Janet





Replies:
Posted By: homerlh
Date Posted: September 12 2005 at 9:24am
Janet,

One of the wonderful things about Excel is that it will teach your its strange form of programming macros if you let it.

To learn, what you do is start a macro recording. Then do things as you would like them to happen. You can set column widths, change fonts, etc. and when you finish, you tell Excel to stop recording the macro, then you use ALT-F11 to activate the VBA editor and examine the macro.

Granted, it's not a good way to learn programming, but it's a great way to learn how to do specific things in Excel VBA. I use it a lot.

To get there, click on the Tools menu, then on the Macro choice then on Record New Macro. Then do whatever you want and finish up by clicking the little "STOP" button that popped up when you started recording.

Hope this helps.

Larry Hazel


Posted By: jcsage
Date Posted: September 12 2005 at 11:04am
Larry,
Thanks for the tips, they will be very helpful.

After I finally figure out what objects, etc I need (and I'm getting there!), I can't figure out how to format that info into the CALL ATGETPROPERTY, ATSETPROPERTY and ATINVOKEMETHOD calls.

For that matter, I don't even know which of the 3 to use!
See? I really am lost! I've tried things similar to: CALL ATSETPROPERTY(Worksheet, 'PageSetup.Orientation', 'xlLandscape', ERRMSGS, OPTS) and several variations, but so far no luck.

Janet


Posted By: homerlh
Date Posted: September 12 2005 at 11:21am
Janet,

I should have specified that I thought you could create some macros in Excel and then used objectbridge to cause those macros to execute.

I have not used objectbridge, so I don't know if what I'm thinking will work or not.

Aren't there samples of this someplace?

Larry Hazel


Posted By: jcsage
Date Posted: September 12 2005 at 11:24am
No examples in the Accuterm documentation. There is one on the site I downloaded, and it helped me with the basics of creating a new spreadsheet and opening Excel, but that's as far as it goes. :(


Posted By: jcsage
Date Posted: September 12 2005 at 11:26am
Larry,
I just took a look at your website. Your murals are unbelieveable! How cool!
Janet


Posted By: GeoffG
Date Posted: September 12 2005 at 3:30pm
Janet,

Like you I am just coming to grips with ObjectBridge. I think that you are on the right track with ATSETPROPERTY for setting page orientation etc. In the example in your previous post, xlLandscape is a VB variable - to get what you want, replace it with 0 or 1 (I think - this worked for me in Word with 'WindowState')

Regards
Geoff


Posted By: jgold
Date Posted: September 13 2005 at 4:19am
Janet,

I hate to have to tell you this but if you want to develop interfaces with Excel, you're going to have to learn VBA. If you don't, you'll be doomed to working much harder with a much bigger frustration level. You don't have to become a VBA expert but if you take the time to learn a little, it will pay off big by making your job much simpler. It doesn't make any sense to try and use ObjectBridge to avoid a learning curve that you're going to have to climb anyway.

In my opinion, there are a number of things with ObjectBridge that keep me from using it;

1. There's no help for building objects and properties.
2. There's no step by step debugging.
3. The syntax is awkward and difficult to work with.
4. It's not very efficient.

As a consequence, I don't use ObjectBridge for anything. Even when I've had to put together samples of ObjectBridge programs, I've always written and debugged a VB routine FIRST and then translated it into ObjectBridge commands.

As Larry pointed out, when you program in VB, there's lots of help avaliable. You can use the macro recording facilities of the Office programs to create you're initial templates. You can debug those templates directly in the application. This gives you lots of nice things like online help, pop up property and method completion, step by step debugging, etc. To automate an Office application, you'll be using objects, properties, and methods. The built in Office VBA editor makes that job much simpler.

The method that I use whenever I need to develop a mvhost/Office interface goes something like this;

1. Work in the application to develop macros that do what I need to do.
2. Translate the macros into AccuTerm scripts.
3. Write a subroutine to download and execute the script from the mvhost side.

Translating the macro into AccuTerm is usually pretty simple. When you record macros, Office often uses defined constants instead of values in some of the function calls. I just go into the Office VBA editor and print the value of the constant in the immediate window. That tells me what the actual value is. Then I either change the constant name to the value or define the constant myself. The next thing is to translate all the specific object types into generic objects and change the way they're created. Instead of this;

Dim Worksheet as Excel.Worksheet
Set Worksheet = New Excel.Worksheet

You would need this;

Dim Worksheet as Object
Set Worksheet = CreateObject("Excel.Worksheet")

I'm not positive the syntax of the above is correct but it should give you the idea.

After translating the script, you can continue to debug it in the AccuTerm VBA editor. This lets you execute code step by step. It's not as powerful as the Office VBA environment but it's close and it works well for this step.

After it's all debugged and running in AccuTerm, you upload it to the host, build an item with the macro lines separated with the EM character and then use the AccuTerm ESC STX "P" command to download and execute the macro.

I've written programs for clients that create formatted Excel financials from their mvhost system with the click of a button. To do it, I followed the steps above.

In summary, the advantages of this method are;

1. Lots of help with the Office objects.
2. The ability to debug each step.
3. The results are more efficient.

With object bridge, you have to send messages across the network for each step. With the downloaded macro, you send the entire macro at once and then it runs on the client.

There are places where object bridge might make sense like if you just need to set a few properties on an existing object. I don't think it's the best thing to use to develop large scale integration projects.



Posted By: jcsage
Date Posted: September 13 2005 at 10:49am
Geoff,
Thanks for the clue. I'm still trying to get my brain to process objects & properties, and something you said turned on at least 1 lightbulb!
I hadn't done an ATGETPROPERTY for the PageSetup yet, so now that I've done that, I'm working on the ATSETPROPERTY for the orientation. Getting an error message still, but a different one!
I consider that progress :)


Posted By: jcsage
Date Posted: September 13 2005 at 11:02am
To jgold, moderator:

I can appreciate your suggestion that I learn VBA, but like many IT shops, we have 2 programmers supporting 150 users on a legacy system, plus imaging applications, plus web integration, etc... So around here, new skills are a bit of a luxury right now.

However, I'm not trying to develop any large scale integration project. I am simply creating a spreadsheet. Using ObjectBridge, it took me 30 minutes to do this. All I want now is to do a little formatting on it. Seems like a PERFECT application for ObjectBridge.


Posted By: jcsage
Date Posted: September 13 2005 at 11:11am
Geoff,
Oops, didn't give you much info on my last post, so here's what I have so far, in case it lights any of your bulbs!
ATGETPROPERTY(Worksheet,"PageSetUp",PageSet,ERRMSG,OPTS)
ATSETPROPERTY(PageSet,"Orientation","xlLandscape",ERRMSG,OPT S)

The error I'm getting on the ATSETPROPERTY is:
Unable to set the orientation property of the PageSetUp class.

I've tried using the "1" you suggested instead of xlLandscape to no avail. Yet. But I haven't given up!


Posted By: jcsage
Date Posted: September 13 2005 at 11:40am
To anyone interested: YIPPEE!!

ATGETPROPERTY(Worksheet,'PageSetUp',PageSet,ERRMSG,OPTS)
ATSETPROPERTY(PageSet,'Orientation,'2',ERRMSG,OPTS)

Thanks to all, especially Geoff, who kick-started my brain.

Janet




Posted By: GeoffG
Date Posted: September 13 2005 at 1:38pm
Janet,

Glad it worked out. I'm in the same boat as you - I had something simple to do but no time to learn the intricacies of VB. ObjectBridge is a superb base & the examples were just enough to get me going.

Geoff


Posted By: noranevers
Date Posted: April 29 2020 at 8:38pm
How do you end the script and close Excel?



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net