Import Hyperlink from Excel

[macrabbit]

Hi,

I have an excel sheet with a column containing hyperlinks.

How can I import it into FM that it creates a field where the links are working ?

Now when I import the file, it only imports the text (name of the link) and not the http//....

Thanks!

[macrabbit]

Okay, I'm one step further down the road.

I managed to get a clickable field with the 'open url' command. But still I have the problem with importing the url from excel. Especially if the url is embedded and not shown as the native text.

[Cabinetman]

Can you upload an example excel file?

[macrabbit]

like this:

http://www.file-upload.net/download-2740036/example.xls.html

[Cabinetman]

Yeah...I'm kinda stumped too.

I'm looking at saving it as an XML Spreadsheet 2003 .... but when it comes to creating the Stylesheet for importing I'm WWWwaaaaayyyyyyyy lacking in that area

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>



 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:html="http://www.w3.org/TR/REC-html40">

 

..........................

 

  

   x:FullRows="1" ss:DefaultColumnWidth="76.075471698113205"

   ss:DefaultRowHeight="12.90566037735849">

   

   

    This is the link

   

  

  

   

    

    600

    600

   

   

   False

   False

  

 

Deleted a lot of extras in there....

[Fenton]

I don't really know, but it seems the "hyperlink" in Excel can be a pointer to all kinds of things, other ranges, etc.. It also seems that there could be a way to change all these into their targeted address within Excel itself. But I couldn't see how (as I have the hyperlink option off by default, but I still see the example as text). Since I'm not much of an Excel user, the above doesn't mean it can't easily be down however.

I wanted to see if the hyperlink target value could be seen via AppleScript, and it can; example:

tell application "Microsoft Excel"

address of every hyperlink of range "$A$1" of 1st sheet of workbook 1 as text

end tell

So possibly you could post-process the Excel file and get the value that way. The above can also return all the hyperlinks of a range. But all rows would need a hyperlink value, as it just skips those that don't (making it impossible to match up to your FileMaker Import found set). A range of > 1 cell returns an AppleScript list, which would need further processing. But if all rows have a hyperlink that method could coerce the AppleScript list to return-separated text and write it out to a text file.

I'm not all that great at AppleScripting Excel either. It has a large AppleScript library, but is often awkward to use. Like why can't you get the hyperlink of a "cell", why does it have to be a "range"?

Of course, Apples own apps can also be awkward, because of Unix underpinnings.

[ CabinetMan's suggestion to Save As Excel 2004 xml, then select/import the hyperlink values is a good one, as you could import many at once. But, as he says, a stylesheet would be needed. It would not be hard to write, as you could ignore most of the Excel stuff. But I don't have time right now; must do some actual work :-]

Edited August 12, 2010 by Guest