Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating Access table with Excel data
Hello,
I'm new to office soln dev so bear with me. How do I take data on an Excel sheet and update an access table with it? I have an empty access table and I need to get info from Excel into it. How do I go about this? Some bulk operation would be the best because I can imagine that going line by line would take too long. Thanks a lot in advance for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating Access table with Excel data
You can start here - this should give you some ideas:
http://www.erlandsendata.no/english/vba/adodao/ Regards, Tom Ogilvy "newbie" wrote in message ... Hello, I'm new to office soln dev so bear with me. How do I take data on an Excel sheet and update an access table with it? I have an empty access table and I need to get info from Excel into it. How do I go about this? Some bulk operation would be the best because I can imagine that going line by line would take too long. Thanks a lot in advance for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating Access table with Excel data
Hi there try this:
Sub CopyDatatoAccess Dim Range1 As Range Dim Array1 As Variant Dim x As Variant Dim Db1 As Dao.Database Dim Rs1 As Dao.Recordset 'First, open the database. Chane the Path Name and the Database Name for yours Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\sampdata.mdb") 'Then, open the recordset. Again Change the name of the table for yours Set Rs1 = Db1.OpenRecordset("distributors", dbOpenDynaset) 'Then, determine the size of the worksheet range. Set Range1 = Worksheets("Sheet1").Range("A1").CurrentRegion.Off set(1, 0) Set Range1 = Range1.Resize(Range1.Rows.Count - 1, Range1.Columns.Count) Range1.Select 'Read the worksheet range into an array. Array1 = Range1.Value 'Then write the data from the array to the recordset. 'Note that for each new record, you must first call Addnew 'then set the value property of the fields, and then call Update. For x = 1 To UBound(Array1, 1) With Rs1 .AddNew .Fields("Field1") = Array1(x, 1) .Fields("Field2") = Array1(x, 2) .Fields("Field3") = Array1(x, 3) .Fields("Field4") = Array1(x, 4) .Fields("Field5") = Array1(x, 5) .Fields("Field6") = Array1(x, 6) .Fields("Field7") = Array1(x, 7) .Fields("Field8") = Array1(x, 8) .Fields("Field9") = Array1(x, 9) .Update End With Next 'Keep adding the fields depending on how many Colunms you may have in excel 'Close the database. Db1.Close End Sub "Tom Ogilvy" wrote in message ... You can start here - this should give you some ideas: http://www.erlandsendata.no/english/vba/adodao/ Regards, Tom Ogilvy "newbie" wrote in message ... Hello, I'm new to office soln dev so bear with me. How do I take data on an Excel sheet and update an access table with it? I have an empty access table and I need to get info from Excel into it. How do I go about this? Some bulk operation would be the best because I can imagine that going line by line would take too long. Thanks a lot in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating data from Access | Excel Discussion (Misc queries) | |||
Updating data with an ODBC link to ACCESS | Excel Worksheet Functions | |||
Data table is not updating correctly in excel 2003 | Excel Discussion (Misc queries) | |||
Data from Excel to Access Table | Excel Discussion (Misc queries) | |||
New Excel data does not appear in linked Access table | Excel Discussion (Misc queries) |