Results 1 to 2 of 2
  1. #1
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30

    Removing Carriage Returns when importing from Excel

    I have a problem with carriage returns when importing from excel. I have a template excel spreadsheet where individuals import data. The sheet is updated weekly and I need to import the data from this sheet weekly. I am using the following code to import from an excel file when the import button is clicked:


    Code:
     Dim filepath As Stringfilepath = "C:\Users\GrantOffice\Documents\Quizzo\Final DB Files\Quizzo Template Test.xlsx"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TestImport", filepath, True, "DataImport!B1:I41"
    The original excel file has some carriage returns in it which dont display correctly in Access. I would like to automatically replace the carriage returns with "/" when the file is imported.

    Is there an easy way to do this in the background with code?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you cannot match the data type and size by adjusting the properties in the table, maybe you can import to a temp table and then parse the data as you update your production table via the temp table.

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

Similar Threads

  1. Replies: 10
    Last Post: 12-13-2015, 07:23 AM
  2. Replies: 8
    Last Post: 06-20-2014, 11:29 AM
  3. Replies: 15
    Last Post: 08-12-2013, 07:27 PM
  4. Replace Carriage Returns with Spaces
    By chitan in forum Queries
    Replies: 1
    Last Post: 12-15-2011, 11:14 AM
  5. Removing all carriage returns from a database
    By Yesideez in forum Access
    Replies: 2
    Last Post: 06-26-2011, 09:55 AM

Tags for this Thread

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