Excel to Table in SQL Database

[mpadgett]

Does anyone have any quick, down-and-dirty instructions on copying some data from Excel to a Table in an SQL Database thru VBA.
I'll need to access the table, create a record and insert some data into specific fields.
Thanks!

 

[abaldwin]

I think this may do it for you but am a little unclear on what you really want

Dim iCounter as integer
dim stsql as string

'Do your ado connection object and open it here.

Do while cells(icounter,1).value <> ""
'Assumes that data is always in the first column and will stop when it finds a blank row

construct your sql statement from each row.
stsql = INsert into mytable (PartNo, Price) VALUES ('" & cells(icounter,1).value & "', '" & cells(icounter,2).value & "');"
connection.execute stsql
icounter = icounter + 1
loop

Basicall loop through each line and construct a sql insert statement from the cells in the current row.






Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.