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

    Can't import spreadsheet unless open / save in Excel first

    Hi,



    Trying to import an xlsx spreadsheet of data downloaded from a website. Because it is downloaded I have no control on its format (Well, xlsx or pdf), though can open in Excel no worries.

    However if I try and import to Access using the command

    DoCmd.TransferSpreadsheet acImport, , "tbltmpCImport", "c:\MyImportSpreadsheet.xlsx", True

    I get the error "Run-time error '3274': External table is not in the expected format". I have tried all variants of SpreadsheetType.

    Yet if I open and save the file in Excel then everything works.

    Browsing suggests this can be an issue, and various solutions of automating opening / closing the spreadsheet offered (One within this forum here). However many PC's that use my db don't have Excel installed, and I only use Access Runtime on them. So I am limited on scope for a solution.

    What started out as a productive day programming has finished in frustration on one single line of code...

    Any assistance appreciated. I can post the spreadsheet if it helps.


    Thanks in advance.

    swas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access is confused as to the type of sheet style. Just because it has .xlsx, doesnt mean thats the format.
    I think the best method would be to select the file in access,click an import button,
    the button would run vb to open excel,
    load the file, 'Save As' back into a known format,
    then import that file.



    Code:
    '============
    Sub ImportXLbook()
    '============
    Dim xl As Excel.Application
    CONST kFILE2IMPORT = "c:\temp\Import.xls"
    
    'you MUST put Excel in the program REFERENCES, in VBE menu (Ctl-G): TOOLS , REFERENCES
    'checkmark the 'Microsoft Excel X.xx Object library'
    
    
    Set xl = CreateObject("excel.application")
    With xl
        .Workbooks.Open "c:\MyImportSpreadsheet.xlsx"
        .ActiveWorkbook.SaveAs kFILE2IMPORT, 56
        .Quit
    End With
    
    DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel8 , "tbltmpCImport", kFILE2IMPORT , True
    
    Set xl = Nothing
    End Sub

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Providing file might be useful. Or the site to download from.

    Now I see ranman's suggestion. Looks good.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

    Ranman256 - As noted many installations don't have Excel, which I would assume removes the ability to reference any Excel library?

    June7 - File is attached. ProblemImport.zip

    Help / thoughts appreciated.

    swas

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If user does not have Excel then cannot open the file by any method to fix. Sorry, I don't see a solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    What PC out there doesn't have Excel on board? I mean...can MS Access even work, if MS Office isn't loaded?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    swas is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2017
    Posts
    32
    More to the point, why Excel works and yet an official MS Office product (Or at least part there of) doesn't. As mentioned the issue has been around for a long time.

    I'm going to have to get creative.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-07-2018, 02:47 PM
  2. Replies: 7
    Last Post: 10-24-2016, 01:55 AM
  3. Import From Excel Spreadsheet on Sharepoint Site.
    By Robeen in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2016, 09:55 AM
  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