Results 1 to 8 of 8
  1. #1
    jaeapla is offline Novice
    Windows 11 Office 365
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12

    Problem with Importing data from Excel into Access table using DoCmd.TransferSpreadsheet


    I finally semi-solved this and hope it will help someone else.
    I use DoCmd.TransferSpreadsheet to import a file into an Access table from Excel, and it was working fine until several weeks ago. All my parameters were working perfectly, and then all of a sudden, when it would tell me how many records it was going to import, it was a ridiculously large number. I finally figured out that if I put in the sheet name as well as a data range, it worked perfectly (e.g., Sheet1!A1:G119).

    Before, I just gave the file name without that parameter. The only thing I changed (and this must somehow be the cause) was to get rid of every time I opened an excel spreadsheet, a popup could come up stating: Personal.XLSB is Locked for Editing Open Read-Only or click Notify... -- the only way I could find to get rid of it was to go to View in File Explorer and change view to Detail Only with no preview pane.

    I would like to find a way to not have to explicitly state the sheet name and range as this will vary with every import.

    Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    My File Explorer is set to allow preview and I do not have issue importing from Excel.

    I have never used xlsb file.
    Last edited by June7; 05-11-2023 at 07:44 PM.
    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.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps something has changed in the .xlsb?. And presumably is still only a single worksheet? To test, retrieve one of your earlier files that imported successfully and try it again (use a copy of access file for testing)

    .xlsb's are usually only of benefit for very large datasets to save space and for faster opening/saving times - and 120 rows is not large.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Personal.XLSB is just a workbook that holds all macroes available to any other workbook.
    I get that message some times, but just leave it as read only, unless I want to edit a macro in it?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Personal.XLSB is just a workbook that holds all macroes available to any other workbook.
    never knew that

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by CJ_London View Post
    never knew that
    Yes, very handy. :-)

    Needs to be in a certain location.
    C:\Users\Paul\AppData\Roaming\Microsoft\Excel\XLST ART
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    jaeapla is offline Novice
    Windows 11 Office 365
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12
    Quote Originally Posted by Welshgasman View Post
    Yes, very handy. :-)

    Needs to be in a certain location.
    C:\Users\Paul\AppData\Roaming\Microsoft\Excel\XLST ART
    Thanks for your answers - it seems to be solved by eliminating the Preview Pane

  8. #8
    jaeapla is offline Novice
    Windows 11 Office 365
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12
    Quote Originally Posted by CJ_London View Post
    never knew that
    Thanks for your help - it seems to be solved by eliminating the Preview Pane

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

Similar Threads

  1. Replies: 2
    Last Post: 07-31-2021, 06:35 PM
  2. DOcmd.Transferspreadsheet with a Table name
    By mcucino in forum Programming
    Replies: 1
    Last Post: 08-01-2018, 01:20 PM
  3. Replies: 11
    Last Post: 03-02-2016, 11:01 AM
  4. Replies: 12
    Last Post: 10-08-2014, 02:35 PM
  5. Importing Excel file: ADODB, DAO or DoCmd.TransferSpreadsheet
    By Monterey_Manzer in forum Import/Export Data
    Replies: 3
    Last Post: 08-13-2013, 11:34 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