ObjectBridge/Excel question
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=611
Printed Date: March 26 2026 at 6:56pm
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com
Topic: ObjectBridge/Excel question
Posted By: LiveBlues
Subject: ObjectBridge/Excel question
Date Posted: December 14 2005 at 8:36am
I haven't played around much with these features, but now I have a project that I would like to use this for.
I need to populate multiple spreadsheets with data from our D3 system. I've looked to the UPDATE.EXCEL.SPREADSHEET program that Pete provided for us and I think I can get that to work for me. What I am not sure I can do is have it tell Excel to do a save as with a filename that the program will generate and then close the sheet. Given the fact that the program will be doing this multiple thousands of times, it won't be feasible to have to have someone manually save each spreadsheet. Is this possible somehow?
Thanks for any help.
|
Replies:
Posted By: PSchellenbach
Date Posted: December 15 2005 at 2:55am
Hi -
Try recording a macro in Excel and use SaveAs to save the sheet. Look at the recorded macro in the VBA editor (Tools->Macros->VBA). This should give you an idea of how to automate the SaveAs process. You will need to translate the VBA from the macro to ObjectBridge subroutine calls, and any constatnts that are used in the macro will need to be pure numbers when converting. You should be able to use the object browser in the VBA window to find the actual value of any constants.
Thanks,
Pete
|
Posted By: LiveBlues
Date Posted: December 15 2005 at 8:47am
Thanks Pete!
I had already did as you said. This is what it put in there.
Sub test()
'
' test Macro
' Macro recorded 12/14/2005 by Tracy H. Raines
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\traines.SMITH\My Documents\test1.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub |
So then I tried this in a copy of your program
CALL ATSETPROPERTY(ExcelApplication,'ActiveWorkbook.SaveAs','test .xls',ERRMSG,OPTS) |
That didn't work and that is as far as I have gotten on it so far. I haven't had a lot of time to look at it yet though.
|
Posted By: LiveBlues
Date Posted: December 15 2005 at 8:48am
|
Oops. There weren't any spaces in the filename that I tried.
|
Posted By: PSchellenbach
Date Posted: December 15 2005 at 10:23am
Hi LiveBlues -
First, you need to invoke a "method", not set a property, so you need to call ATINVOKEMETHOD instead of ATSETPROPERTY.
Second, the object that implements the SaveAs method is the Workbook, not the ExcelApplication, so you need to change the first argument in the subroutine call to the variable that has the workbook object reference.
Next, the method is simply "SaveAs", and the method arguments can be just the file name to save.
If you open the Object Browser from the VBA window in Excel, then select Excel from the top-left dropdown list and Workbook from the class list, you will find SaveAs in the Members list. Click on SaveAs and the pane under the two lists will show you the syntax for the method. Notice that all of the arguments are enclosed in [ ], meaning that they are optional. AccuTerm's ObjectBridge does not support "named argument" syntax like you see in the macro, so you need to include all required arguments, and as many optional ones as you need, in the order shown in the browser. In the case of SaveAs, you need to include the Filename argument, but can ignore the rest.
The call should look like:
CALL ATINVOKEMETHOD(WorkbookObject,'SaveAs','test.xls','',ERRMSG, OPTS) |
Thanks,
Pete
|
Posted By: LiveBlues
Date Posted: December 15 2005 at 12:41pm
Thanks again Pete. I feel a little bad that I didn't dig in and try and figure it out on my own, but time is at a premium right now.
Aside from that, is there a reason why I can't edit a post?
Hope to see you again at Spectrum in a few months!
|
|