Excel Worksheet Select (Activate ?) in VBS
 

Usually The argument to WORKSHEETS() is either a number, or a string or
string variable that represents the sheet in question.

Excel VBA may not know that the function will return a string.Perhaps using:

Function funWorksheetInput() as string
..
..
..
..
Dim s as string
s=funWorksheetInput()
objWorkbooks.Worksheets(s).Activate
--
Gary's Student
gsnu200702


"tbader01" wrote:

Hello Everyone,

I have started the following code that will allow a user to drag and drop an
XLS file ontop of this vbs script, and it will display a list of all the
available worksheet names, which they can then choose the worksheet number
and it will select that worksheet (take you to that worksheet).
This is part of a bigger idea I have in my head, but so far I am stuck with
the following line:

objWorkbooks.Worksheets(funWorksheetInput).Select

Why can't I use my function as the input selection? I get "Script Out of
Range"

I can do the following hard coding:
objWorkbooks.Worksheets(2).Select
or
objWorkbooks.Worksheets("Sheet3").Select

Also, what would that line look like if I used (or can I even use that?):
objWorksheets(?)

Any help would be appreciated!

T

'vbs code start

Dim argsNamed, argsUnnamed
set argsNamed = WScript.Arguments.Named
set argsUnnamed = WScript.Arguments.Unnamed

Dim objExcelApp ' Excel application object
Set objExcelApp = WScript.CreateObject("Excel.Application")

objExcelApp.Visible = True

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

set objWorkbooks = objExcelApp.Workbooks.Open(argsUnnamed(file))
set objWorksheets = objWorkbooks.Worksheets

objWorkbooks.Worksheets(funWorksheetInput).Select

set objWorkbooks = Nothing
set objWorksheets = Nothing
set objRange = Nothing

Set objExcelApp = Nothing
Set argsNamed = Nothing
Set argsUnnamed = Nothing
Set fso = Nothing

WScript.Quit

Function funWorksheetInput
Dim arryWS, c, ws
arryWS = split( funGetWorksheetNames, ",")
For c = 0 to Ubound(arryWS) - 1
ws = ws & c+1 & "). " & arryWS(c)
ws = ws & vbCRLF
Next
strSourceMessage = "Select from one of the following worksheets:" & vbCrLf &
ws

funWorksheetInput = InputBox( strSourceMessage, "WorkSheet Selection", "1")
If funWorksheetInput = "" Then WScript.Quit(0)
End Function


Function funGetWorksheetNames
Dim s,ws
For s = 1 To objWorksheets.Count
ws = ws & objWorksheets(s).Name
ws = ws & ","
Next
funGetWorksheetNames = ws
End Function
'vbs code end

Excel Worksheet Select (Activate ?) in VBS
 

It shouldn't matter that the function call is within the Worksheets()
property. For example, the following code works just fine.

Sub AAA()
Worksheets(GetSheetName()).Select
End Sub

Function GetSheetName() As String
GetSheetName = "Sheet3"
End Function

As long as GetSheetName returns the name of an existing worksheet, the
correct worksheet will be selected.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)



"Gary''s Student" wrote in message
...
Usually The argument to WORKSHEETS() is either a number, or a string or
string variable that represents the sheet in question.

Excel VBA may not know that the function will return a string.Perhaps
using:

Function funWorksheetInput() as string
.
.
.
.
Dim s as string
s=funWorksheetInput()
objWorkbooks.Worksheets(s).Activate
--
Gary's Student
gsnu200702


"tbader01" wrote:

Hello Everyone,

I have started the following code that will allow a user to drag and drop
an
XLS file ontop of this vbs script, and it will display a list of all the
available worksheet names, which they can then choose the worksheet
number
and it will select that worksheet (take you to that worksheet).
This is part of a bigger idea I have in my head, but so far I am stuck
with
the following line:

objWorkbooks.Worksheets(funWorksheetInput).Select

Why can't I use my function as the input selection? I get "Script Out of
Range"

I can do the following hard coding:
objWorkbooks.Worksheets(2).Select
or
objWorkbooks.Worksheets("Sheet3").Select

Also, what would that line look like if I used (or can I even use that?):
objWorksheets(?)

Any help would be appreciated!

T

'vbs code start

Dim argsNamed, argsUnnamed
set argsNamed = WScript.Arguments.Named
set argsUnnamed = WScript.Arguments.Unnamed

Dim objExcelApp ' Excel application object
Set objExcelApp = WScript.CreateObject("Excel.Application")

objExcelApp.Visible = True

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

set objWorkbooks = objExcelApp.Workbooks.Open(argsUnnamed(file))
set objWorksheets = objWorkbooks.Worksheets

objWorkbooks.Worksheets(funWorksheetInput).Select

set objWorkbooks = Nothing
set objWorksheets = Nothing
set objRange = Nothing

Set objExcelApp = Nothing
Set argsNamed = Nothing
Set argsUnnamed = Nothing
Set fso = Nothing

WScript.Quit

Function funWorksheetInput
Dim arryWS, c, ws
arryWS = split( funGetWorksheetNames, ",")
For c = 0 to Ubound(arryWS) - 1
ws = ws & c+1 & "). " & arryWS(c)
ws = ws & vbCRLF
Next
strSourceMessage = "Select from one of the following worksheets:" &
vbCrLf &
ws

funWorksheetInput = InputBox( strSourceMessage, "WorkSheet Selection",
"1")
If funWorksheetInput = "" Then WScript.Quit(0)
End Function


Function funGetWorksheetNames
Dim s,ws
For s = 1 To objWorksheets.Count
ws = ws & objWorksheets(s).Name
ws = ws & ","
Next
funGetWorksheetNames = ws
End Function
'vbs code end

Excel Worksheet Select (Activate ?) in VBS
 

Hello,
Thanks for your input, but I don't think VBS likes or accepts the "As
String" statement, since it gives me an error there. I think that might be an
exclusive thing to VBA?
Were you able to copy and paste my code to see what I am seeing?
It just seems like it should work like I have it!
I guess I can use vartype to see just exactly what the function is
returning...
If you have any insights, please let me know!
Thanks!

"Gary''s Student" wrote:

Usually The argument to WORKSHEETS() is either a number, or a string or
string variable that represents the sheet in question.

Excel VBA may not know that the function will return a string.Perhaps using:

Function funWorksheetInput() as string
.
.
.
.
Dim s as string
s=funWorksheetInput()
objWorkbooks.Worksheets(s).Activate
--
Gary's Student
gsnu200702


"tbader01" wrote:

Hello Everyone,

I have started the following code that will allow a user to drag and drop an
XLS file ontop of this vbs script, and it will display a list of all the
available worksheet names, which they can then choose the worksheet number
and it will select that worksheet (take you to that worksheet).
This is part of a bigger idea I have in my head, but so far I am stuck with
the following line:

objWorkbooks.Worksheets(funWorksheetInput).Select

Why can't I use my function as the input selection? I get "Script Out of
Range"

I can do the following hard coding:
objWorkbooks.Worksheets(2).Select
or
objWorkbooks.Worksheets("Sheet3").Select

Also, what would that line look like if I used (or can I even use that?):
objWorksheets(?)

Any help would be appreciated!

T

'vbs code start

Dim argsNamed, argsUnnamed
set argsNamed = WScript.Arguments.Named
set argsUnnamed = WScript.Arguments.Unnamed

Dim objExcelApp ' Excel application object
Set objExcelApp = WScript.CreateObject("Excel.Application")

objExcelApp.Visible = True

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

set objWorkbooks = objExcelApp.Workbooks.Open(argsUnnamed(file))
set objWorksheets = objWorkbooks.Worksheets

objWorkbooks.Worksheets(funWorksheetInput).Select

set objWorkbooks = Nothing
set objWorksheets = Nothing
set objRange = Nothing

Set objExcelApp = Nothing
Set argsNamed = Nothing
Set argsUnnamed = Nothing
Set fso = Nothing

WScript.Quit

Function funWorksheetInput
Dim arryWS, c, ws
arryWS = split( funGetWorksheetNames, ",")
For c = 0 to Ubound(arryWS) - 1
ws = ws & c+1 & "). " & arryWS(c)
ws = ws & vbCRLF
Next
strSourceMessage = "Select from one of the following worksheets:" & vbCrLf &
ws

funWorksheetInput = InputBox( strSourceMessage, "WorkSheet Selection", "1")
If funWorksheetInput = "" Then WScript.Quit(0)
End Function


Function funGetWorksheetNames
Dim s,ws
For s = 1 To objWorksheets.Count
ws = ws & objWorksheets(s).Name
ws = ws & ","
Next
funGetWorksheetNames = ws
End Function
'vbs code end

Excel Worksheet Select (Activate ?) in VBS
 

I understood VBS does not have datatypes, everything is a variant (Or Object
is also available ?).

Anyway, you problem is caused by the Inputbox function returning a string;
check the Help.
So you are trying to set the Worksheets("1"), not Worksheets(1).

One solution is:
funWorksheetInput = CLng(InputBox( strSourceMessage, "WorkSheet Selection",
"1"))
or better to use objExcelApp.Inputbox, so you can limit the values to
Numbers with "Type:=1".

NickHK

"tbader01" wrote in message
...
Hello,
Thanks for your input, but I don't think VBS likes or accepts the "As
String" statement, since it gives me an error there. I think that might be
an
exclusive thing to VBA?
Were you able to copy and paste my code to see what I am seeing?
It just seems like it should work like I have it!
I guess I can use vartype to see just exactly what the function is
returning...
If you have any insights, please let me know!
Thanks!

"Gary''s Student" wrote:

Usually The argument to WORKSHEETS() is either a number, or a string or
string variable that represents the sheet in question.

Excel VBA may not know that the function will return a string.Perhaps
using:

Function funWorksheetInput() as string
.
.
.
.
Dim s as string
s=funWorksheetInput()
objWorkbooks.Worksheets(s).Activate
--
Gary's Student
gsnu200702


"tbader01" wrote:

Hello Everyone,

I have started the following code that will allow a user to drag and
drop an
XLS file ontop of this vbs script, and it will display a list of all
the
available worksheet names, which they can then choose the worksheet
number
and it will select that worksheet (take you to that worksheet).
This is part of a bigger idea I have in my head, but so far I am stuck
with
the following line:

objWorkbooks.Worksheets(funWorksheetInput).Select

Why can't I use my function as the input selection? I get "Script Out
of
Range"

I can do the following hard coding:
objWorkbooks.Worksheets(2).Select
or
objWorkbooks.Worksheets("Sheet3").Select

Also, what would that line look like if I used (or can I even use
that?):
objWorksheets(?)

Any help would be appreciated!

T

'vbs code start

Dim argsNamed, argsUnnamed
set argsNamed = WScript.Arguments.Named
set argsUnnamed = WScript.Arguments.Unnamed

Dim objExcelApp ' Excel application object
Set objExcelApp = WScript.CreateObject("Excel.Application")

objExcelApp.Visible = True

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

set objWorkbooks = objExcelApp.Workbooks.Open(argsUnnamed(file))
set objWorksheets = objWorkbooks.Worksheets

objWorkbooks.Worksheets(funWorksheetInput).Select

set objWorkbooks = Nothing
set objWorksheets = Nothing
set objRange = Nothing

Set objExcelApp = Nothing
Set argsNamed = Nothing
Set argsUnnamed = Nothing
Set fso = Nothing

WScript.Quit

Function funWorksheetInput
Dim arryWS, c, ws
arryWS = split( funGetWorksheetNames, ",")
For c = 0 to Ubound(arryWS) - 1
ws = ws & c+1 & "). " & arryWS(c)
ws = ws & vbCRLF
Next
strSourceMessage = "Select from one of the following worksheets:" &
vbCrLf &
ws

funWorksheetInput = InputBox( strSourceMessage, "WorkSheet Selection",
"1")
If funWorksheetInput = "" Then WScript.Quit(0)
End Function


Function funGetWorksheetNames
Dim s,ws
For s = 1 To objWorksheets.Count
ws = ws & objWorksheets(s).Name
ws = ws & ","
Next
funGetWorksheetNames = ws
End Function
'vbs code end