Excel 2007 VBA - Runtime Error 1004

Question

I have an Excel 2007 workbook that I use to connect to the MSSQL 2008 server to pull out some names, I can achieve this. My problem is that I would like new worksheets to be created from the names that it gets from SQL Server.

I managed to create an array of results and iterate through the array, creating a new sheet, but I can’t rename it, VB returns a 1004 runtime error: an error detected by the application or an object error, I created msgbox that displays the results of the array as Im, iterating through it , and the names displayed in msgbox are correct and have the correct amount.

Can anyone point out any problems with my code or explain what this error means and how to solve it? My code is an error in a line where activesheet is renamed to a name in an array. If I were to make the name a value of i, the activesheet would be renamed.

Here is the code I'm 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 would be greatly appreciated.

Thanks,

Matt

Answer 1

Three ideas why setting a name may fail:

• Do you already have a sheet with that name in your book?
  Attempting to set a name that is already in use will result in "1004"

• The name you are trying to set may contain some invalid characters:
  : / \ * ? [ ]not allowed

• 31 ,