Excel 中的 VBA 宏来运行 SQL 插入语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8987517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA macro in Excel to Run SQL Insert statement
提问by user1167046
Hey im quite new to VBA and I was hoping someone could help me with last bit of code.
嘿，我对 VBA 很陌生，我希望有人能帮助我完成最后一点代码。
I am trying to take cells from a spreadsheet and add them to a SQL table but I am having trubble running the SQL statement. Here is the code I have so far.
我正在尝试从电子表格中获取单元格并将它们添加到 SQL 表中，但是我在运行 SQL 语句时遇到了麻烦。这是我到目前为止的代码。
Private Sub ConnectDB()
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "DRIVER={SQL Server};" & _
"SERVER=SERVER02;" & _
"DATABASE=platform;" & _
"USER=5y5t3mus3r;" & _
"PASSWORD=*******;" & _
"Option=3;"
If oConn.State = adStateOpen Then
MsgBox "Welcome to Database!"
Else
MsgBox "Sorry No Database Access."
End If
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim Company As String
Dim Address As String
Dim Address1 As String
Dim Address2 As String
Dim County As String
Dim Contact As String
Dim Phone As String
Dim Fax As String
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Sheets("wsheet").Activate
Set rs = New ADODB.Recordset
rs.Source = Sql
With wsheet
MyFile = "C:\Users\Ryan.ICS\Documents\Documents\InsertStatement.txt"
fnum = FreeFile()
Open MyFile For Output As fnum
myRow = 2
myCol = 4
For myRow = 2 To InputBox(Prompt:="What is the last row of data?", Title:="Data Row", Default:=1)
myCol = 4
Company = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address1 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address2 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address3 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 2
Phone = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Fax = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
strSQL = "INSERT INTO [sandbox].[5y5t3mus3r].[ryan] (Organisation, Address1, Address2, TownCity, County, Telephone, Fax) VALUES('" & Company & "', '" & Address & "', '" & Address1 & "', '" & Address2 & "', '" & Address3 & "', " & Phone & ", " & Fax & ");"
Print #fnum, strSQL
DoCmd.RunSQL strSQL ***Here is where I am haveing an error it will not run the SQL command.****
oConn.Execute strSQL **** here is another tag I tried in a number of different ways but i still couldnt get the SQL statement to run
Next
End With
' Find out how many rows were affected by the Insert.
Set rs = oConn.Execute("Select @@rowcount")
' Display the first field in the recordset.
MsgBox rs(0) & " rows inserted"
oConn.Close
Set rs = Nothing
Set oConn = Nothing
Close #fnum
End Sub
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function
The error arises when I am trying to run the SQL statement do I need to create an object or method for this or something.
当我尝试运行 SQL 语句时出现错误，我是否需要为此或某事创建对象或方法。
Any help with this would really be appreciated thanks!
对此的任何帮助将不胜感激，谢谢！
回答by Mark McGinty
I'd guess it's this line:
我猜是这一行：
rs.Source = Sql
rs.Source = Sql
The Source property accepts an IStream as well as a string, so since Sql isn't declared anywhere, it's implicitly an object with a value of Nothing.
Source 属性接受一个 IStream 和一个字符串，因此由于 Sql 没有在任何地方声明，它隐式地是一个值为 Nothing 的对象。
My next guess is a couple of lines below that, where does wsheet get assigned?
我的下一个猜测是下面几行， wsheet 在哪里分配？
Of course, all this would be easier if we knew which line the error occurs on... easier still if you set a break point and step into the code -- you don't need to dump variable values to file, you can view them interactively in the debugger.
当然，如果我们知道错误发生在哪一行，所有这一切都会更容易……如果您设置断点并进入代码，则更容易——您不需要将变量值转储到文件中，您可以查看它们在调试器中交互。
回答by Derek B. Bell
Instead of using the INSERT statement, I would suggest creating a stored procedure and passing values into it using the ADOBO.Command object.
我建议不要使用 INSERT 语句，而是建议创建一个存储过程并使用 ADOBO.Command 对象将值传递给它。
