Member Avatar for violette

hi all. here's my problem;. I would like to have a macro to export data from an excel file into the database(SQL) for insert and update data purposes. but i'm not sure how to do this.Can someone please show me a code syntax on how to do it?the excel file name is FISTdb.xls and the table i want to get it updated is company_info.how can i ensure the data update the right firld in the database? thanks all. :) ur help is much appreciated.

*i'm not sure where to post this. mod, please move the thread if necessary. thanks.

  • 2 Contributors
  • 3 Replies
  • 175 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k
Member Avatar for adam_k

From that link (where a guy asks pretty much what you are asking) I'm copying :

You need to loop through the rows and execute the insert statement for each row of data 1 at a time so something more like:

dim arrUpload as variant
dim c as range
For each c in Range("UploadRange").columns(1)
 arrUpload = range(cells(c.row,1),cells(c.row,range("Uploadrange").columns.count))
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={SQL Server};Server=PLPST1209;User ID=abc;Password=abc123;Database=Customer_Info"
strSQL = "Insert into Customer_Info Select " & arrUpload
cn.Execute (strSQL)
set cn = nothing
Next

Rgds, Geoff

And later corrects that code with:

For each c in Range("UploadRange").columns(1)
 for each rc in Range("UploadRange").rows(c.row)
   strArr = rc.Value & "," 
next    
strArr = left(strArr,Len(strArr)-1)  
strSQL = "Insert into Customer_Info (Field1, Field2, Field3, etc) Select " & strArr

as there were no commas between the values. Still you might want to change this to include single quotes around fields for this to handle strings.

Edited by adam_k because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.