Open Excel workbook form access 1

[qureshi]

Hi,
I am using office 97. I have a database in access 97 which is primarily used to store the details of the different machines used in the office. There is one workbook in Excel 97 which contains the service history of those machines in different worksheets.
The form in access which shows the user about details of the machines also has a button which should open the specific "worksheet". Any help will be appreciated.

One more thing, When i try to define excel object i dont see it in the list e.g.

dim objexcel as excel.application
Cant see the excel in the drop down list

Thanks in advance
Qureshi

 

[lambuhere1]

You need to check the Excel Reference in Tools-References in your VBE mode.

And when the user presses a Button on the form in Access you need to call in Excel Application and that particular workbook with your sheet of interest activated.

Hope this helps.

Ram P

 

[qureshi]

Thanks for the reply
I got excel working. The only problem i am facing is getting to the correct worksheet. The worksheets are named in the format "ER0010".e.g its a string.
Using workbook.sheet() command requires an index number. Is there a way to get a variable into it.i.e

workbook.Sheet(WorkSheetName) where WorkSheetName holds the values like ER0012 etc

Thanks

 

[TLady]

ExcelWK.Sheets("ER0010&quot.Select
'this will select particular sheet

If you want to get them one by one you go:

dim i as integer
dim xSheet as String

For i = 1 To ExcelWK.Worksheets.Count
xSheet=ExcelWK.Sheets(i).Name
Next

So,it'll loop through every sheet in WBook
TIA

 

[Zathras]

You can use

ActiveWorkbook.Sheets("ER0012")

or

ActiveWorkbook.Sheets(WorkSheetName)

as in

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Value = 3 Then
    ActiveWorkbook.Sheets("Sheet1").Activate
  End If
End Sub

 

[qureshi]

My VBA program picks up the Sheet name which is the machine ID number in an access database.So depending on which machine the user is viewing the program has the correct sheet name. Now I want to select that sheet programatically.A variable has to be there which hold the sheet name.

Here my code
'sheetname is a public string variable
' it holds the machine ID which is the worksheet name also

Private Sub Command41_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.application&quot

Set xlBook = xlApp.Workbooks.Open("\\abc\Service B\service history.xls&quot

'Need to get this working
xlBook.Sheets(sheetName).Select
xlApp.Visible = True

xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlsheet = Nothing

End Sub

Hope this clears my position

Thanks in advance

Qureshi

 

[Zathras]

Not sure, but I would think you want to .Activate instead of .Select.

More importantly, try it without the xlApp.Quit.