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?