PDA

View Full Version : Solved: Open CSV website?



omnibuster
10-15-2011, 03:12 PM
Welcome.

I have strange situation?
My code works fine years, but monday it stop works?:banghead:

This is fragment of code.

For Each vIE In SWs
If Left(vIE.LocationURL, 4) = "http" Then
Set vIEDoc = vIE.Document
For i = 0 To vIEDoc.links.Length - 1
found = False
With vIEDoc
If Right(vIEDoc.links(i).href, 7) = "english" Then
found = True
todaysURL = vIEDoc.links(i).href
If found Then
Workbooks.Open todaysURL

'error: Method 'Open' of object 'Workbooks' failed


Stop
GoTo 10
End If
End If
End With
Next i
End If
Next


I tried go to other-short way, but stuck here: how open CSV-file in website?


URL = Range("A" & i)

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = 1
.Navigate URL
Do While .Busy: DoEvents: Loop
' DoEvents


' How open CSV file for copy to Excel?
' ActiveWorkbook.SaveAs Filename:="AEG.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

omnibuster
10-16-2011, 12:24 PM
Sry My panic.
Solved with: http://www.sqldrill.com/excel/programming-vba-vb-c-etc/999025-download-csv-file-website.html

Private Sub CommandButton1_Click()
Dim sfileName As String
Dim sStockName As String
Dim sUrl As String
sUrl = http://www....."
sfileName = "C:\Users\Kodu\Desktop/Data.xls" '
SaveWebFile sUrl, sfileName
End Sub
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String)
'As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as
'MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
oXMLHTTP.send 'send request
'Wait for request to finish
Do While oXMLHTTP.ReadyState <> 4
DoEvents
Loop
oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
'Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
'Clear memory
Set oXMLHTTP = Nothing
End Function