Zumasys Homepage
Forum Home Forum Home > AccuTerm > Advanced Features
  New Posts New Posts RSS Feed - ObjectBridge and Excel
  FAQ FAQ  Forum Search   Register Register  Login Login

ObjectBridge and Excel

 Post Reply Post Reply
Author
Message
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post Topic: ObjectBridge and Excel
    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.

Id 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


Back to Top
homerlh View Drop Down
Beta Tester
Beta Tester


Joined: November 11 2004
Location: United States
Status: Offline
Points: 288
Post Options Post Options   Thanks (0) Thanks(0)   Quote homerlh Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
homerlh View Drop Down
Beta Tester
Beta Tester


Joined: November 11 2004
Location: United States
Status: Offline
Points: 288
Post Options Post Options   Thanks (0) Thanks(0)   Quote homerlh Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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. :(
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post Posted: September 12 2005 at 11:26am
Larry,
I just took a look at your website. Your murals are unbelieveable! How cool!
Janet
Back to Top
GeoffG View Drop Down
Groupie
Groupie


Joined: August 02 2005
Location: Australia
Status: Offline
Points: 43
Post Options Post Options   Thanks (0) Thanks(0)   Quote GeoffG Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
jgold View Drop Down
Moderator Group
Moderator Group

Moderator

Joined: December 29 2003
Location: United States
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote jgold Quote  Post ReplyReply Direct Link To This Post 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.

Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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 :)
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
jcsage View Drop Down
Newbie
Newbie


Joined: June 22 2004
Location: United States
Status: Offline
Points: 16
Post Options Post Options   Thanks (0) Thanks(0)   Quote jcsage Quote  Post ReplyReply Direct Link To This Post 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


Back to Top
GeoffG View Drop Down
Groupie
Groupie


Joined: August 02 2005
Location: Australia
Status: Offline
Points: 43
Post Options Post Options   Thanks (0) Thanks(0)   Quote GeoffG Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
noranevers View Drop Down
Newbie
Newbie


Joined: October 13 2005
Location: United States
Status: Offline
Points: 5
Post Options Post Options   Thanks (0) Thanks(0)   Quote noranevers Quote  Post ReplyReply Direct Link To This Post Posted: April 29 2020 at 8:38pm
How do you end the script and close Excel?
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.10
Copyright ©2001-2017 Web Wiz Ltd.