469,286 Members | 2,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

How do I Manage Worksheets Within a Workbook

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!
Dec 21 '11 #1
5 13834
32,173 Expert Mod 16PB
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).
Dec 22 '11 #2

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.

Expand|Select|Wrap|Line Numbers
  1. Sub Macro8()
  2.     Dim conString As String
  3.     Dim conName As String
  4.     Dim txtSymbols(0, 700) As String
  6.     txtSymbols(0, 1) = "A"
  7.     txtSymbols(0, 2) = "AAPL"
  8.     txtSymbols(0, 3) = "ACH"
  9.     txtSymbols(0, 4) = "ACLS"
  10.     txtSymbols(0, 5) = "ACTS"
  11.     txtSymbols(0, 6) = "ADI"
  12.     txtSymbols(0, 7) = "ADTN"
  13.     txtSymbols(0, 8) = "ADY"
  14.     txtSymbols(0, 9) = "AEIS"
  15.     txtSymbols(0, 10) = "AERL"
  17.     For i = 1 To 10
  18.         Sheets.Add After:=Sheets(Sheets.Count)
  19.         ActiveSheet.Name = txtSymbols(0, i)
  21.         conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  22.         conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  24.         With ActiveSheet
  25.             .Name = conName
  26.             .FieldNames = True
  27.             .RowNumbers = False
  28.             .FillAdjacentFormulas = False
  29.             .PreserveFormatting = True
  30.             .RefreshOnFileOpen = False
  31.             .BackgroundQuery = True
  32.             .RefreshStyle = xlInsertDeleteCells
  33.             .SavePassword = False
  34.             .SaveData = True
  35.             .AdjustColumnWidth = True
  36.             .RefreshPeriod = 0
  37.             .WebSelectionType = xlSpecifiedTables
  38.             .WebFormatting = xlWebFormattingNone
  39.             .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
  40.             .WebPreFormattedTextToColumns = True
  41.             .WebConsecutiveDelimitersAsOne = True
  42.             .WebSingleBlockTextImport = False
  43.             .WebDisableDateRecognition = False
  44.             .WebDisableRedirections = False
  45.             .Refresh BackgroundQuery:=False
  46.         End With
  47.     Next i
  48. End Sub
BTW, Happy New Year to you and thanks for being patient.
Jan 2 '12 #3
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub SetSheet(strName As String)
  2.     On Error Resume Next
  3.     Call Worksheets(strName).Select
  4.     If ActiveSheet.Name = strName Then
  5.         Call ActiveSheet.UsedRange.Delete
  6.         Exit Sub
  7.     End If
  8.     Call Worksheets.Add(After:=Worksheets(Worksheets.Count))
  9.     ActiveSheet.Name = strName
  10. 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 :
    Expand|Select|Wrap|Line Numbers
    1. 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 :
    Expand|Select|Wrap|Line Numbers
    1. 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 :
    Expand|Select|Wrap|Line Numbers
    1. 'GetExternalData refreshes the data in the current sheet from strQryName.
    2. Private Sub GetExternalData(ranDest As Range, strQryName As String)
    3.     Dim strWork As String, strSQL As String
    4.     Dim namQuery As Name
    6.     strWork = ""  'Code here to specify the database
    7.     strSQL = Replace("SELECT * FROM [%Q]", "%Q", strQryName)
    8.     With ActiveSheet.QueryTables.Add(Connection:=strWork, Destination:=ranDest)
    9.         .CommandText = strSQL
    10.         .Name = strQryName
    11.         .FieldNames = False
    12.         .RowNumbers = False
    13.         .FillAdjacentFormulas = False
    14.         .PreserveFormatting = False
    15.         .BackgroundQuery = True
    16.         .RefreshStyle = xlInsertDeleteCells
    17.         .SavePassword = False
    18.         .SaveData = True
    19.         .AdjustColumnWidth = False
    20.         .RefreshPeriod = 0
    21.         .PreserveColumnInfo = True
    22.         Call .Refresh(BackgroundQuery:=False)
    23.         For Each namQuery In ActiveSheet.Names
    24.             If InStr(1, namQuery.Name, .Name) > 0 Then Call namQuery.Delete
    25.         Next namQuery
    26.         Call .Delete
    27.     End With
    28. 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.
Jan 2 '12 #4
Thanks for the suggestions - I'm still working on implementation.
Jan 16 '12 #5
32,173 Expert Mod 16PB
No worries. Let us know if you manage to get it all working, or if you need more explained :-)
Jan 16 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Pierre | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.