I have an Excel spreadsheet that has a link to a web page in each cell.
Is there a way to import the spreadsheet and maintain the links?
I have tried importing external data as a new table and as a linked table.
In each case the cell text is available but not the embedded link.
If I open the spreadsheet with "Call OpenExcelFile(path & "APNI_Image_Links.xlsx")" then the links work.
There are 17 sheets in the Excel document with some 70,000 separate entries. I would like to be able to link these cells to the relevant entries in one, or more, of my Access tables.
The call that works suggests the nature your problem. If you need to supply the path for concatenation, it suggests that what you have in the spreadsheet is not a complete path somehow. Which perhaps reflects on differences between Access and Excel hyperlinks.
Having never tried Excel hyperlinks, I admit it is a guess. Perhaps part of the import process would be that whenever you are importing, import by treating the sheet as a linked table. Then import via INSERT INTO ... FROM query. Concatenate the path for the hyperlink fields during the import process. Then alter the table's relevant fields from simple Short Text to Hyperlink. THIS IS JUST A GUESS.
Doc_Man. I have tried a linked table but the hyperlinks don't show.
Plog. Sample attached.
CJ_London. The fields show as a hyperlink, Blue and underlined, but still no link embedded.
561414.
I see you are a Spanish speaker but could you be a bit more specific in the process.
It appears you have brought the sample into Access, by what method, and where did you go to get the information shown.
The image is too small to get any detail. There seems to be a small code snippet, unreadable, and you are in the immediate window.
I am very much the novice programmer and there is much about Access I do not know or understand.
I thank you for your time and effort.
John
I'm sorry about the lack of explanation. What you saw was me with your Excel file open on the left side of the screen and to the right you can see the VBA window with a code snippet and a few hand drawn remarks over the locals window. What I meant to show is that by inserting a module from the VBA window into your Excel file, you can declare the worksheet as a variable and inspect its contents from the locals window with a method similar to the following:
Code:
Option Explicit
Sub test()
Dim ws As Worksheet
Set ws = ActiveWorksheet
Stop
End Sub
First you declare the variable "ws" as a worksheet object, then you initialize it by setting it to the active worksheet. Without adding code to just those two lines, Excel won't do anything. But if you add a little Stop, when you run that code snippet, it will let you inspect its contents from the locals window as if you were debugging it. You could also step through it, but I prefer to use the Stop keyword because it's my style. Once there, you can expand with the plus sign the ws variable, and you can find all of the properties and members initialized with that worksheet variable. You should be interested in the Hyperlinks collection, which you can further expand to explore it and know where to get your stuff. You should be particularly interested in the TextToDisplay and the Address members of each Hyperlink. If you look closely, there are as many items as hyperlinks in your worksheet. In your case, there were 25, so you can see 25 items there. All that we have to do from Access now, is to address these with a loop and insert them into a table.
I do not know the structure of your database, but if you had a table called links, with fields link_id, linkUrl and linkTitle, you can use the following function from a MS Access module to import your excel table and its links. Further explanation in the code.
Code:
Option Compare Database
Option Explicit
Sub ImportExcelLinks()
' Declare Excel application and workbook variables
Dim excelApp As Excel.Application
Dim workbook As Excel.workbook
' Create a new instance of Excel
Set excelApp = New Excel.Application
' Set the path and name of the workbook
Dim workbookPath As String
workbookPath = CurrentProject.Path & "\Sample.xlsx"
' Open the workbook
Set workbook = excelApp.Workbooks.Open(workbookPath)
' Loop through all sheets
' With each sheet, loop the hyperlinks collection
' Then insert each hyperlink in table
Dim excelWorksheet As Excel.Worksheet
Dim item As Variant
For Each excelWorksheet In workbook.Worksheets
For Each item In excelWorksheet.Hyperlinks
CurrentDb.Execute "INSERT INTO links (linkUrl, linkTitle) " & _
"VALUES ('" & item.Address & "', '" & item.TextToDisplay & "')"
Next item
Next excelWorksheet
' Close the workbook
workbook.Close SaveChanges:=False
' Quit Excel
excelApp.Quit
' Clean up objects
Set workbook = Nothing
Set excelApp = Nothing
End Sub
Assuming Sample.xlsx is in the same path than your access database. Please test it. Please take into account that this is an early bound methodology, so you need to add a reference to Microsoft Excel Object Library in order for this code to run. If you do not want to add a reference to it, you can use a late bound method, which would make the code look a little different. If you have other questions, let me know.
561414.
Many thanks for this.
I have not had time to digest and test your info but it looks to be just what I need.
I appreciate the time you have devoted to this and will let you know the results of my endeavours.
John
561414
Your code worked perfectly.
I now have a table with the text and hyperlinks that I can join to my data tables via queries.
This then allows my users to access a huge gallery of photographic data at the click of a button.
I thank you and my users thank you.
John
