Excel

[Garyj]

I have a command button that opens an excel document. How do I open a specific worksheet in my excel document?

Thanks

 

[Fizzio]

You will need to use the excel object in code to do this. If you are already manipulating the file via this then it is fairly simple. If you are opening the file via a FollowHyperlink or Shell method then it will be a little more lengthy.

How are you currently opening the file?

 

[Garyj]

I used the wizard to open excel and then added the path where the document resides on the server. The name of my doc is Permit and I have 11 tabs within the worksheet. I am not good in writing code. I am still learning. If you could provide an example I would appreciate it.

Thanks in advance

 

[Fizzio]

To get you started in code then, before we hit the heavy stuff, have a look at the code produced by the wizard. In design mode, select the command button, click the events tab and where it says [event procedure] next to the On Click event, click the ... and copy that code and post it here so I can see how access is opening the document.

 

[Garyj]

excel

Here is the code.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stAppName As String

stAppName = "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE h:\PTSa\Permits\permit.xls"
Call Shell(stAppName, 1)

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Fizzio]

Try this instead you must have the reference to the Microsoft Excel object selected in Tools->References

Private Sub Command0_Click() 
On Error GoTo Err_Command0_Click 

Dim objXL As Object 
Set objXL = CreateObject("Excel.Application") 
With objXL.Application 
.Visible = True 
.Workbooks.Open "YourDocument.xls"
.Worksheets("NameofWorksheet").Activate
End With 

Exit_Command0_Click: 
Exit Sub 

Err_Command0_Click: 
MsgBox Err.Description 
Resume Exit_Command0_Click 

End Sub

 

[Garyj]

excel

where you have .worksheet[worksheetname].activate I will place the name of each worksheet. Do I add eleven lines?

Example.

Name of doc: Permit
Tab1: 24462
Tab2: 32748
Tab3: 3642

This goes on to about eight more times.

Thanks again

 

[Fizzio]

You can only show one worksheet at a time!
The Worksheets("WorksheetName").Activate just selects that worksheet.

Maybe I have misunderstood what you need?

 

[Garyj]

excel

No, I understand now I think that will work.

If you don't mind I have another question.

I have been trying to solve this problem for two weeks and I hope you can help.

Read next reply

 

[Garyj]

I have a table and fields are as follows.

TransactionID: AutoNumber
TransactionDate: Date/time
PermitName: Text
SourceName: Text
DepositAmount: Currency
WithdrawalAmount: Currency
PostOfficeBalance: Currency
ContactName: text

My question:

I have a form that opens a subform in datasheet mode where data can be entered and displayed. The first line has a depositamount of 1000.00 for 05/30/2003. The second line begins a new month with only withdrawalamounts for each business day for June. Now here my problem. My form has a running sum in the footer which displays the balance as of the last transaction day.

I need my report to show me the ending balance for each month and also includes any depositamounts during the course. I don't need a sum or total for PostOffice balance. I am only interest in the depositamount and withdrawalamount.

I hope I explained this correctly.

thanks in advance

 

[Fizzio]

I would post this as a new topic in the reports forum - I'll have a think in the meantime.

 

[Garyj]

Thank you

I appreciate all your help