ObjectBridge/Excel question

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.

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.

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