Excel 2007 VBA - 运行时错误1004(Excel 2007 VBA - Run Time Error 1004)

 我有一个Excel 2007工作簿，我用它连接到MSSQL 2008服务器以下拉一些名称，我能够成功实现这一目标。 我的问题是我想从它从SQL Server获取的名称创建新的工作表。 
 
 我已经能够创建一个结果数组并遍历数组创建一个新工作表，但不能让它重命名工作表，VB返回运行时错误1004：应用程序定义或对象定义错误。 我创建了一个msgbox，它输出数组结果，因为我迭代它，并且msgbox中显示的名称是正确的，并且数量正确。 
 
 是否有人能够指出我的代码有任何问题，或者解释这个错误意味着什么以及如何解决它？ 我的代码在将活动表重命名为数组中的名称的行上发生错误。 如果我要将名称设为i的值，则重命名活动表。 
 
 这是我正在使用的代码： 
 
    Public Sub Dataextract()
      ' Create a connection object.
      Dim cnPubs As ADODB.Connection
      Set cnPubs = New ADODB.Connection
      ' Provide the connection string.
      Dim strConn As String

      strConn = "Source=OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
                "Persist Security Info=True;Data Source={REMOVED};"
      'Now open the connection.
      cnPubs.Open strConn
      ' Create a recordset object.
      Dim rsPubs As ADODB.Recordset
      Set rsPubs = New ADODB.Recordset
      With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        ' The Select Query to display the data
        .Open "SELECT DISTINCT [databaseName] FROM [DBMonitor].[dbo].[dbGrowth]"
        ' Copy the records into cell A2 on Sheet1.
        'Sheet1.Range("A2").CopyFromRecordset rsPubs
        vArray = rsPubs.GetRows()
        rowsreturned = UBound(vArray, 2) + 1
        For i = 0 To rowsreturned - 1
           ' Added the following to see if it errors anywhere else, or if it is
           ' just the one record.
           'On Error Resume Next
           Sheets.Add After:=Sheets(Sheets.Count)
           ' FAILS HERE....
           ActiveSheet.Name = vArray(0, i)
           MsgBox (i & " " & vArray(0, i))
        Next i
       ' Tidy up
       .Close
     End With
     cnPubs.Close
     Set rsPubs = Nothing
     Set cnPubs = Nothing
  End Sub
 
 任何人都可以提供的任何帮助将不胜感激。 
 
 谢谢， 
 
 马特 

I have an Excel 2007 workbook which I am using to connect to a MSSQL 2008 server to pull down some names, I am able to achieve this successfully. My problem is I would like new worksheets created from the names that it gets from the SQL Server.
 
I have been able to create an array of the results and iterate through the array creating a new sheet, but cant get it to rename the sheet, VB returns a run-time error of 1004: Application-defined or object defined error. I have create a msgbox that outputs the array results as Im iterating through it and the names that are displaying in the msgbox are correct and of the right amount.
 
Is anyone able to point out any problems with my code or maybe explain what this error means and how to resolve it? My code is erroring on the line where the activesheet is being renamed to the name in the array. If I was to make the name the value of i, the activesheet is renamed.
 
Here is the code I am using:
 
    Public Sub Dataextract()
      ' Create a connection object.
      Dim cnPubs As ADODB.Connection
      Set cnPubs = New ADODB.Connection
      ' Provide the connection string.
      Dim strConn As String

      strConn = "Source=OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
                "Persist Security Info=True;Data Source={REMOVED};"
      'Now open the connection.
      cnPubs.Open strConn
      ' Create a recordset object.
      Dim rsPubs As ADODB.Recordset
      Set rsPubs = New ADODB.Recordset
      With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        ' The Select Query to display the data
        .Open "SELECT DISTINCT [databaseName] FROM [DBMonitor].[dbo].[dbGrowth]"
        ' Copy the records into cell A2 on Sheet1.
        'Sheet1.Range("A2").CopyFromRecordset rsPubs
        vArray = rsPubs.GetRows()
        rowsreturned = UBound(vArray, 2) + 1
        For i = 0 To rowsreturned - 1
           ' Added the following to see if it errors anywhere else, or if it is
           ' just the one record.
           'On Error Resume Next
           Sheets.Add After:=Sheets(Sheets.Count)
           ' FAILS HERE....
           ActiveSheet.Name = vArray(0, i)
           MsgBox (i & " " & vArray(0, i))
        Next i
       ' Tidy up
       .Close
     End With
     cnPubs.Close
     Set rsPubs = Nothing
     Set cnPubs = Nothing
  End Sub
 
Any help anyone can provide will be greatly appreciated.
 
Thanks,
 
Matt

原文：https://stackoverflow.com/questions/1250484