Excel中的VBA宏以运行SQL插入语句

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语句的trubble。这是我到目前为止的代码。

     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!

任何有关这方面的帮助将非常感谢！

3 个解决方案

#1

I'd guess it's this line:

我猜这就是这条线：

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.

当然，如果我们知道错误发生在哪一行上，这一切都会更容易...如果你设置一个断点并进入代码更容易 - 你不需要将变量值转储到文件中，你可以查看它们在调试器中以交互方式进行。

#2

Instead of using the INSERT statement, I would suggest creating a stored procedure and passing values into it using the ADOBO.Command object.

我建议使用ADOBO.Command对象创建存储过程并将值传递给它，而不是使用INSERT语句。

#3

Look this article

看看这篇文章

it is a easy way to import data from Excel to SQl with ADO.

这是一种使用ADO将数据从Excel导入SQl的简便方法。

[]´s

[]的

#1