Access vba to insert hyperlink formula in an excel tab column 2

[sxschech]

Been working on formatting an excel file with multiple tabs via access since I not allowed to create as a macro in excel. Did some searching and found a formula for hyperlinking to a cell in another tab based on the value in a cell in the current tab. Trying to convert it such that can be parameterized in access vba. I am putting hyperlinks in col A where the item in column B is colored. Here is the formula. Items in red need to be adjusted via code:

=HYPERLINK(ADDRESS(MATCH(S143,DupesList_304!B:B,0),1,,,"[TARGET_COMPANIES_6125_TL_pd.xlsx]DupesList_304"),"See Companies")

Another part of the problem is dealing with the quotes. I was able to get the row number using [tt]cell.row[/tt] and I can get the index number of the sheet that needs to be used, but can't figure out how to convert the Sheet number to the name. In access vba, ws.name gives me the name of the current sheet, and ws.index+1 can give me the index number of the sheet I want, but not sure how to directly get the name of tab 3. Or do I not need the name in order to populate the above formula? Seems that [tt]worksheets(3).name[/tt] works in Excel but not in access, or the way I set it up, doesn't seem to work. This code should put the hyperlink in the appropriate cell of the current sheet (second tab), but needs to refer to the lookup range using match on the third tab. The hyperlink will take the user to the corresponding cell in the third tab when clicked. Since the tab names change each time the code is run, can't hard code the name.

I started building a string...
[tt]stHyperlink = "S" & cell.row & "," & ws.Index + 1 '& ws.item(3).Name

as well as trying to put into formula...
.cells(cell.row, 1).Formula = "=HYPERLINK(ADDRESS(MATCH(.cells('S'" & .cell.row & ")," & ws.Name & "!B:B,0),1,,,'[" & FileName & "]DupesList_304'),'See Companies')"
[/tt]
Not sure how much of the code is needed to be seen since all the other formatting and looping seems ok. I'll put the beginning part.

    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    Set wb = objapp.workbooks.Open(FileName, True, False)
    'select all worksheets & cells In turn
    For Each ws In wb.worksheets
        'amend font To whatever
        With ws
...
...
[indent]If indexcolor = 3 Then
   '=HYPERLINK(ADDRESS(MATCH(S143,DupesList_304!B:B,0),1,,,"[TARGET_COMPANIES_6125_TL_pd.xlsx]DupesList_304"),"See Companies")
   'stHyperlink = "HYPERLINK(ADDRESS(MATCH(S143,DupesList_304!B:B,0),1,,," '"[TARGET_COMPANIES_6125_TL_pd.xlsx]DupesList_304"'"),"'"See Companies"'")"
  stHyperlink = "S" & cell.row & "," & ws.Index + 1 '& ws.item(3).Name
 .cells(cell.row, 1).Formula = "=HYPERLINK(ADDRESS(MATCH(.cells('S'" & .cell.row & ")," & ws.Name & "!B:B,0),1,,,'[" & FileName & "]DupesList_304'),'See Companies')"
End If[/indent]

 

[lameid]

At a glance, assuming your Excel formula is correct, it looks like all you need to know is how to get double quotes in your string.

There are two ways. First a pair of double quotes in a double quote delimited string gives you one double quote...


So the string value of """Hi""" is "Hi". The outer most quotes are for the string and the two inner ones are for the embedded double quotes.

The below should work better.

.cells(cell.row, 1).Formula = "=HYPERLINK(ADDRESS(MATCH(.cells(""S""" & .cell.row & ")," & ws.Name & "!B:B,0),1,,,""[" & FileName & "]DupesList_304""),""See Companies"")"

 

[Andrzejek]

There are two ways [..] to get double quotes in your string."

And the second way would be - use Chr(34) to indicate a [tt]"[/tt]
So "Hi" would be: [tt]Chr(34) & "Hi" & Chr(34)[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lameid]

Andy, I guess that's what happens when I answer a question tired while installing SQL Express.

 

[sxschech]

Hi Guys, I'll try your quote suggestion tonight. Still looking for how to get the tab name to replace the hard coded tab name:

DupesList_304

This is the third tab in the excel file. Would I need to loop through the tabs and grab the name from that where the tab index = 3, or is there a more direct way to obtain it?

 

[lameid]

I would expect wb.worksheets(3).name would get you the third worksheet name.

 

[sxschech]

Thanks all, your suggestions on quotes worked.
Lameid, when I changed the ws that I tried to use before to the wb in your example, it worked.

The other thing I had to change was column S to column T and remove the dot after the column letter--> [tt]T" & .cell.row[/tt] --> [tt]T" & cell.row [/tt]

I also figured out how to put the hyperlink in the cell containing the company name, so could eliminate an extra column containing the hyperlink with the word see companies. Now user can click directly on the company name to hyperlink.

[tt]
stHyperlink = .cells(cell.row, 3).Value
.cells(cell.row, 3).Formula = "=HYPERLINK(ADDRESS(MATCH(T" & cell.row & "," & wb.worksheets(3).Name & "!B:B,0),2,,," & Chr(34) & "[" & FileNameNoExt(FileName) & ".xlsx]" & wb.worksheets(3).Name & Chr(34) & "),""" & stHyperlink & """)"

[/tt]

 

[sxschech]

Tried to give both you guys stars, not sure if it worked.