We help IT Professionals succeed at work.

Putting the contents of an ASHX file on a website into Excel using VBA

2,184 Views
Last Modified: 2013-11-25
Given:
Link
ASHX Link within Link (is a link to an excel file)

Result:
Open Link, then get contents of ASHX link into Sheet1 of current open workbook.

An example Link:
Link:  http://finviz.com/screener.ashx
ASHX Link embedded:  'export'(http://finviz.com/export.ashx?v=111)

I was thinking of either saving the contents of ASHX link and then copying it OR just recursively going through each webpage and copy all the tables using QueryTables.
Comment
Watch Question

The export link is the one you'd want - as it can be opened in Excel directly.  Copying the sheet to the active workbook is quite straightforward too... Did you simply want it moved to the active book?  What version of Excel are you using?  I could write some code to download the csv file and import to current workbook quite easily.

Author

Commented:
Hi pp,  I have excel 07 and yes I was looking for code that would automate this in the current open workbook.
The below code will download from the given URL and dump the data to a new worksheet in Excel.

Paste the code into a new module in the VB editor, then add a reference (Tools... References...) to Microsoft VBScript Regular Expressions 5.5.  Once done, you can simply call the function and your data will be grabbed.

Public Sub DownloadCSV()
   Dim objHttp As Object
   Dim oRegExp As RegExp
   Dim oMatches As MatchCollection
   Dim oMatch As Match
   Dim sCSV As String
   Dim aCSVLines() As String
   Dim aCSVItems() As String
   Dim sCSVField As String
   Dim ws As Worksheet
   Dim i As Long, j As Long
   
   ' Download the file
   Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
   Call objHttp.Open("GET", "http://finviz.com/export.ashx?v=111", False)
   Call objHttp.Send("")
   sCSV = objHttp.ResponseText
   Set objHttp = Nothing
   
   ' We now have the exported file, create new worksheet and dump into it
   Set ws = ActiveWorkbook.Worksheets.Add
   ' Split the file into lines
   aCSVLines = Split(sCSV, vbCrLf)
   
   ' Create regular expression object to parse CSV lines
   Set oRegExp = New RegExp
   With oRegExp
      .Pattern = "(""([^""]*|""{2})*""(,|$))|""[^""]*""(,|$)|[^,]+(,|$)|(,)"
      .Global = True
      .MultiLine = False
   End With
   
   ' Now loop through each line and dump to worksheet
   For i = 0 To UBound(aCSVLines)
      j = 1
      Set oMatches = oRegExp.Execute(aCSVLines(i))
      For Each oMatch In oMatches
         sCSVField = oMatch.Value
         If Right(sCSVField, 1) = "," Then
            ' Strip trailing comma
            sCSVField = Left(sCSVField, Len(sCSVField) - 1)
         End If
         If (Left(sCSVField, 1) = """") And (Right(sCSVField, 1) = """") Then
            ' Strip quotes
            sCSVField = Mid(sCSVField, 2, Len(sCSVField) - 2)
         End If
         ws.Cells(i + 1, j).Value = sCSVField
         j = j + 1
      Next
   Next
End Sub

Open in new window

Author

Commented:
Hi PP,

The only problem with this is that all the export csv links are named the same.  The current code above will only get me the default screen.  For example, a specific link/screen (e.g. http://finviz.com/screener.ashx?v=152&c=1,2,3,4,5,6,7,10,11,12,14,15,24,31,35,36,38,48,65,66) will have an export link, which is named the same for all screens.  

Logically I need to open the webpage (which I know how to do) and then get the link embedded (I'm assuming asp is doing something in the background to get the contents of the specific screen).  So I was hoping to get exactly the screened contents.

I hope this makes sense...thx
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the help

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.