Range.Formula not working with Hyperlink

[KBertsch]

I work with a BI program, QlikView, that has native export to Excel, but which can't export hyperlinks. I wrote a macro to do this ("wrote" is a misnomer; I cobbled it together from bits and pieces
I found elsewhere):

Sub ExportToExcel()
      Const xlShiftToRight = -4161
      Const xlPasteValues = -4163 '(&HFFFFEFBD)
	Const xlPasteSpecialOperationNone = -4142 '(&HFFFFEFD2)

      Set XLApp = CreateObject("Excel.Application") 'Creating excel instance in VB Script
      XLApp.Visible = True 'Settting the value to False, so this happens in background
      Set XLDoc = XLApp.Workbooks.Add 'Creating new excel workbook

      XLDoc.Sheets(1).Name = "Auditor WS" 'Adding the sheet name as Export
      Set XLSheet = XLDoc.Worksheets(1) 'Setting the XLSheet 
      Set MyTable = ActiveDocument.GetSheetObject("CH47") 'Setting MyTable variable to current QlikView Object
      MyTableCount = MyTable.GetRowCount

      Set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
      Mytable.CopyTableToClipboard True 'Copy data to Clipboard
      XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at a1
      ' Set objRange = objExcel.Range("Y:Y").EntireColumn
      XLSheet.Columns("Y:Y").Insert xlToRight

      ' objRange.Insert(xlShiftToRight)
'	    Columns("Y:Y").Select
'    Selection.Insert Shift:=xlToRight
For i = 2 to MyTableCount
'     	XLSheet.Range("Y"&i).Formula = "=Hyperlink(X"&i&")" 'This Excel formula changes the Text in column X to a hyper link in Column Y
'       It used to work, but now it doesn't so I tried the line below; that didn't work either. 
	XLSheet.Range("Y"&i).Formula = "=Hyperlink("&Range("X"&i).Value&")"
Next 
' Now have to copy column Y onto itself, but use PasteSpecial to paste values, not references, then rename column and delete original X column
	XLSheet.Range("Y1:Y"&MyTableCount).Copy
	XLSheet.Range("Y1:Y"&MyTableCount).PasteSpecial xlPasteValues
	XLSheet.Range("Y1").Formula = "IMAGE" ' Restore the column name
	XLSheet.Columns("X:X").Delete xlToLeft ' Delete the original IMAGE column
	XLApp.Close True
	
End Sub[\code]

This used to work, but now doesn't. When I look at the new Excel file, the column that should be the links has the correct path, underlined in blue,
but it's not a hyperlink (and the cell doesn't contain the formula "=hyperlink(", just the path).

Any thoughts/tips? Thanks in advance for your help!

 

[Andrew Poulsom]

If you Copy|Paste Special Values a cell that contains the HYPERLINK function the hyperlink will be removed. You will have to add the hyperlink:

How to copy cells with HYPERLINK function? - ExcelBanter

 

[KBertsch]

I'm afraid I didn't express myself clearly.

My file in QlikView contains, for example, "abc.com/123456.pdf"

In the EXPORTED file, I want the column to contain "=hyperlink(abc.com/123456.pdf)", so that when the user clicks on it once, it opens up the .pdf file.

Right now, all I'm getting in the exported file is "abc.com/123456.pdf", and while it LOOKS like a hyper link (text is blue and underlined) clicking on it
is like voting; nothing happens.

The link you provided seems to be for someone who has WORKING hyperlinks, and just wants to extract the URL from them; completely the reverse of my situation!

But I appreciate your response, thanks!

 

[Andrew Poulsom]

That's because of:

XLSheet.Range("Y1:Y"&MyTableCount).Copy
XLSheet.Range("Y1:Y"&MyTableCount).PasteSpecial xlPasteValues

 

[KBertsch]

Andrew, clearly you're much smarter than I, but I'm having a couple of problems. First and foremost, QlikView for some godforsaken reason uses VBS instead of VBA. They are almost, but not quite the same, so I get a lot of syntax errors.

Second, I don't know what the "Anchor" is, and why it's there. What does this mean? A search for "VBS Anchor" turns up nothing.
Here's what I have now, modified for my needs:

For i = 2 to MyTableCount
Range("X"&i).Select
ActiveSheet.Hyperlinks.Add Anchor=Selection.Offset(0,1), Address=Range("X"&i).Value , TextToDisplay= Range("X"&i).Value
Next

Once again, it runs without error, but does not convert the links (and there's no debugger, so I can't step through it either)

PLEASE NOTE: Although VBA uses "Anchor:=", VBS seems to prefer "Anchor=", as the former gives me syntax errors.
I hope that's all that's going on, but maybe the assignment isn't working with just an "="?

 

[KBertsch]

As I noted in my code comments, I need to paste the "Y" column back on itself, so I need to Paste Special. I've tried "xlPasteValues", and "XlPasteFormulas". Same result. What would you suggest?

 

[Andrew Poulsom]

Anchor is the cell that contains the hyperlink. It's one of the Add method's arguments, just like Address and TextToDisplay. Does Selection contain a HYPERLINK formula? I'm not familiar with QlikView, sorry.

 

[KBertsch]

I'm embarrassed to admit the solution was so simple...
XLSheet.Hyperlinks.Add XLSheet.Range("X"&i), XLSheet.Range("X"&i).Value

I was over-complicating everything, because I initially thought I had to keep the original column ("X"), and then when I found out I didn't, still focused on copying everything to "Y".

Thanks for your help!