Excel 2007 VBA - 运行时错误 1004

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1250484/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:40:44  来源:igfitidea点击:

Excel 2007 VBA - Run Time Error 1004

excel-vbavbaexcel

提问by Lima

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.

我有一个 Excel 2007 工作簿,我用它来连接到 MSSQL 2008 服务器以提取一些名称,我能够成功实现这一目标。我的问题是我想要根据它从 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.

我已经能够创建一个结果数组并遍历该数组创建一个新工作表,但无法重命名工作表,VB 返回运行时错误 1004:应用程序定义或对象定义错误。我创建了一个 msgbox,它输出数组结果作为 Im 遍历它,并且显示在 msgbox 中的名称正确且数量正确。

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.

有没有人能够指出我的代码的任何问题,或者解释这个错误的含义以及如何解决它?我的代码在将 activesheet 重命名为数组中的名称的那一行出错。如果我将名称设为 i 的值,则活动表将被重命名。

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

马特

回答by Treb

Three ideas why setting the name might fail:

设置名称可能失败的三个想法:

  1. Do you already have a sheet with that name in your workbook?
    Trying to set a name that is already in use will result in a '1004'

  2. Maybe the name you are trying to set contains some illegal characters:
    : / \ * ? [ ]are not allowed

  3. An empty string or a string of more than 31 characters is not allowed, either

  1. 您的工作簿中是否已有具有该名称的工作表?
    尝试设置已在使用的名称将导致“1004”

  2. 也许您尝试设置的名称包含一些非法字符:
    : / \ * ? [ ]不允许

  3. 也不允许空字符串或超过 31 个字符的字符串