Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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?
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Pleased that I could help - and I should go outside and enjoy a sunny Colorado day!
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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?
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  10. #10
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry, at least I am not missing something else.

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by itconc View Post
    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?
    Regards
    John



  13. #13
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    To upload the file Zip it first.
    Are the key fields designated as Primary Keys in both tables?
    Regards
    John



  15. #15
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yes they are. Both key fields are the same.
    I am attaching the zipped file.
    Thanks.
    itconc
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •