How Can I Send Data from Excel to Access

[Skibum346]

I have two sheets in a workbook with ranges that need to be sent to existing tables in an existing database. Manually I would use the "Data, Convert To MSAccess..." menu route but how can I do this with a macro?

Thanks in advance for any help.

Kindest

Frank

 

[ChrisUK]

Frank, try this code. Please not you will need to add a reference to the MS ActiveX library 2.5 in the tools / references section. This worked fine for me

Have fun.

Sub InsertData()

Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

'The name of my ODBC connection
MyODBCConnection = "UsersDB"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

'rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the first 10 rows of sheet1
' getting data froom the first two columns
LineCount = 1

' Loop through the cells
Do While LineCount < 10
'Get values from worksheet
x = Worksheets("Sheet1").Cells(LineCount, 1).Value
y = Worksheets("Sheet1").Cells(LineCount, 2).Value

'Write to database
SQLStatement = "Insert into mytable values('" & x & "','" & y & "')"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

LineCount = LineCount + 1
Loop

Set ConnectDB = Nothing
Set rsNames = Nothing

End Sub

 

[Skibum346]

Just a quick post to thank you for your help. Before I read your post, I got an answer in another forum which I was able to make work. However, I thought you might be interested in seeing that answer for your own purposes.

http://www.erlandsendata.no/english/vba/adodao/exportado.htm

Thanks again

Frank