Open a specific excel sheet from Access

[navin]

Hi All,

In my from, i let users open an excel file with
application.followhyperlink. Excel sheet has separate sheets with name
of the user. this excel sheet is already saved at the shared drive.

What is need is that, when the user opens the excel file, file should
select the sheet with the user's name on it.

PLease let me know if this is possible.

thanks,
navin

 

[Dale Fye]

Navin,

It is possible with automation. Following is air code, so don't count on
the syntax to be perfect, but it will give you the idea. This would fire in
the click event of a command button (cmdOpenExcel).

Private Sub cmdOpenExcel_Click

Dim xlObj as Object
Dim wbk As Object
Dim sht As Object

If LEN(DIR(me.txt_FileName)) = 0 then
msgbox "File does not exist!"
Exit sub
endif

Set xlobj = CreateObject("Excel.Application")
xlobj.Visible = True

Set wbk = xlobj.Workbooks.Open("H:\BreifingAttendance.xls")
Set sht = wbk.Sheets("Sheet2")
sht.Activate

Set sht = Nothing
Set wbk = Nothing
Set xlobj = Nothing

End Sub

You might also want to throw in some code to check to see whether Excel is
already open (google on GetObject +CreateObject). If you only want them to
be able to see their sheet, then you could loop through all of the sheets and
set their visible properties to false except for that belonging to the
individual. This might look something like:

For each sht IN wbk.sheets
sht.Visible = (sht.Name = username)
Next sht

HTH
Dale

 

[Tom van Stiphout]

On Mon, 17 Dec 2007 08:12:38 -0800 (PST), navin

Name a range on each sheet. E.g. on Sheet2, A1 gets a range name of
"Ref2". Then you can write:
Application.FollowHyperlink "c:\junk\book1.xlsx", "Ref2"

-Tom.