I am trying to Automatically import an excel spreadsheet into an existing Access database using VB code. I am using the docmd.transferspreadsheet method and it is working, however I need to make sure there are no duplicates when importing-either in the access database or the excel spreadsheet. Also, if the columns in excel are not in the database I want to add them to the database-Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Importing Excel Spreadsheet in Access using VB code
- Thread starter zimmerd
- Start date
MichaelRed
Programmer
To assure that a spreadsheet is imported with all of the records & fields, you need to import the spreadsheet into a NEW table. An easy way to do this is to always use a temporary table (by deleting the old one). This assures that Ms. Access will 'construct' the new table based on the spreadsheet, which 'virtually' assures you that the table has all of the information fro the spreadsheet.
The further advantage of this approach is the capability to accomplish extensive verification and validation of the data being (eventually) added to your database. You can (more-or-less) easily implement useful little checks and even some conversions to the data in the temp table, and then only copy the unuique records to the "real" table.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
The further advantage of this approach is the capability to accomplish extensive verification and validation of the data being (eventually) added to your database. You can (more-or-less) easily implement useful little checks and even some conversions to the data in the temp table, and then only copy the unuique records to the "real" table.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
The easiest way to do this is to first set up an import spec. This is done by manually importing a spreadsheet as a TEXT file. If you attempt to import a spreadsheet as an XLS you will have two problems:
1. Access and Excel will work together to decide what datatype you have in each column. A big problem if you have some text, and then some numbers, and then some text, etc.
2. Access will add a new table called 'Import_Errors' every time you import and your datatypes don't match up or there's some other error. If you have a database where you hide everything from the enduser this will eventually lead to problems if they import things often.
Also, you can't import calculated cells.
The best thing to do, in my opinion, is to save your Excel file as a .CSV file. Then, go into Access and start the import process. Do like this:
-File - Get External Data - Import
-Select the file type, a 'TEXT' file
-Navigate to and select the file you want to import
-Choose 'Delimited', cause it's a CSV file, then Click next
-Answer the question as to whether or not the first row has header fields
-If this is the first time you're importing, select 'In A New Table'
-Click next a couple times until you see the question about primary keys, select no primary key
-You should now be at the finish line, but don't click Next yet. Click the Advanced button. If you don't see the advanced button you picked the wrong file type. You'll see a list there of all the fields and the data type. To be safe, just pick 'Text' for all of them. That way you won't get any validation or data typing errors. It'll all make it in. If you see anything in 'Indexed' make sure they all say 'No'. If you want to skip any fields make sure you select the ones to skip. OK, now you're done. Click 'SAVE' and give the import spec a name. Remember that name cause you'll use it in your VBA code.
OK, go ahead and import the table. Open the table in 'Design' mode. All the fields that you imported are there. This is where you can start to restrict things. When you go through the table, choose the way you want each field to be set up. Choose the datatype. Limit the length of the field. You'll want to do this if you want to limit the type, length, and amount of data getting into your database. OK, after you're finished, save that table and then open it up again but in Data Entry mode. Delete all the records.
After you're done, go back and test your import spec. Follow the instructions above, except this time you can skip to the end and then click on Advanced and use your import spec to dictate what datatypes, primary key(s), and fields to import. The reason you went through this once before was to get your import table set up. This time through it's to test everything out. Once you're finished tweaking then import the table. You may have lost data, and you'll have to go back and figure out why. If you changed anything in the import table during the design mode, you might get validation errors if the data didn't agree with the restrictions in the import table.
Whew! Almost there! I won't write out a macro to tell you exactly how to do this, but the line of code that does all the work is this:
I have code that asks the user for the Pathname and name of the .CSV file to open. You can program it in manually and it'll work just fine. If you want dialog boxes, look for a thread with the words "dialog box" in the subject line. I'm in there somewhere.
Good luck! Let me know if you have any more questions! I hope I wasn't too confusing...
Onwards,
Q-
1. Access and Excel will work together to decide what datatype you have in each column. A big problem if you have some text, and then some numbers, and then some text, etc.
2. Access will add a new table called 'Import_Errors' every time you import and your datatypes don't match up or there's some other error. If you have a database where you hide everything from the enduser this will eventually lead to problems if they import things often.
Also, you can't import calculated cells.
The best thing to do, in my opinion, is to save your Excel file as a .CSV file. Then, go into Access and start the import process. Do like this:
-File - Get External Data - Import
-Select the file type, a 'TEXT' file
-Navigate to and select the file you want to import
-Choose 'Delimited', cause it's a CSV file, then Click next
-Answer the question as to whether or not the first row has header fields
-If this is the first time you're importing, select 'In A New Table'
-Click next a couple times until you see the question about primary keys, select no primary key
-You should now be at the finish line, but don't click Next yet. Click the Advanced button. If you don't see the advanced button you picked the wrong file type. You'll see a list there of all the fields and the data type. To be safe, just pick 'Text' for all of them. That way you won't get any validation or data typing errors. It'll all make it in. If you see anything in 'Indexed' make sure they all say 'No'. If you want to skip any fields make sure you select the ones to skip. OK, now you're done. Click 'SAVE' and give the import spec a name. Remember that name cause you'll use it in your VBA code.
OK, go ahead and import the table. Open the table in 'Design' mode. All the fields that you imported are there. This is where you can start to restrict things. When you go through the table, choose the way you want each field to be set up. Choose the datatype. Limit the length of the field. You'll want to do this if you want to limit the type, length, and amount of data getting into your database. OK, after you're finished, save that table and then open it up again but in Data Entry mode. Delete all the records.
After you're done, go back and test your import spec. Follow the instructions above, except this time you can skip to the end and then click on Advanced and use your import spec to dictate what datatypes, primary key(s), and fields to import. The reason you went through this once before was to get your import table set up. This time through it's to test everything out. Once you're finished tweaking then import the table. You may have lost data, and you'll have to go back and figure out why. If you changed anything in the import table during the design mode, you might get validation errors if the data didn't agree with the restrictions in the import table.Whew! Almost there! I won't write out a macro to tell you exactly how to do this, but the line of code that does all the work is this:
Code:
'Set up the basic strings
strImportSpec = "Your Import Specification"
strImportTable = "Your Import Table Name"
DoCmd.TransferText acImportDelim, strImportSpec, strImportTable, strPathName, TrueI have code that asks the user for the Pathname and name of the .CSV file to open. You can program it in manually and it'll work just fine. If you want dialog boxes, look for a thread with the words "dialog box" in the subject line. I'm in there somewhere.
Good luck! Let me know if you have any more questions! I hope I wasn't too confusing...
Onwards,
Q-
Thanks for the help! I can use the above example in Visual Basic 6.0? The entire front end is using VB 6.0 with access being in the back end.
I wrote this but it doesn't work:
Private Sub xcl1()
Dim oRS As New ADODB.Recordset
db_file = "c:\rad\billinganalysis.mdb"
Set con3 = New ADODB.Connection
con3.Open "provider=microsoft.jet.oledb.4.0; data source=" & db_file
Set oRS = con3.Execute("billing", , adCmdTable)
'DoCmd.OpenTable("billing", acViewDesign, acAdd) = con3
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, "billing", con3 & _
"c:\rad\billing.xls"
End Sub
I wrote this but it doesn't work:
Private Sub xcl1()
Dim oRS As New ADODB.Recordset
db_file = "c:\rad\billinganalysis.mdb"
Set con3 = New ADODB.Connection
con3.Open "provider=microsoft.jet.oledb.4.0; data source=" & db_file
Set oRS = con3.Execute("billing", , adCmdTable)
'DoCmd.OpenTable("billing", acViewDesign, acAdd) = con3
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, "billing", con3 & _
"c:\rad\billing.xls"
End Sub
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question