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!
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).
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 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 : - 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 : - I would suggest using str for String variables. con is typically used to indicate constants and txt identifies TextBox controls on forms or reports.
- Your String array, txtSymbols, is Dimmed as two-dimensional, but it seems only one is ever used. Try instead Dim strSymbols(700).
- 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. - Lines #18 and #19 would be changed to :
- Call SetSheet(strName:=strSymbols(i))
- 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.
- 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
- Line #22 activates the query.
- Lines #23 to #25 ensure any related Name objects are deleted afterwards.
- 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.
Thanks for the suggestions - I'm still working on implementation.
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 :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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"%>
...
|
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...
|
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
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |