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

 

[Jaafar Tribak]

What about if you replace the Debu.print line with the following :

Debug.Print GetObject(filename).FullName

 

[condorelli]

What about if you replace the Debu.print line with the following :

Debug.Print GetObject(filename).FullName

error 1004 in

GetObject(filename).Sheets(strTestString).Select

In the Immediate Window i have the correct filename (\\xxx\yyy\etc)

 

[Jaafar Tribak]

If the line :

Debug.Print GetObject(filename).FullName

shows the correct file name but the line :

GetObject(filename).Sheets(strTestString).Select

generates error 1004, it means there is no sheet in the workbook with name held in the strTestString variable.

 

[condorelli]

If the line :

Debug.Print GetObject(filename).FullName

shows the correct file name but the line :

GetObject(filename).Sheets(strTestString).Select

generates error 1004, it means there is no sheet in the workbook with name held in the strTestString variable.

The code...

Private Sub Workbook_Open()

    Dim strTestString
    strTestString = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))

    Excel.Application.Workbooks.Open("\\xxx\yyy\fileB.xlsm").Sheets(strTestString).Activate
    ActiveWindow.WindowState = xlMaximized
    
    ThisWorkbook.Close SaveChanges = False

End Sub

...is actually working so the problem can't be strTestString variable

I need to open a new instance so i have to...

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

...but I can't get it to work

 

[Jaafar Tribak]

Instead of shelling the new application you could automate it using the New Keyword ... something like this :

Dim oXlApp As New Application

oXlApp.Workbooks.Open("\\xxx\yyy\fileB.xlsm").Sheets(strTestString).Activate

Edit

Use oXlApp.Visible=True before opening the workbook.

 

[condorelli]

Instead of shelling the new application you could automate it using the New Keyword ... something like this :

Dim oXlApp As New Application

oXlApp.Workbooks.Open("\\xxx\yyy\fileB.xlsm").Sheets(strTestString).Activate

Edit

Use oXlApp.Visible=True before opening the workbook.

FileB load up in the correct sheet

 

[Jaafar Tribak]

FileB load up in the correct sheet

Glad it is now working for you.

 

[condorelli]

Glad it is now working for you.

You solved my problems very fast!
Many many many thanks!!! ray: