How do I Manage Worksheets Within a Workbook

[Bill Mochal]

This question was split from a related thread (VBA Importing Data from Web into Excel2007).

There's one issue left.

The VBA code won't run if the workbook already contains individual sheets with the same names. If you look at the code, the first loop iteration creates a spreadsheet page named "A". If there's already a page named, "A", in the workbook, you get an error. So I've been running the VBA code in a blank workbook. The problem is that I'd prefer to run the code in a workbook that contains all the previous data so that when a webpage can't be accessed, the original data is still available. And if the webpage can be imported, the new data would overwrite the old.

Another suggestion would be a great gift for me NeoSanta!

[NeoPa]

I guess that would be a workbook with 690 worksheets within it. Pretty big huh.

I suggest you post your latest code here and ensure that you follow instructions found at When Posting (VBA or SQL) Code. Your last version referred to items that were not defined in the code so left me without anything reliable to work with.

The latest version should include code that ensures any QueryTables are deleted as soon as they've been created and executed (as explained in post #4 of the original thread).

[Bill Mochal]

NeoPa,

Sorry for the breach of etiquette... I am restoring the best answer designation to your reply in the prior thread.
Here's the code I've been experimenting with (reduced to only ten web page queries).
The problem is that an error is generated because the code is trying to create a worksheet named "A","AAPL", etc. and that worksheet name already exists.

Sub Macro8()
    Dim conString As String
    Dim conName As String
    Dim txtSymbols(0, 700) As String

    txtSymbols(0, 1) = "A"
    txtSymbols(0, 2) = "AAPL"
    txtSymbols(0, 3) = "ACH"
    txtSymbols(0, 4) = "ACLS"
    txtSymbols(0, 5) = "ACTS"
    txtSymbols(0, 6) = "ADI"
    txtSymbols(0, 7) = "ADTN"
    txtSymbols(0, 8) = "ADY"
    txtSymbols(0, 9) = "AEIS"
    txtSymbols(0, 10) = "AERL"

    For i = 1 To 10
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = txtSymbols(0, i)

        conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
        conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"

        With ActiveSheet
            .Name = conName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    Next i
End Sub

BTW, Happy New Year to you and thanks for being patient.

[NeoPa]

Bill Mochal:
Sorry for the breach of etiquette...

Not a problem. You may want to re-view the linked article as you didn't include the full error message and associated line number this time. Not a big issue. I was able to work it out, but it certainly helps. Again, don't worry about it. It's best to do it properly, but you've included enough to work with and clearly made the effort.

On to the code. To allow the same code to be run the first time, as well as any subsequent times, use the following procedure to switch to, or create if it doesn't already exist, the correct worksheet :

Private Sub SetSheet(strName As String)
    On Error Resume Next
    Call Worksheets(strName).Select
    If ActiveSheet.Name = strName Then
        Call ActiveSheet.UsedRange.Delete
        Exit Sub
    End If
    Call Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ActiveSheet.Name = strName
End Sub

On to your main code :

1. I would suggest using str for String variables. con is typically used to indicate constants and txt identifies TextBox controls on forms or reports.
2. Your String array, txtSymbols, is Dimmed as two-dimensional, but it seems only one is ever used. Try instead Dim strSymbols(700).
3. Your For loop on line #17 could be written more flexibly as :
   

   For i = 1 To UBound(strSymbols)

   Starting at 1 is perfectly acceptable, but you should understand that by default the array has an element #0 too. Using 1 first simply ignores this one.
4. Lines #18 and #19 would be changed to :
   

   Call SetSheet(strName:=strSymbols(i))

5. Line #24 is confusing. I would have expected that to refer to the query, yet it refers to the current worksheet instead. It then goes on to rename the worksheet and apply values to a bunch of properties that don't exist.
6. When you have the correct query reference, you need to include a last line which deletes it, but only after deleting any Name objects which have been created to support it. I have some old code I wrote a while ago to handle most of this, but it was written for getting data from objects in an Access database. Nevertheless much of it will indicate how you should be looking to do what you need to here :
   

   'GetExternalData refreshes the data in the current sheet from strQryName.
   Private Sub GetExternalData(ranDest As Range, strQryName As String)
       Dim strWork As String, strSQL As String
       Dim namQuery As Name
   
       strWork = ""  'Code here to specify the database
       strSQL = Replace("SELECT * FROM [%Q]", "%Q", strQryName)
       With ActiveSheet.QueryTables.Add(Connection:=strWork, Destination:=ranDest)
           .CommandText = strSQL
           .Name = strQryName
           .FieldNames = False
           .RowNumbers = False
           .FillAdjacentFormulas = False
           .PreserveFormatting = False
           .BackgroundQuery = True
           .RefreshStyle = xlInsertDeleteCells
           .SavePassword = False
           .SaveData = True
           .AdjustColumnWidth = False
           .RefreshPeriod = 0
           .PreserveColumnInfo = True
           Call .Refresh(BackgroundQuery:=False)
           For Each namQuery In ActiveSheet.Names
               If InStr(1, namQuery.Name, .Name) > 0 Then Call namQuery.Delete
           Next namQuery
           Call .Delete
       End With
   End Sub

   1. Line #22 activates the query.
   2. Lines #23 to #25 ensure any related Name objects are deleted afterwards.
   3. Line #26, finally and after all references to it are no longer required, deletes the QueryTable itself.

Finally, Happy New Year to you Bill, and don't be worried about not getting everything perfectly right first time round.

[Bill Mochal]

Thanks for the suggestions - I'm still working on implementation.

[NeoPa]

No worries. Let us know if you manage to get it all working, or if you need more explained :-)