Copy Data from Excel to Access, via Excel VBA

[t64]

Hi all,

I'm trying to copy data from Excel to Access, by using VBA in Excel. All I have in Access is a table, and I want to use Excel to copy it's data from a sheet to a table in Access. (specifying which cell goes to which tablename/columnname in access).

I see alot of examples for the opposite thing (access to excel). Any help with how to do it the other way round , Excel to Access, through Excel VBA?

Many thanks.
t.

[Bob Phillips]

Here is a simple example of adding data to an Access table

[vba]

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub
[/vba]

You will need to extend it to get your data and pass it to the routine.

[t64]

thank you.

so this is ADO? whats the difference between this and the other one (was it dao or something)?

[Bob Phillips]

DAO is an older technology. ADO is MS' (current) flagship data access layer, so they are putting all of there efforts into developing that product, none in DAO anymore.

I am sure that you could do this just as well with DAO, it is just that I use ADO and am not familiar with DAO.

[IgnBan]

El Xid, thanks for providing this example . I was looking for a simple example also. Question on your code where is the reference to the range of Excel, or better yet can you give me an example transferring a Excel Sheet (Audits) like this;
[Excel Table]
1 UNIT DATE_OF_AUDIT TIME ERROR CLEAN
2 980001 January 1, 2008 12:34 PM BROKEN YES
3 980002 January 1, 2008 12:34 PM WRONG COLOR YES
5 980004 January 1, 2008 12:37 PM WRONG COLOR NO
6 980005 January 1, 2008 12:42 PM WRONG COLOR YES
7 980006 January 2, 2008 1:02 AM COLOR THIN NO
8 980007 January 2, 2008 1:04 AM BROKEN YES
9 980008 January 2, 2008 1:09 AM BROKEN NO
Number Date Time Text Text[/]












To a Access Db, let say a Table name Audits with the same columns names.
More questions; Do you have to define the range you need to transfer? …and will you code cut and paste the data or just copies it?
...and last question; Does you code append the data of just creates a new clean table?

[Bob Phillips]

My code showed transferring values into an Access db. Those values were hard-coded in my example, but could be from variables, or ranges.

You are using SQL, so you cannot use a range per se, you pass them in series to the db.

In your example you seem to be wanting to load many items. Is this just adding new records in, is it updating where found adding where not found, replacing the whole kaboosh or what?

[IgnBan]

What I need is to transfer all day's production data. The data will need to append to the previous days in the Access table. What I need is to storage all the data in Access so later I can query it to make reports. The excel table is only to capture the data and make it available to production the current day. You may think why not just capture it and transfer it directly in to the Access table?, well I have a automatic report that is generated by the current day data that is available to production so they can see "whats going on" with their process, but at the end of the week I need to produce a weekly report, and Excel is limited on what I amount of data I can storage.
I'm learning VBA, so if you can give me an example how to transfer just couple of fields on the table I posted, I should be able to figure how to transfer all the other fields. I need to know how at the end of the day, either by closing the workbook or by pressing a button transfer the data to a Db with a Table name, lets say "Audits". I think if I can transfer the data in to one big transaccion table, then I can make queries to do my report.

Thanks El Kix for replaying.

[rangudu_2008]

Go through and try out the code in these links...

http://www.erlandsendata.no/english/...badacexportado

The following link does the opposite...

http://www.erlandsendata.no/english/...badacimportado

Ranga

[rangudu_2008]

IgnBan,
I'm able to understand what u need. If u can get me a sample of the data by uploading it as file attachments along with ur post, (that needs to be stored to the Access DB) i may work it out for u. I also may need an empty DB file that u use to store all the data. (only the table structure)

I can pick up these data if they from different excel files too...
I use ADO for all this.