I'm trying to call an Excel file from Access VBA using the Shell command. I
want to be able to select one of two worksheets to be activated whenever I
make the call. One shows a monthly chart and the other a quarterly chart.

Is there a command-line switch that I can use when calling Excel.exe that
will activate the worksheet of my choice?

Thanks,
TJ Dowling

There are no commandline switches that will do this.

In fact, if you're using the Shell command, I'm not sure you can do it at all.

But you could use something like this instead:

Option Explicit
Sub testme()

Dim XLApp As Object
Dim XLWkbk As Object
Dim wkbkName As String
Dim XLWasRunning As Boolean

wkbkName = "C:\my documents\excel\book1.xls"

XLWasRunning = True
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number < 0 Then
Set XLApp = CreateObject("Excel.Application")
XLWasRunning = False
End If

XLApp.Visible = True 'at least for testing!

Set XLWkbk = XLApp.workbooks.Open(FileName:=wkbkName)

'this line will change depening on the sheet to select
XLApp.GoTo XLWkbk.worksheets("Sheet2").Range("a1")

Set XLWkbk = Nothing
Set XLApp = Nothing

End Sub

TJ Dowling wrote:

I'm trying to call an Excel file from Access VBA using the Shell command. I
want to be able to select one of two worksheets to be activated whenever I
make the call. One shows a monthly chart and the other a quarterly chart.

Is there a command-line switch that I can use when calling Excel.exe that
will activate the worksheet of my choice?

Thanks,
TJ Dowling

--

Dave Peterson

ps. I didn't test this in access--but it did work ok in MSWord.

Dave Peterson wrote:

There are no commandline switches that will do this.

In fact, if you're using the Shell command, I'm not sure you can do it at all.

But you could use something like this instead:

Option Explicit
Sub testme()

Dim XLApp As Object
Dim XLWkbk As Object
Dim wkbkName As String
Dim XLWasRunning As Boolean

wkbkName = "C:\my documents\excel\book1.xls"

XLWasRunning = True
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number < 0 Then
Set XLApp = CreateObject("Excel.Application")
XLWasRunning = False
End If

XLApp.Visible = True 'at least for testing!

Set XLWkbk = XLApp.workbooks.Open(FileName:=wkbkName)

'this line will change depening on the sheet to select
XLApp.GoTo XLWkbk.worksheets("Sheet2").Range("a1")

Set XLWkbk = Nothing
Set XLApp = Nothing

End Sub

TJ Dowling wrote:

I'm trying to call an Excel file from Access VBA using the Shell command. I
want to be able to select one of two worksheets to be activated whenever I
make the call. One shows a monthly chart and the other a quarterly chart.

Is there a command-line switch that I can use when calling Excel.exe that
will activate the worksheet of my choice?

Thanks,
TJ Dowling

--

Dave Peterson

--

Dave Peterson