retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

[condorelli]

Hi all!
Is there any way to choose a sheet in

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

????
I need to open another excel file with "shell" in a specific sheet.

Thanks

 

[RedBeard]

There's no command line switch that will activate a worksheet. You have to do one of the following:

-Save the workbook with the desired sheet active. Excel will open it active.

-Put a macro in the workbook to activate the sheet. This would go in the Workbook_Open event handler in the ThisWorkbook module.

 

[Jaafar Tribak]

Or you could do this :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

GetObject(filename).Worksheets("specific sheet").Activate

 

[condorelli]

Or you could do this :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

GetObject(filename).Worksheets("specific sheet").Activate

Now i have in fileA.xlsm

Private Sub Workbook_Open()

    Dim strTestString
    strTestString = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    Dim retval As String
    Dim filename As Variant
    filename = "\\xxx\yyy\[B]fileB[/B].xlsm"
    retval = Shell("excel.exe """ & filename & """", vbNormalFocus)
    GetObject(filename).Worksheets(strTestString).Activate

End Sub

It doesn't show any error but the sheet activated is the saved one and not the "specific sheet".

 

[Jaafar Tribak]

Try running a small loop to wait until the workbook is loaded in the shelled application ... Something along these lines :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

Do
    DoEvents
Loop Until Not GetObject(filename) Is Nothing

GetObject(filename).Worksheets("specific sheet").Activate

 

[condorelli]

Try running a small loop to wait until the workbook is loaded in the shelled application ... Something along these lines :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

Do
    DoEvents
Loop Until Not GetObject(filename) Is Nothing

GetObject(filename).Worksheets("specific sheet").Activate

Same as above...No errors but the sheet activated is the saved one

 

[Jaafar Tribak]

Are you sure the strTestString variable has the correct sheet in it ?

 

[condorelli]

Are you sure the strTestString variable has the correct sheet in it ?

Yes, i've tried inserting it manually too, but no luck

 

[Jaafar Tribak]

Try this and see what you get on the Debug.print line :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

Do
    DoEvents
Loop Until Not GetObject(filename) Is Nothing

Debug.Print GetObject(filename).Sheets(strTestString).Name

GetObject(filename).Sheets(strTestString).Select

 

[condorelli]

Try this and see what you get on the Debug.print line :

retval = Shell("excel.exe """ & filename & """", vbNormalFocus)

Do
    DoEvents
Loop Until Not GetObject(filename) Is Nothing

Debug.Print GetObject(filename).Sheets(strTestString).Name

GetObject(filename).Sheets(strTestString).Select

run-time '1004' error