Hyperlink from Excel into Access

Topic

New Reply

I have a spreadsheet with hyperlinks that I can import into Access by saving it first as a htm file. Works like a charm. Now I need to put the process into code and am failing. The transfer spreadsheet command gives an error saying that the format is incorrect.

Can anybody help?

Thanks,

itconc

Reply | Quote

Replies

Are you still changing the file type from .xls to .htm? If you do that the TransferSpreadsheet will fail – it only expects to see Excel format documents. You don’t indicate what version you are using, but if you have a recent version, you probably want to use the TransferText Method – it supports HTML document formats as well as several others. Also there is the ability to import XML data and specifications using the Application.ImportXML Method – so you could convert your Excel workbook to XML and then import it that way, but that would to seem to require a fair bit more effort.

Reply | Quote

Thanks for your answer.
We use office 2003 – no way to upgrade at this point.
I save the excel file as an htm. The import into Access works great manually.
However, I can not automate the import. I am between a rock and a hard place!

Reply | Quote

I’m not sure I understand the issue – you can automate the saving of the Excel workbook to HTMl (.htm) using Office Automation – it works fine with 2003. Then you can use the TransferText method to import the results into an Access table – check out the parameters and such in VBA help. For that matter, the XML import/export functions work just fine in Access 2003 as well. Did I miss something here?

Reply | Quote

No Sir, you are not missing anything – I am; or was until I looked at your second reply. I could not see the forest for the trees. I was using the transfer spreadsheet method instead of transfer text.
It is working very well now and I cannot thank you enough – you made my weekend! I can now go outside and enjoy Houston’n sunny warm Sunday.
Thanks again,

itconc

Reply | Quote

Pleased that I could help – and I should go outside and enjoy a sunny Colorado day!

Reply | Quote

Well, I must have been out in the sun too long in Houston.
I now have a new problem. The hyperlink comes through just fine. However, the abstract field (set to memo in Access) gets truncated and words run into each other. I can see the problem during the manual import as I scroll from one column to the other.

Is there anything I can do to fix this?

Thanks,

itconc

Reply | Quote

Unfortunately, the TransferText method does not behave well with imports into memo fields – and I suspect the conversion to HTML is further confusing the issue. If you do a manual import directly from Excel, does the abstract field get truncated?

Reply | Quote

yes it does. I am going to try 2 imports – one spreadsheet for the memo filed, one htm for the hyperlink. I can recombine the files in Access.
I hope this will work.
Thanks for your answer – at least I am missing something else.

itconc

Reply | Quote

Sorry, at least I am not missing something else.

Reply | Quote

Wendell, I have been trying to work around the problem of the html import by doing 2 different imports:
1 straight forward Excel, another a html/ text import for the hyperlink.
I was hoping to be able to link the 2 tables after the import and grab the hyperlink field.
Nothing I have done works – both tables have the same key field. However, they will not link. I have checked field types and sizes – all is correct. Nothing works.
Do you have any idea what I could do here?

Thanks,

itconc

Reply | Quote

Nothing I have done works – both tables have the same key field. However, they will not link. I have checked field types and sizes – all is correct. Nothing works.

What does happen? Do you get an error message?
How are you trying to link them?

Reply | Quote

no error messages. I link in the relationship window and cannot set a one to one relationship with referential integrity on. When I link in a query I cannot get records from both tables.
I have tried to upload a file with only the two tables but am getting an error message that I am not permitted to upload this kind of file. I tried the mdb and tried chaing the file extension – no luck.

Any help is appreciated.

itconc

Reply | Quote

To upload the file Zip it first.
Are the key fields designated as Primary Keys in both tables?

Reply | Quote

yes they are. Both key fields are the same.
I am attaching the zipped file.
Thanks.
itconc

Reply | Quote

One table has 24 records and the other has 25. So when you try to enforce referential integrity you get a message that says:

There is one record in tblForFullText that does not have a matching record in casdata.

Reply | Quote

I apologize. When I set up the test database, I grabbed the wrong table. I have deleted the nonmatching record. There is still no match, no ability to link. When I ran an unmatched query, none of the records matched. I then went in and copied the primary key value for 1 record from one table to the other. That 1 record showed a match.
Is it possible that the import does something to the fields? Wendell had said the the text import does not behave very well.
I am attching the revised test database.
Thanks,

itconc

Reply | Quote

The two fields are clearly not equal except in the one case where you cut and pasted. My guess (strictly that at the moment) is that the conversion to HTML is putting some funny characters into that field – but they are non printing. It will take a binary editor to verify that. I would suggest that you put a numeric sort field into the Excel workbook and pull that in as your key to each table, and then join on that. But it really appears to me that you should be able to do an import from the spreadsheet directly and then change the field type to hyperlink after you have imported it. Is the issue with doing that because you would need to program that in order to automate the process?

Reply | Quote

I cannot import the file as an excel spreadsheet without losing the hyperlink. My users are most adamant about the hyperlink. If absolutely necessary, I can live with bad title and abstract but not without the hyperlink.
I will try your suggestion with the numeric field and see what happens.

Thanks,

IT

Reply | Quote

I have added the numeric identifiers in Excel and done 2 imports. Now all is well – the links worked, the data is clean!! Hyperlinks are OK and the title and Abstract are readable.

Thank you so much – both of you for helping me with this.

itconc

Reply | Quote