Excel 2007 VBA - Run Time Error 1004

Question

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

Answer 1

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