Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Upload from Excel to SQL Server
Try this
First, change your insert query into a Stored Procedure in SQL Server, using parameters I GUESSED AT DATA TYPES FOR THE PARAMETERS. Make them the same as the table's field/column types create procedure dbo.usp_Insert_Upload_Specific @Loc vchar(5), @PType vchar(5), @Quant integer, @PName vchar(25), @Style vchar(5), @Features vchar(25) AS INSERT INTO Upload_Specific ( Location, [Product Type], Quantity, [Product Name], Style, Features ) VALUES ( @Loc, @PType, @Quant, @PName, @Style, @Features, ) GO Now your code can be a little easier Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" ' NEW CODE HERE Dim intParams As Integer Dim objCmd As New ADODB.Command ' Connect the Command object to the data source. objCmd.ActiveConnection = objConn ' Set CommandText equal to the stored procedure name. objCmd.CommandText = "dbo.usp_Insert_Upload_Specific" objCmd.CommandType = adCmdStoredProc ' Automatically fill in parameter info from stored procedure. objCmd.Parameters.Refresh ' Get the count of required parameters SHOULD BE 6 intParams = objCmd.Parameters.Count - 1 'first one is RETURN value DIM rng as Range DIM ccell as Range set rng = range("A2:A20") ' change this range to include all your data For Each cc in rng ' call the stored procedure for x = 1 to intParams objCmd(x) = ccell.offset(0,x-1) next x ' now that all the parameters have been assigned values ' execute the query objCmd.execute next ccell 'close the connection oConn.Close Set oConn = Nothing End Sub "Doctorjones_md" wrote: I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet -- how would I modify the code to upload all rows, or iterate on each row having data? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. |
#2
Posted to microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Upload from Excel to SQL Server
Duke,
Thank you for the reply and recommendation -- Stored Procedures aren't my forte, so I'm going to give your suggestion a try -- it's Friday, maybe I'll get lucky! I'll let you know how it works for me -- thanks again, "Duke Carey" wrote in message ... Try this First, change your insert query into a Stored Procedure in SQL Server, using parameters I GUESSED AT DATA TYPES FOR THE PARAMETERS. Make them the same as the table's field/column types create procedure dbo.usp_Insert_Upload_Specific @Loc vchar(5), @PType vchar(5), @Quant integer, @PName vchar(25), @Style vchar(5), @Features vchar(25) AS INSERT INTO Upload_Specific ( Location, [Product Type], Quantity, [Product Name], Style, Features ) VALUES ( @Loc, @PType, @Quant, @PName, @Style, @Features, ) GO Now your code can be a little easier Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" ' NEW CODE HERE Dim intParams As Integer Dim objCmd As New ADODB.Command ' Connect the Command object to the data source. objCmd.ActiveConnection = objConn ' Set CommandText equal to the stored procedure name. objCmd.CommandText = "dbo.usp_Insert_Upload_Specific" objCmd.CommandType = adCmdStoredProc ' Automatically fill in parameter info from stored procedure. objCmd.Parameters.Refresh ' Get the count of required parameters SHOULD BE 6 intParams = objCmd.Parameters.Count - 1 'first one is RETURN value DIM rng as Range DIM ccell as Range set rng = range("A2:A20") ' change this range to include all your data For Each cc in rng ' call the stored procedure for x = 1 to intParams objCmd(x) = ccell.offset(0,x-1) next x ' now that all the parameters have been assigned values ' execute the query objCmd.execute next ccell 'close the connection oConn.Close Set oConn = Nothing End Sub "Doctorjones_md" wrote: I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet -- how would I modify the code to upload all rows, or iterate on each row having data? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. |
#3
Posted to microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Upload from Excel to SQL Server
Duke,
Looking at your code -- did you mean to type "ccell" vice "cc" in the following (4th line): DIM rng as Range DIM ccell as Range set rng = range("A2:A20") ' change this range to include all your data For Each cc in rng ' call the stored procedure =================== When I run the code, I get the following error: "Runtime error '3001' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. Any thoughts on what could be causing this? Could it be a conflict in data types that's causing this? "Duke Carey" wrote in message ... Try this First, change your insert query into a Stored Procedure in SQL Server, using parameters I GUESSED AT DATA TYPES FOR THE PARAMETERS. Make them the same as the table's field/column types create procedure dbo.usp_Insert_Upload_Specific @Loc vchar(5), @PType vchar(5), @Quant integer, @PName vchar(25), @Style vchar(5), @Features vchar(25) AS INSERT INTO Upload_Specific ( Location, [Product Type], Quantity, [Product Name], Style, Features ) VALUES ( @Loc, @PType, @Quant, @PName, @Style, @Features, ) GO Now your code can be a little easier Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" ' NEW CODE HERE Dim intParams As Integer Dim objCmd As New ADODB.Command ' Connect the Command object to the data source. objCmd.ActiveConnection = objConn ' Set CommandText equal to the stored procedure name. objCmd.CommandText = "dbo.usp_Insert_Upload_Specific" objCmd.CommandType = adCmdStoredProc ' Automatically fill in parameter info from stored procedure. objCmd.Parameters.Refresh ' Get the count of required parameters SHOULD BE 6 intParams = objCmd.Parameters.Count - 1 'first one is RETURN value DIM rng as Range DIM ccell as Range set rng = range("A2:A20") ' change this range to include all your data For Each cc in rng ' call the stored procedure for x = 1 to intParams objCmd(x) = ccell.offset(0,x-1) next x ' now that all the parameters have been assigned values ' execute the query objCmd.execute next ccell 'close the connection oConn.Close Set oConn = Nothing End Sub "Doctorjones_md" wrote: I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet -- how would I modify the code to upload all rows, or iterate on each row having data? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. |
#4
Posted to microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Upload from Excel to SQL Server
I reposted this because I was unable to achieve desired results from
previous recommendations. Previous Post: =================== I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet. My thought is that I might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on each row having data (those not deleted by the DeleteBlankRows procedure)? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. "Duke Carey" wrote in message ... Try this First, change your insert query into a Stored Procedure in SQL Server, using parameters I GUESSED AT DATA TYPES FOR THE PARAMETERS. Make them the same as the table's field/column types create procedure dbo.usp_Insert_Upload_Specific @Loc vchar(5), @PType vchar(5), @Quant integer, @PName vchar(25), @Style vchar(5), @Features vchar(25) AS INSERT INTO Upload_Specific ( Location, [Product Type], Quantity, [Product Name], Style, Features ) VALUES ( @Loc, @PType, @Quant, @PName, @Style, @Features, ) GO Now your code can be a little easier Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" ' NEW CODE HERE Dim intParams As Integer Dim objCmd As New ADODB.Command ' Connect the Command object to the data source. objCmd.ActiveConnection = objConn ' Set CommandText equal to the stored procedure name. objCmd.CommandText = "dbo.usp_Insert_Upload_Specific" objCmd.CommandType = adCmdStoredProc ' Automatically fill in parameter info from stored procedure. objCmd.Parameters.Refresh ' Get the count of required parameters SHOULD BE 6 intParams = objCmd.Parameters.Count - 1 'first one is RETURN value DIM rng as Range DIM ccell as Range set rng = range("A2:A20") ' change this range to include all your data For Each cc in rng ' call the stored procedure for x = 1 to intParams objCmd(x) = ccell.offset(0,x-1) next x ' now that all the parameters have been assigned values ' execute the query objCmd.execute next ccell 'close the connection oConn.Close Set oConn = Nothing End Sub "Doctorjones_md" wrote: I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet -- how would I modify the code to upload all rows, or iterate on each row having data? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update data in Excel | New Users to Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to Transfer data from Excel to SQL SERVER 2000 | Excel Discussion (Misc queries) | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) |