How to generate/create Excel range subaddress in an Access hyperlink field.

[JamesPower4]

Hello: I have been having great difficulty generating an Excel range subaddress in Access that includes the worksheet name and range's cell address(es). I would like to use this string as a subaddress in the hyperlink. The worksheets have spaces in their names, so the subaddress should look something similar to 'Sheet 1'!R1C1:R12C3. I would like to do this so that I can embed a hyperlink to the range (subaddress) in an Access field (typed as hyperlink). Creating a hyperlink to just the file works with no problems; it's including the subaddress where I'm having difficulty. Examples online show multiple ways to accomplishing this, from simply creating the full hyperlink string (concatenating the four hyperlink parts, separating them with "#") to using the hyperlink object and populating its properties.
My problem is that no matter what approach I try, Access invariably replaces the exclamantion point ("!") in the generated subaddress with a dollar sign ("$"), and then I get an "invalid address" error when I try to use the hyperlink. Does anyone have any idea how I can generate a range address, with a sheet name containing spaces, in Access?
Thanks in advance.
Jim Power

 

[theDBguy]

Hi Jim. Welcome to AWF!

Did you say you're using a Hyperlink Field? If so, are you able to manually add the range subaddress to the field's data? Just curious...

 

[JamesPower4]

Hi Jim. Welcome to AWF!

Did you say you're using a Hyperlink Field? If so, are you able to manually add the range subaddress to the field's data? Just curious...

Hi! Thanks for getting back to me so quickly. In the interest of brevity I posed my question as building a hyperlink, but the problem occurs elsewhere as well. Here's an example of a failed attempt to build the hyperlink in a control (txtLink) that's set to be a hyperlink and bound to a hyperlink field:

With .txtLink.Hyperlink
.Address = mPf.RunData.FullPath
.SubAddress = mWrkSheet.Name
.ScreenTip = "Sheet " & mWrkSheet.Name
.TextToDisplay = "Worksheet " & mWrkSheet.Name
End With

I gave up on that, and then tried to see if I could put a link to the Excel range in the database. Here's the code, with the TableName, FileName, and Range passed as strings:

DoCmd.TransferSpreadsheet Transfertype:=acLink, Spreadsheettype:=acSpreadsheetTypeExcel12, _
TableName:=mAbbrRngName, _
FileName:=mPf.RunData.FullPath, hasfieldnames:=True, _
Range:=RangeAddress

I'm creating the RangeAddress string using code modified from How do I get ranges address...

The RangeAddress string has the value: 'Field Data & Calculations'!R5C1:R1094C34 (I thought using R1C1 addresses might work). When the DoCmd.TransferSpreadsheet statement runs I get the error message:

"The Microsoft Access database engine could not find the object "Field Data & Calculations'$R5C1:R1094C34'. Make sure the object exists...

I made the "$" sign in the above in bold to show it's replacing the "!". In typing the above I just noticed that the error message begins with double quotes, has a single quote in front of the $, and then ends with a single quote. Could that somehow be the problem??

Thank you very much for considering my problem.

 

[theDBguy]

Hi. Thank you for the additional clarification. I don't use Hyperlink fields, that's why I was curious.

In my mind, if you can create the link manually, then you should be able to do it also programmatically. That's why I asked if you can do it manually. I'm not sure you have mentioned yet if you can do that or not.

In case you're interested, this article partially explains why I don't use hyperlink fields.

Text or Hyperlink

This is a topic of discussion that I have participated in the forums a number of times in the past and will probably continue to engage in t...

thedbguy.blogspot.com

 

[Gasman]

Hi! Thanks for getting back to me so quickly. In the interest of brevity I posed my question as building a hyperlink, but the problem occurs elsewhere as well. Here's an example of a failed attempt to build the hyperlink in a control (txtLink) that's set to be a hyperlink and bound to a hyperlink field:

With .txtLink.Hyperlink
.Address = mPf.RunData.FullPath
.SubAddress = mWrkSheet.Name
.ScreenTip = "Sheet " & mWrkSheet.Name
.TextToDisplay = "Worksheet " & mWrkSheet.Name
End With

I gave up on that, and then tried to see if I could put a link to the Excel range in the database. Here's the code, with the TableName, FileName, and Range passed as strings:

DoCmd.TransferSpreadsheet Transfertype:=acLink, Spreadsheettype:=acSpreadsheetTypeExcel12, _
TableName:=mAbbrRngName, _
FileName:=mPf.RunData.FullPath, hasfieldnames:=True, _
Range:=RangeAddress

I'm creating the RangeAddress string using code modified from How do I get ranges address...

The RangeAddress string has the value: 'Field Data & Calculations'!R5C1:R1094C34 (I thought using R1C1 addresses might work). When the DoCmd.TransferSpreadsheet statement runs I get the error message:

"The Microsoft Access database engine could not find the object "Field Data & Calculations'$R5C1:R1094C34'. Make sure the object exists...

I made the "$" sign in the above in bold to show it's replacing the "!". In typing the above I just noticed that the error message begins with double quotes, has a single quote in front of the $, and then ends with a single quote. Could that somehow be the problem??

Thank you very much for considering my problem.

So I would say your concatenation is very wrong.
Put it into a string variable and debug.print it before you try to use it. Then you can use the string variable in the hyperlink.