RE: [1] Digest for excel-vba@googlegroups.com - 1 update in 1 topic
Pete A
Suggest you look at the excel add-in library from Randy Harmelink. Totally free, and EASY to access most web pages and extract JUST what you want from a formula. It cut my programming effort to about 10% of the original. Go here:
https://groups.yahoo.com/neo/groups/smf_addin/info
Literally 1000’s of people use this, and the support is superb, AND it is ALL TOTALLY free!
Pete A
From: exce...@googlegroups.com [mailto:exce...@googlegroups.com]
Sent: Saturday, May 24, 2014 7:07 AM
To: Digest recipients
Subject: Digest for exce...@googlegroups.com - 1 update in 1 topic
Group: http://groups.google.com/group/excel-vba/topics
§ Automate Webquery for list of nearly 1900 company identifiers [1 Update]
Christopher Yust <christop...@gmail.com> May 23 09:07AM -0700
Thank you in advance for your help.
I am trying to automate an excel web query. I've got 1898 different unique
company identifers and am trying to download a table of financial
information (the statement of cash flows if you are curious) from a
database. Unfortunately, the only way to get the data due to their system
is manually through the webpage but I don't want to have to type in nearly
1900 different companies into the webpage, one-at-a-time, and then download
this table.
I think I figured out a way to do this in theory but need your help. It's
been several years since I wrote macros in VBA so I'm very rusty. I did
however write a macro that I can manually edit the company name in the web
query and then it will download the table into a new spreadsheet. However,
I still have to manually update the company identifier in the code multiple
times and then run the web query separately all nearly 1900 times.
See code excerpt (simplified):
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.webaddressommitted=COMPANYKEY&&KeyTableName=3&Defaults=0" _
, Destination:=Range("$A$1"))
.Name = _
"reports.aspx?KeyReport=-2147483510&ResetDefaults=1&ID=COMPANYKEY&KeyTableName=3&Defaults=0"
.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 = "166"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.FreezePanes = True
ActiveWindow.LargeScroll Down:=1
Range("A1") = COMPANYKEY
ActiveSheet.Name = COMPANYKEY
I didn't put the full webpage name but you can see where I have to manually
put the company key in both the webpage and the reports line to pull the
values. Thankfully, at least the database has a similar structure for each
company so you just change that identifier in both locations and it will
download the appropriate information for that company (or at least it
appears to in the 5 I've manually tested).
At the end of the code, I also put the company key in the first cell (which
is blank) and name the sheet accordingly so I can what company's info is on
each sheet.
What I need help with is how to automate it. I have a sheet in the workbook
called "Firms" which has the 1898 company identifiers in Column A starting
in Row 1. I need presumably some kind of loop in my code to scroll down
that column, copy the name of the identifier and presumably make it some
global macro and then run the above update the above code with those values
as appropriate. And then the loop would just do that one at a time.
Honestly, I don't think it should be hard for someone who knows what they
are doing, but like I said it's been a while.
Hoping one of you can pretty easily update my code to help me out.
Thanks!
FYI, if you are curious, I'm a grad student and I'm just trying to download
some new data for my research.
--
You received this message because you are subscribed to the Google Groups "excel vba" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-vba+...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-vba.
For more options, visit https://groups.google.com/d/optout.