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
Excel 2007 VBA - Run Time Error 1004
提问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:
设置名称可能失败的三个想法：
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'Maybe the name you are trying to set contains some illegal characters:
: / \ * ? [ ]are not allowedAn empty string or a string of more than 31 characters is not allowed, either
您的工作簿中是否已有具有该名称的工作表？
尝试设置已在使用的名称将导致“1004”也许您尝试设置的名称包含一些非法字符：
: / \ * ? [ ]不允许也不允许空字符串或超过 31 个字符的字符串
