How do VB for Word, Excel and/or VBScript work together? [Resolved]

[daiei27]

I need help understanding how object models interact with each other. If I open Excel using VBScript or VB in Word, how do I know what objects and methods are available to me?

Should I be able to use any and all of the Excel methods in VBScript/Word once I create an Excel object (under appropriate Excel objects, of course)?

P.S. Posted elsewhere but realized this is the place to ask.

[RobDog888]

Probably the best solution is to check the help file for each office app in question. They usually have all
methods/functions/properties available when opened either with VB or VB Script. One of the main issues
is to use the VB constants value instead of the name of the constant. For ex. Const xlCellTypeLastCell = 11
so you would use 11 in your vbscript when referencing the method that takes it as a parameter.

[daiei27]

If all the objects/methods should be available when I create an Excel object, why doesn't VBScript or VB for Word seem to recognize the TextToColumns method from Excel?

I wish there was an exists() function or something to check if a object/method is available.

[ethyachk]

One thing you can do is poke around in the Object Browser that's available to VB for Word. It's under the view menu in the VB editor, but you can also access it by hitting F2. That's one way to find a lot of the information you are looking for. However, if you don't have the Excel reference added, you're not going to find what you are looking for. Check your references and if you have the Excel one, check out the Object Browser to find what you need.

[RobDog888]

I think your talking about Intellisense? Like when you start typing an object and you press
the dot and a popup menu comes up with the available methods and properties? If so, that
wil only be available if you early bind and add a reference to Excel in your project. After your
done you can change it back to later binding and remove the reference(s).

[daiei27]

I guess Intellisense does sort of do that for you, but not if you're developing from another app.

My real problem is I already know how to use the TextToColumns method in the VB editor for Excel, but when I try to use it on an Excel application opened from VB for Word or VBScript , the method isn't recognized.

In other words, the following code will work in Excel.

wsheet.Range("A1").Value = "a, b, c"
wsheet.Range("A1").TextToColumns DataType:=xlDelimited, Comma:=True

However, running these statements in VBScript or VB for Word gives me errors like it doesn't recognize TextToColumns as a method. That's why I asked if creating an Excel object imports Excel's object model or if there was a way to test if the method was available.

I'd appreciate any help I can get. Thanks for the replies!

[RobDog888]

Oh your opening Excel from Word. Word does not have a reference for Excel. You need to add one.
Then it should be recognized.

[daiei27]

Actually, I was just reading another thread involving a constant reference and what you said earlier hit me. Maybe it's not the method but the constant in the call that is the problem. I just realized the 'xlDelimited' is a constant and probably can't be resolved like you said. I'm gonna go try to subsitute the value and let you know how it goes.

[RobDog888]

With VBScript you can not use the named constants. You need to use the actual numeric values.

[daiei27]

What can I say, you were right.

However, the fix works in Word, but I still can't get it to work in VBScript, which is where I need it! The following bold line gives me an 'Expected Statement' error.

Option Explicit
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
' Make Excel visible'
objExcel.Visible = True
 
'Open template'
Dim wb
Set wb = objExcel.Workbooks.Open("C:\dummy.xls")
                 
'Import data'
wb.Sheets(2).Range("A8").Value = "a, b, c"
[B]wb.Sheets(2).Range("A8").TextToColumns DataType:=1, Comma:=True[/B]
 
' Close to prompt save and then quit.'
wb.close
If objExcel.Workbooks.Count = 0 Then
    objExcel.Quit
End If

So unless something else is wrong, doesn't it look like VBScript does not have access to the TextToColumns method? I saw someone mention you can bind Excel earlier on the dim statement to make the application's methods available in other versions of VB, but I don't think you can do this in VBScript.

[RobDog888]

This worked for me on a blank sheet/book.

Private Sub Worksheet_Activate()
    Sheets(2).Range("A8").Value = "a, b, c"
    Sheets(2).Range("A8").TextToColumns DataType:=1, Comma:=1
End Sub

You should also dim your vars as Object instead of Variant

Option Explicit
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
' Make Excel visible'
objExcel.Visible = True
 
'Open template'
Dim wb As Object
Set wb = objExcel.Workbooks.Open("C:\dummy.xls")
                 
'Import data'
wb.Sheets(2).Range("A8").Value = "a, b, c"
wb.Sheets(2).Range("A8").TextToColumns DataType:=1, Comma:=1
 
' Close to prompt save and then quit.'
wb.close
If objExcel.Workbooks.Count = 0 Then
    objExcel.Quit
End If

[daiei27]

I can get it to work in Excel and Word just fine. It's VBScript run in Internet Explorer that I can't do it in. And from what I've seen so far the 'As [datatype]' declaration doesn't seem to be an option in VBScript. My experience is mainly in VB for word so I could be wrong.

[RobDog888]

Maybe its the parameter labels that are messing it up. Try it in the correct order with the correct parameter types.

expression.TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

Sheets(2).Range("A8").TextToColumns , 1, , , , , 1

[daiei27]

It worked! I seriously couldn't have done it without your help. I never would have guessed the crappy errors I was getting was related to the values and not the method. Thanks a million, Rob.

I noticed you answer just about everybody that posts here. Do you work for vbforums.com? I mean, you certainly went out of your way for me...

[RobDog888]

Its not a problem! Thanks.

No I dont work for them, but I do volunteer my time. I Moderate this (VBA) Forum and Co-Moderate the Application Deployment Forum.
When you have been involved with the Forums for a lengthy time and have a certain level of expertise in a particular area
you can ask to become a Moderator of one of the Forums that is your speciality (depending on availabiity).

[daiei27]

If you ever need help with XML-related stuff like XSLT2.0 or web stuff like Perl/Javascript/CSS, send me an email. I'd be glad to return the favor.

FYI, I researched some and apparently VBScript doesn't allow for named arguments like in regular VB. That's why using just the values worked.

[RobDog888]

With VBScript you can not use the named constants. You need to use the actual numeric values.

I thought you saw my post?

[ZeBula8]

One of the main issues
is to use the VB constants value instead of the name of the constant. For ex. Const xlCellTypeLastCell = 11
so you would use 11 in your vbscript when referencing the method that takes it as a parameter.

FYI:
What I do with this particular issue is to create my own constants for easy readibility - such as:

Public Const xlCellTypeLastCell As Long = 11
Public Const xlWhatever As Long = 12
etc..

This has been a great time saver when doing information transfer.

[daiei27]

I did, but I think named constants are different from named arguments. It's argument=value, where value can be a constant. For example, I think all of these are valid in VB but only the last one works in VBScript:

named arguments and named constants:
xxx.method argument:=xlConstant

named arguments WITHOUT named constants (which is what I thought you meant):
xxx.method argument:=1

unnamed arguments with named constants:
xxx.method xlConstant

unnamed arguments without named constants:
xxx.method 1

Overkill, I know. Just wanted to make sure everything was straight.