Getting records from EXCEL for ACCESS

[m2b79]

I must create a database, using informations from an EXCEL page. The problem is that I can't use the Wizard, because some fields are wrongly filled. So I want to know if it is possible to extract the records line by line from EXCEL, so that I can check out every line.<br>At the moment, I use ACCESS, EXCEL and Visual Basic for this project.<br><br>Thank you very much for your help.

 

[DougP]

When you say some fields are wrongly filled. Do you mean an entire field like &quot;Lastname&quot; is wrong all the way down or some of the records in &quot;Lastname&quot; are wrong and that some are right? <br>Then is this a one time shot or do you need to do it on an on going basis, Bring records from Excel?<br>It will depend on whether you save and keep track of what you are going to do below or delete it when you are done.<br>Here is what I would do.<br>Import all the records using the normal Import.<br>Then use the power of Access by creating queries that return either good records or bad. so you can weed thru what fields or records you don't need.<br>Make notes as to what you are doing. Save your queries as you go.<br>If its on going, Like evry week or day.<br>Then create an Import function that would be called from somewhere in Access like a form or an AutoExec macro.<br>You can use the SQL form your queries you made to narrow down your records and or fields. This would be added to your function like so.<br><br>Dim db as database, rst as recordset, SQL as string<br>set db = currentdb<br>SQL = &quot;Select DistinctRow Something From SomeTable Where ....<br>Set rst = db.openrecordset(SQL)<br>etc<br><br>OK<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[m2b79]

The problem is that when I import the records, ACCESS also creates a table called <br><b>Feuil1$_ImportErrors</b>, with the number of the lines that are wrong.<br>The only thing I can do is changing the records in the EXCEL file, but this is not very user-friendly...<br><br>The wrong recordings come from a change in the data format.<br>For example, instead of a number, someone wrote the name of a town.

 

[Cornerstone]

First create a table to handle the import. Make all of the fields text so they can accept anything imported into them.&nbsp;&nbsp;Then run code similar to the following. This assumes Excel 2000. If Excel 97 then change Excel9 to Excel8.&nbsp;&nbsp;I put True below assuming your spreadsheet does not have fieldnames.<br><br>DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, &quot;yourtablename&quot;, &quot;yourfilename&quot;, False, &quot;yourrange&quot;<br><br>Then once the info is all imported you can weed things out.<br>Hope that helps.<br>

 

[elizabeth]

Or you could just &quot;fix&quot; the records in the ImportErors table and then append them to the table with the correctly-imported records.

 

[Cornerstone]

In my above comment I meant to say I put FALSE assuming no fieldnames in your spreadsheet.