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

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.

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

Select allOpen in new window

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

Thanks for the help