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