asked on

VBScript and VBA (Excel) problems

I am driving an Excel Spreadsheet from VBScript, so I start off with a statement such as:

Set Spreadsheet = getobject("Q:\TIMS3\Setup\TIMS3 Build Control.xls")

I am then able to access plenty of the VBA methods and properties with no problems, but I have hit two snags:

1. I want to use a MsgBox function, but I want it to be the Excel VBA MsgBox rather than the VBScript one. How do I do this?

2. I've also got some code going on in the spreadsheet itself, and it would be nice if I could access in VBScript some of the variables used in the VBA code in the spreadsheet. Can this be done?

Any suggestions for reading matter in this area?

osp70

1. how about creating a method that pops up the vba msgbox with what you want, something like:

sub myMsgBox(s as string)

msgbox(s)

end sub

2. Unless you want to get into accessing memory directly I don't think you can, unless the same as 1. works for this, like a getVar subroutine.

osp70

stevbe

not sure but I think if you declare your VBA variables as public they will be available after you create your object

Spreadsheet.VarName

Steve

srimanth

KenTurner,

1. You can directly use MsgBox in Excel Macro VBA and it would work. Also, check out the DialogBox Property in the Range Object.

2. You can access values in the Excel Macro VBA by exposing them using the Set/Let properties. By using the following code, u can use MyVariable even in Excel VBA as well as outside Excel in your scripts. So, u'd access the variable as objExcel.MyVariable.

Private mvarMyVariable As String

Public Property Let MyVariable(ByVal vData As String)
mvarMyVariable = vData
End Property

Public Property Get MyVariable() As String
MyVariable = mvarMyVariable
End Property

KENNETH TURNER

ASKER

Thanks for the suggestions, chaps, I will play with them and get back to you ...

ASKER CERTIFIED SOLUTION

srimanth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KENNETH TURNER

ASKER

Yes srimath, your suggestions for Property Let and Property Get are magic - thanks.

Haven't tried the MsgBox suggestion yet. Meanwhile have a few points for your troubles ...