Open Excel on Specified worksheet

[nezza]

Hi,
I want to create a button on one of my forms that takes the user to an Excel workbook. I can open Excel to the workbook I want using the AppRun macro with the following code:

excel.exe "T:\B-GLBCUS\Neil\Trade Reporting Project\[Total Subscriptions per Month.xls]"

What do I need to add to this in order to specify which worksheet in the workbook I'd like Excel to open in. For example I'd like to tell Excel to open with Sheet 3 rather then Sheet 1.

Thanks for any help.

 

[GohDiamond]

Try:
excel.exe "T:\B-GLBCUS\Neil\Trade Reporting Project\[Total Subscriptions per Month.xls]Sheet3!$A$1"

Let us know if that helps,

Goh

 

[Anthony George]

A Good Solution

Hi

Put this into a command button, or wherever you want.

I used a button called Command 16

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

'Dimension the Variables
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

'Set the application
Set xlApp = New Excel.Application

'Make the Application Visible
xlApp.Visible = True

'Set the workbook and the filepath 'Change "C:\Book1.xls " to your own filepath and Workbook name
Set xlWkbk = xlApp.Workbooks.Open("C:\Book1.xls ")

'Set the worksheet that you want the workbook to open on
xlApp.Sheets("Sheet3").Select

'Turn everything off otherwise yo will have problems with your spreadsheet
Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSht = Nothing

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

It does work and it does not wreck your Excel sheet providing that you copy the code exactly as shown.

Kindest Regards

Tony

 

[nezza]

Just what I need. Thanks for your help.

 

[Geoffk]

Tony

I have used your code to open an Exel worksheet

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

'Dimension the Variables
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

'Set the application
Set xlApp = New Excel.Application

'Make the Application Visible
xlApp.Visible = True

'Set the workbook and the filepath 'Change "C:\Book1.xls " to your own filepath and Workbook name
Set xlWkbk = xlApp.Workbooks.Open("C:\PTVC\Routing\CustMacro.xls ")

'Set the worksheet that you want the workbook to open on
xlApp.Sheets("Sheet3").Select

'Turn everything off otherwise yo will have problems with your spreadsheet
Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSht = Nothing

Exit_Command16_Click:
Exit Sub

This works fine, for wich I thank you, my problem is that I have a macro to transpose the workbook called Autpen which auto runs when workbook CustMacro.xls opens.

The macro runs ok when I open the workbook from MS access using a Hyperlink and when I open it directly, when I run your code it opens the workbook but does not run the macro, do you have a solution.

After running the macro I need to import the transposed workbook into MS Access table Customers

Thanks in advance.
Geoffk

 

[socko139]

Follow Up

When I try copying this code I keep running into a compile Error: User Defined Type not Defined. Then when I go to debug its highlighting my bold parts:

Private Sub Command100_Click()
On Error GoTo Err_Command100_Click

'Dimension the Variables
Dim xlApp As Excel.Application

What am I missing?

 

[boblarson]

To use Dim xlApp as Excel Application you must first set a reference to Excel at Tools > References

 

[BadScript]

To use Dim xlApp as Excel Application you must first set a reference to Excel at Tools > References

I don't have an option to set a reference to excel in access.. am i doing something wrong?

 

[BadScript]

Never mind, figured it out, it's in the VB editor..
Thanks for this post