bs329
asked on
Putting the contents of an ASHX file on a website into Excel using VBA
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.
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.
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.
ASKER
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.
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help