Hyperlinking to a specific Excel sheet

[ezz]

Hi,

I have Job Descriptions in Excel workbook (each job is in a different sheet) and I want to create a hyperlink that appears in my Access table and form that directs me to a specific sheet.

I tried using a command button in a form, it worked, but I have many records and the button will always direct me to one sheet.

If there's a way I can create a hyperlink that directs me to the specific sheet, it would be appreciated.

Thanks.

[rpeare]

what code have you tried?

[ezz]

what code have you tried?

This is the code I used in a command button (On-Click event)

Private Sub OpenExcel_Click()
On Error GoTo Err_OpenExcel_Click
Dim oApp As Object



Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open ("WBname.xlsx")


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


'Turn everything off otherwise yo will have problems with your spreadsheet
Set oApp = Nothing


Exit_OpenExcel_Click:
Exit Sub


Err_OpenExcel_Click:
MsgBox Err.Description
Resume Exit_OpenExcel_Click




End Sub

But as I go through the records in the form, I want to open different sheets when clicking the button, but it only directs me to the sheet mentioned in the code. But I figured to use hyperlinks, I just don't know how to open a specific sheet using hyperlink.

[rpeare]

you've hard coded a sheet name into your code so it will always go to the same sheet. You want a variable in there that expresses the name of the excel sheet, so in your 'job posting' table can you add another field that indicates the excel sheet the job listing is on, then you could just substitute in that value where you've stated "Sheet1".

so let's say the field on your form is 'ExcelSheetName' you would just modify your code to

oapp.sheets(ExcelSheetName).select

I am not so sure how this would work on a continuous form but should work just fine on a single form.

[ezz]

you've hard coded a sheet name into your code so it will always go to the same sheet. You want a variable in there that expresses the name of the excel sheet, so in your 'job posting' table can you add another field that indicates the excel sheet the job listing is on, then you could just substitute in that value where you've stated "Sheet1".

so let's say the field on your form is 'ExcelSheetName' you would just modify your code to

oapp.sheets(ExcelSheetName).select

I am not so sure how this would work on a continuous form but should work just fine on a single form.

That didn't work. It opens the excel workbook but not the specified sheet.

Do you know how to do that in a hyperlink?

[rpeare]

you're using

application.followhyperlink to open an excel file?

I don't know that you can specify a sheet using .followhyperlink

https://msdn.microsoft.com/en-us/lib.../ff839280.aspx

There's no switch that I can find to specify a sheet, you might be able to use it in conjunction with other excel objects but if you're going to do that you might as well do the whole thing with excel objects and abandon the followhyperlink for excel files only. The only examples I can find for opening to a specific sheet follow the example I gave you.

though I'm curious the code you posted doesn't have the .followhyperlink command so I'm not sure if I'm even interpreting this correctly.