- Jan 10, 2006
I've written code to upload data into SQL from Excel, but it is row-by-row iterative and takes forever when there are many rows, so I started looking for a way to do a range in one pass. The code below was found from a very old post on another site and it looked like something I could manage. After some massaging, I thought I had it, but I'm getting an "Invalid object name 'TempRange'" error on the .Execute line. Any thoughts on what I'm doing wrong with this range?
Sub UpdateTable3() Dim cnn As Object Dim wbkOpen As Workbook Dim objfl As Variant Dim rngName As Range Workbooks.Open "X:\MyPath\MyExcelFile.xlsm" Set wbkOpen = ActiveWorkbook Sheets("Data").Select Set rngName = Range("A2:E10") 'range defined here rngName.Name = "TempRange" 'TempRange defined here strFileName = wbkOpen.FullName Set cnn = New ADODB.Connection cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYDB" cnn.Open nSQL = "INSERT INTO Workarea.[ad hoc]" nJOIN = " SELECT * from [TempRange]" 'TempRange used in SQL statement cnn.Execute nSQL & nJOIN '<----- ERROR WHILE EXECUTING THIS LINE MsgBox "Uploaded Successfully" wbkOpen.Close Set wbkOpen = Nothing End Sub