Results 1 to 4 of 4
  1. #1
    swas is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2017
    Posts
    32

    Import Excel spreadsheet failing

    Hello,

    I have to repeatedly download and import an xlsx spreadsheet to an Access DB from a website that is failing using docmd.transferspreadsheet.

    It's because of a known issue, with the downloaded Excel file not being fully compliant which seems to be typical of many websites.

    Simply opening / saving the file in Excel solves the problem; I can then import ok.

    Apart from being a clunky solution, my problem is our Access DB isn't always on a PC with Excel installed.

    I can use online services which convert an xlsx file to a csv and import, but this too seems like a clunky solution.

    And no luck on getting the website to create a correct xlsx...

    Are there any known solutions here? Surely an Office product like Access should be able to open a file that Excel can?

    Like I say, a known problem and several workarounds about - most requiring Excel though. Another is changing the xlxs extension to htm, but doesn't work for me.


    Are there any other ideas?


    Thanks in advance.

    mrswas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you seem to be running out of options,
    the website XL file is not standard,
    you cant correct the XL on the PC since it doesnt have XL,

    it seems your only option is use a PC that does have XL and correct the file.
    Can some person WITH XL correct the file then email it to the Access person?
    (what PC in todays world does not have XL?)

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have you tried Access automation to open the web site Excel file and save it?
    Maybe you could use Access automation to open the web site Excel file and import it without saving it.


    Excel Automation: https://www.microsoftaccessexpert.co...utomation.aspx
    Automation 101: http://access-experts.com/default.as...tomation&sm=18
    limited viewing - must create an account: https://www.experts-exchange.com/art...ss-Part-I.html

    Database Automation Using VBA: http://www.ucb-access.org/Files/AdvA...urseReader.pdf

    Then there is Ken Snell's site: http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

  4. #4
    swas is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2017
    Posts
    32
    Thanks for the thoughts.

    I'm not so sure all PC's have Excel these days, with OpenOffice / Google Sheets etc... viable alternatives for small businesses not requiring big time features. Regardless, it isn't within the scope of my question.

    Automation is great - but again only if Excel is installed to automate... CreateObject("Excel.Application") without Excel installed won't get far. Again, outside the scope of my question.

    Excel installed, no problems. Even better - the Access DoCmd.TransferSpreadsheet method to have the same opening / reading functionality as Excel does, since they are both part of the Office environment.

    Like I say, there are online tools that accept the improper format and convert to CSV format, so I was perhaps hoping for a simple command line level function that achieves the same result from within local VBA code.

    I do appreciate the thoughts though.

    swas

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-01-2018, 04:40 PM
  2. Import From Excel Spreadsheet on Sharepoint Site.
    By Robeen in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2016, 09:55 AM
  3. Excel Import - Check if Spreadsheet Exists
    By kagoodwin13 in forum Programming
    Replies: 3
    Last Post: 11-21-2013, 02:38 PM
  4. How to import excel spreadsheet into an Access subform
    By upfish in forum Import/Export Data
    Replies: 1
    Last Post: 10-03-2012, 01:53 PM
  5. import excel spreadsheet though outlook
    By bopsgtir in forum Import/Export Data
    Replies: 0
    Last Post: 03-18-2011, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums