Results 1 to 4 of 4
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    TransferSpreadsheet Error capture (ImportErrors table export issue)

    I have a Access database that loads data from an Excel file using TransferSpreadsheet the routine works fine unless the spread has bad data (date entered as 12/1/202) in which case Access creates a import error table named 'BOM Standard$'_ImportErrors At the end of the load routine, i check to see if there is a ImportErrors table and if there is, i stop the process. I want to export the 'BOM Standard$'_ImportErrors to give the user an idea where to look in the source data file (9k rows) However, the



    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "'BOM Standard$'_ImportErrors", outputFileName, True

    errors because of the ' in the table name that Access creates.

    Any thoughts on how to get around this issue. i happens if i find the table and use table.name as well.

    THANKS

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why not use Docmd.Rename to remove the $' then export that :
    DoCmd.Rename "BOM_Standard_ImportErrors", acTable, "BOM Standard$'_ImportErrors"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "'BOM_Standard_ImportErrors", outputFileName, True

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of
    DoCmd.TransferSpreadsheet ,

    Save new excel file the same file everytime. (make a macro to do it) like c:\temp\File2Import.xls
    LINK this file as an attached linked table in access.

    build your 'import' as an append query(ies) to import the data to the various tables

    build a data verification (select) query (ies), to run check on fields that could be wrong.
    select * where not IsDate([dateFld])

    if it has records stop.
    if Dcount("*","qsInValidDate")>0 then
    docmd.openquery "qsInvalidDate"
    end
    endif

    put in macro:
    now the import process is just 2 steps:

    1. Get a new excel file, save it to the c:\temp\File2Import.xls
    2. run the import macro.
    done


    if no errors the final append query imports.
    I actually had a table of the queries to inspect the import. if any had records, I stopped the import.

  4. #4
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    thanks, sometimes i just get to wrapped in the weeds to see the easy fix (rename)

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

Similar Threads

  1. Issue Importing with TransferSpreadsheet
    By Kluaoha in forum Programming
    Replies: 10
    Last Post: 06-07-2018, 07:54 PM
  2. TransferSpreadsheet issue
    By Xarkath in forum Programming
    Replies: 6
    Last Post: 12-03-2015, 01:35 PM
  3. Replies: 12
    Last Post: 09-13-2012, 12:07 PM
  4. Capture Form Data to Export to Excel
    By ajones92 in forum Forms
    Replies: 8
    Last Post: 06-16-2011, 03:35 PM
  5. Replies: 2
    Last Post: 10-12-2010, 02:10 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