link to specific sheet in excel file which contains points and spaces

[jonneke]

I am linking from my Access database to an Excel file. The Excel file
contains several sheets with different names. These names have spaces,
points etc. The name of an excel sheet has the same name as a field in
my form (Fieldname in script beneath). When I try to link to a sheet
in the excel file that contains spaces or points my link does not
work. When the name has no spaces or points my link does work. I tried
to put the name between brackets [] but than the link does not work at
all.

Code:

Private Sub CommandActionRegister3_Click()

Dim Location

Location = Me.Fieldname.Value & "!A1"

Application.FollowHyperlink "filename", Location, True

End Sub

Please let me know if you have any suggestions.

 

[jonneke]

I am linking from my Access database to an Excel file. The Excel file
contains several sheets with different names. These names have spaces,
points etc. The name of an excel sheet has the same name as a field in
my form (Fieldname in script beneath). When I try to link to a sheet
in the excel file that contains spaces or points my link does not
work. When the name has no spaces or points my link does work. I tried
to put the name between brackets [] but than the link does not work at
all.

Code:

Private Sub CommandActionRegister3_Click()

Dim Location

Location = Me.Fieldname.Value & "!A1"

Application.FollowHyperlink "filename", Location, True

End Sub

Please let me know if you have any suggestions.

I eventually figured it out myself.
The following script does work, you need to use quotes ' ' :

Private Sub CommandActionRegister3_Click()

Dim Location

Location = "'" & Me.Fieldname.Value & "'" & "!A1"

Application.FollowHyperlink "filename", Location, True

End Sub