Private Sub btnImport_Click_Click()
On Error GoTo EH
CurrentDb.Execute "delete * from [XLImportToAccess]"
Dim strName As String
Dim xlApp As Object
Dim xlWB As Object
Set xlApp = New Excel.Application
With xlApp
.Visible = False
Set xlWB = .Workbooks.Open("C:\Users\tj\Documents\Work\DB\ImportToAccess.xlsx", , False)
strName = "ToImport"
End With
Set xlWB = Nothing
Set xlApp = Nothing
DoCmd.TransferSpreadsheet acImport, , "XLImportToAccess", "C:\Users\tj\Documents\Work\DB\ImportToAccess.xlsx", True, strName & "!"
Hi Tom, thanks for the response. To answer your question:
< If I understand the problem you wouldn't want a record to be imported where the Date field is NULL.
I do want to import but the issue was that for all records, the date was NULL in the imported table and the excel records had dates in the DATE field. I think scrubbing is what I need to do. I remember that using text to columns is a way to scrub data. I am not sure about that though.
Yes, it is attached. Book1.xlsx
Since you have several cases ...the best way is to dump the auto import and work with Excel Automation via VBA
Open the Excel from Access ...read cell by cell and decide on each case what to import and "how".
It would be some extra work but at least you will have full control
Start from here : https://www.access-programmers.co.uk/forums/threads/import-specific-cell-from-excel-to-access.212031/
Tom: the tables appear to be ok. The dates are there in tables. When I import on my side, the dates are null in the table but when I press delete key on each one of those null values in the excel file and reimport, the dates import fine. If you use the control and down arrow key the cursor doesnt stop but goes all the way to the bottom and skips those cells that appear blank/null because there is invisible garbage in the cells (probably not the correct term).
I get an error message this this:
The excel data's origin is a copy and paste from an email so I imagine the data is not "clean".
John: I have to import in Access to keep the history. I can not link to it if that is what you mean.
Ok Tom...thanks for the info. I am going to keep it open a little bit and see who else chimes in. I still am under the impression that I need to scrub the data first.
You can import data via csv file. Look at sample
Special thanks to Daniel Pineault for xls2csv function
There is an error in row 2 of sample file, where date and text is mixed in one column. If it is important, you can create import specification and add it to TransferText
Hi Tom, I remember something about that text to columns trick. I tried it but didnt choose the MDY, which was a crucial part of the step. Thank you for that!
als, that was really neat. It imported clean data fine but when I replaced the clean data on the DATE column in excel with that dirty data then after import access gave me an error that it couldnt append all data but the dates imported. I didnt see an import error file either. Very nice though I just dont know why I would get that message though. thank you and special thanks to Daniel Pineault
Linking is not what I meant.
If you read the link I posted and subsequently Google the Excel Automation you will see that instead of relying on a "black box" which is the automatic import functionality of Access, you can instead "open" Excel via Automation and read each and every cell and in each case establish rules...e.g for example if the value is NULL substitute with "N.A" or similar
As I've mentioned, you can add import specification and set there all fields to text. Text fields should be imported without problems and you can later (in query) decide what to do with wrong fields.