ActiveX Basix

[Boozer]

Hello,

Just need some references for newbies to ActiveX. I am fairly good with Excel VB and need to know how to access that power from outside programs.

I have never used ActiveX before to create an excel sheet. I am now trying to use ActiveX to create an Excel spreadsheet with SecureCRT and VBscript. The example they gave to set this up was:

Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Add
Set ws = wb.Worksheets(1)

But this confused me because of the .Add part. Everytime I use ws to do something, shouldn't that, in theory, add a workbook. I know from using it that it doesn't. I was wondering if the code I can use is limited by the program I am using, by Excel, or by ActiveX. Is there any simple way to reference things in excel just like I was writing code from excel VB editor? Any hints/tips/suggestions for a newbie would be greatly appreciated.

Thanks!

 

[Juan Pablo González]

What you're using is called "Late binding", because the "link" between your program and Excel is created at run time. If you set a reference to Excel (Using the References... command somewhere around there), you should be able to write code just like you do in VBA, for example

<pre>Sub Test()
Dim XlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet

Set XlApp = New Excel.Application
Set WB = XlApp.Workbooks.Add
Set WS = WB.Sheets(1)

MsgBox WS.Name
WB.Close False
XlApp.Quit
Set XlApp = Nothing
End Sub</pre>Note: Didn't test this, I just wrote this in here, but should work ok...

 

[Juan Pablo González]

On 2002-08-22 14:56, Boozer wrote:
...But this confused me because of the .Add part. Everytime I use ws to do something, shouldn't that, in theory, add a workbook...

Forgot this question. The answer as you found out is no, because you only create the workbook once, when you do the Set wb = , from then on, VB "knows" that the ws variable refers to (And only to) the first worksheet (or sheet) of the wb workbook. That's why it doesn't add a new workbook everytime.

 

[Boozer]

Thanks a lot for your help!

 

[Boozer]

Thanks again for everyones help. But alas, I have another question.

I have no problem getting data to the worksheet, but is it possible to open a workbook and then call a VBScript or macro that is in that workbook?

Just for example:

Lets say I have an extravagent script already written in Excel to manipulate data. Now the data source changed and I have to learn activex interface commands to get the data to Excel. Do I also have to re-code the data manipulation in the external program, or can I send the data to excel and then somehow call the script already in excel to manipulate the data for me.

I know I can just have the script run when the Excel spreadsheet is opened, but I would like to have all the data manipulation completed automatically when the data is entered with the script already in Excel.

Thanks!

 

[Juan Pablo González]

The code to this, is to use

Application.Run "WorkbookName.xls!MacroName"