Results 1 to 7 of 7

Data import from Excel spreadsheet stopped working, now requiring open & close in Excel first.

  1. #1
    tryingjohn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2019
    Posts
    4

    Data import from Excel spreadsheet stopped working, now requiring open & close in Excel first.

    I have an Access database that imports data from an xls spreadsheet using the DoCmd.TransferSpreadsheet Method. The spreadsheet is downloaded with updated data at various times from an online service provider, using their software. Importing the data from the spreadsheet as received did work, but since late January this year it has not worked, giving the Error 3274 message "External table is not in the expected format". However, if the spreadsheet is opened then closed in Excel, without saving or doing any other operation, it will import successfully. The online service provider says nothing has changed at their end. What would have caused the change and how can the problem be fixed?



    The whole import process must be as simple as possible for use by a busy office secretary/assistant with limited computer skills. One click on a command button should be all the operator need do.

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,433
    not sure I can help but have you changed anything at your end? operating system? office?

    .xls is very old, but still valid, format - have you unintentionally moved to .xlsx?

    finally how are you importing the data - what code are you using?

  3. #3
    tryingjohn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2019
    Posts
    4
    Nothing has changed at my end, apart from automatic updates to Windows and Office/Access. I originally chose .xls because Excel 2000 was the latest version mentioned in the Access help file. However, since adding that import I have added elsewhere in the same database import of a different spreadsheet of .xlsx format using the same DoCmd.TransferSpreadsheet method with no problems (the only differences being the spreadsheet imported and the table into which it is imported).

    The Access visual basic code line I am using is:
    DoCmd.TransferSpreadsheet acImport, 8, "tblAddressesImp", "C:\SpreadsheetFolder\Valid.xls", True

    As mentioned earlier, it initially worked, but since January this year has not.

  4. #4
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    As a workaround why don't you try to do just that in your code before importing it:

    Code:
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
      Set appExcel = CreateObject("Excel.Application")
      appExcel.Visible = False
      
      Set myWorkbook= appExcel.Workbooks.Open("C:\SpreadsheetFolder\Valid.xls")
      myWorkbook.Save 
      myWorkbook.Close
      appExcel.Quit
      Set myWorkbook = Nothing
      Set appExcel = Nothing
     'now import it
    DoCmd.TransferSpreadsheet acImport, 8, "tblAddressesImp", "C:\SpreadsheetFolder\Valid.xls", True   
    Cheers,
    Vlad

  5. #5
    tryingjohn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2019
    Posts
    4
    Thanks Vlad, for showing me how to do that. I was thinking about looking into that approach.

    When I tried your suggested code, there was a “Compile error: User-defined type not defined” referring to “Dim appExcel As Excel.Application” and “Dim myWorkbook As Excel.Workbook”. I tried changing each of “Excel.Applcation” and “Excel.Workbook” to “Object” and it did open and close the spreadsheet. In turn, it achieved the objective of enabling a single command button click to import the data.

    Although a workaround rather than a direct fix, it did solve the problem. Time will tell if, unlike the original approach, it continues to work. Reading various forum posts indicates downloaded Excel spreadsheets often have format problems. But I am curious that in my case initially there were no problems; they started later for no apparent reason.

  6. #6
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    Glad to hear you got it working, actually better to dim them as object (late binding) as you did so they are not dependent on the Excel version installed. Something must have changed in your Excel source, sometimes the changes are not obvious to the eye. Mainly an empty used range can cause problems. And if the file itself is not created by Excel itself (which I believe is the case for you) then the exporting application might have had updates, driver updates, etc. that all could impact the outcome.

    Cheers,
    Vlad

  7. #7
    tryingjohn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2019
    Posts
    4
    Thanks, Vlad

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. Replies: 6
    Last Post: 06-07-2018, 02:47 PM
  3. Replies: 7
    Last Post: 10-24-2016, 01:55 AM
  4. Replies: 2
    Last Post: 04-20-2016, 05:34 PM
  5. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM

Posting Permissions

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