-
Jan 25th, 2021, 03:37 PM
#1
Thread Starter
New Member
[RESOLVED] vbs to download data from url as csv table
Hi!
I am using the following code to download data from a website using vbs. The data is held in the webpage in table form. However, the resulting downloaded data is in the form of a simple continuous text, which I cannot import into excel and use as a table. (I can download the same using excel etc., but I find vbs to be much faster and efficient).
The solution at https://www.example-code.com/vbscrip...ble_to_csv.asp allows converting the downloaded data to csv format, but requires specific api and software to be pre-installed.
I was wondering if it would be possible to download the data in csv format using vbs only and without using a third-party software.
Also, I had posted this request on Stackoverflow at https://stackoverflow.com/questions/...91746#65891746. A user had posted a probable solution, but I am unable to use it due to my limited vbs literacy.
Perhaps above links could give some ideas?
Code:
For i = 1 to 1
createFile(i)
Next
Public Sub createFile(a)
Dim fso,MyFile
filePath = "D:\file_name" & a & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(filePath)
myURL = "https://www.investing.com/indices/major-indices"
'Create XMLHTTP Object & HTML File
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set ohtmlFile = CreateObject("htmlfile")
'Send Request To Web Server
oXMLHttp.Open "GET", myURL, False
oXMLHttp.send
'If Return Status is Success
If oXMLHttp.Status = 200 Then
'Get Web Data to HTML file Object
ohtmlFile.Write oXMLHttp.responseText
ohtmlFile.Close
'Parse HTML File
Set oTable = ohtmlFile.getElementsByTagName("table")
For Each oTab In oTable
MyFile.WriteLine oTab.Innertext
Next
MyFile.close
End If
End Sub
'Process Completed
'WScript.Quit
Note:
1. I would request to kindly post the full working code with modifications to download the data in csv format, since I am not familiar with vbs.
2. The file with the above code needs to be saved in D:\ as any_name.vbs and resulting downloaded data file will be downloaded in D:\
Thanks.
-
Jan 25th, 2021, 06:21 PM
#2
Thread Starter
New Member
Re: [RESOLVED] vbs to download data from url as csv table
Hi!
I got the solution from Stackoverflow at https://stackoverflow.com/questions/...91746#65891746
I was however wondering how to modify the code to download only first 4 rows from the website. I know For i = 1 to 4 or Do While loops should work, but I am unable to get it to work.
Could someone kindly help me modify below code to download only first 4 rows:
Code:
For i = 1 to 1
createFile(i)
Next
Public Sub createFile(a)
Dim fso,MyFile
filePath = "D:\file_name" & a & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(filePath)
myURL = "https://example-code.com/data/etf_table.html"
'Create XMLHTTP Object & HTML File
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set ohtmlFile = CreateObject("htmlfile")
'Send Request To Web Server
oXMLHttp.Open "GET", myURL, False
oXMLHttp.send
'If Return Status is Success
If oXMLHttp.Status = 200 Then
'Get Web Data to HTML file Object
ohtmlFile.Write oXMLHttp.responseText
ohtmlFile.Close
'Parse HTML File
Set oTable_coll = ohtmlFile.getElementsByTagName("table")
For Each oTab_enum In oTable_coll
For Each oRow_enum In oTab_enum.rows
ROW = ""
For Each oCell_enum In oRow_enum.cells
ROW = ROW & oCell_enum.innerText & ","
Next
CSV = CSV & ROW & vbCrLf
Next
Next
MyFile.WriteLine CSV
MyFile.close
End If
End Sub
'Process Completed
'WScript.Quit
Thanks
Last edited by Rohan_Singh; Jan 26th, 2021 at 05:05 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|