473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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 14462
NeoPa
32,556 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
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.

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
  5.  
  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"
  16.  
  17.     For i = 1 To 10
  18.         Sheets.Add After:=Sheets(Sheets.Count)
  19.         ActiveSheet.Name = txtSymbols(0, i)
  20.  
  21.         conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  22.         conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  23.  
  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
NeoPa
32,556 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
    5.  
    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
NeoPa
32,556 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

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

Similar topics

0
by: Shawn | last post by:
I have the basics working as is described in KB199841. I currently have my ASP page loading into Excel by setting the ContentType of the page: <%response.ContentType="application/vnd.ms-excel"%> ...
0
by: Dayron | last post by:
Hi, I would like export the records retrieved from database to excel worksheets. unfortunately, each worksheet will support until 65535 records and I have about 1 million of records to write. So I...
7
by: Pierre | last post by:
Hi, Tryin to use this method : MyExcelObject.Application.ActiveWorkBook.set_Colors(int index, object RHS). But really don't know what this RHS is ? Any ideas ? Thks for help
1
by: gssstuff | last post by:
Hi I have a need to send data from Access to an existing Excel template that I am using for reporting purposes. The nature of the data in the Access data table does not lend itself to a simple...
4
by: paul.chae | last post by:
I have a table in Access with about 3000 records. There are ~60 unique values in the ID field for the 3000 records. What I would like to do is automatically generate multiple Excel worksheets...
1
by: J Daniel Melton | last post by:
Hello, I am using late binding in a managed VC++ .NET 2003 application. I used KB 302902 (for C#) as a starting point and converted it to managed C++. I built a managed class that is intantiated...
1
by: appu | last post by:
Hi, I need to rename the worksheets like Sheet1, Sheet2,....to my choice names. How to do it using asp.net code... TIA
3
by: mike11d11 | last post by:
I was able to create three worksheets in my workbook, but when I go to add the 4th I get an Invalid Index error. I must be leaving something out to when adding 4 or more sheets. Thanks Dim...
2
by: grego9 | last post by:
I have a problem in Excel 2000. I have written some VBA code that transfers data from the current excel workbook to a file called "Deal Input2.xls". Everything transfers ok apart from the date in...
2
by: simonjames | last post by:
I have an Excel workbook, which is an order form using tick boxes, if "ticked"(true)the item seleted data appears into other worksheets. If it returns "" (blank)then I want to delete the blank rows...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.