新聞財經娛樂♬資訊 News Economy

I get one text file from remote website and its file size is 50K and I try to
open with follow code in VBA

workbooks.open "http://www.example.com/getData" ,false

and excel will open the file and stored it into A1 cell in sheet1, and then I will decode or extract
A1 value into my format by VBA. if the getData file is too big, the Excel's  A1 cell
could not store all data and it will split into A1, B1 or C1 that is hard for
me to decode because I would like the data is continuous into one buffer or cell
So I try think  why not putting it into memory and then reference to one VBA variable without
opening the getData file into Excel ?
But I don't know how ?

Please advise

Duncan
 

Sorry, forgot to mention that.
You will need to include a reference to following library:

Microsoft WinHTTP Services, version 5.1

Just from Tools > References menu as usual.

In that case, I would advise you to use the function I described under #3. Just cut and paste that function into your VBA module.

Public Function DownloadTextFile(url As String) As String    
.....

Then call it from your code.
The result is a text string which you can then manipulate all you want.
Easiest will be to make a little loop to fill the cells, using the "|" as delimiter.
Are you familiar with that?

Little sample code ...

  Dim i As Integer
  Dim s As String

  s = DownloadTextFile(yourURLtoDownloadHere)
 
  Dim val As String
  Dim pos1 As Integer
  Dim pos2 As Integer
  pos1 = 1
  pos2 = 1
 
  Do While pos2 > 0
    pos2 = InStr(pos1, s, "|")
    If (pos2 > 0) Then
        val = Mid(s, pos1, pos2 - pos1)
    Else
        val = Mid(s, pos1)
    End If
    Range("A1").Offset(i, 0).Value = val
    If (pos2 > 0) Then
        i = i + 1
        pos1 = pos2 + 1
        If pos1 > Len(s) Then
            Exit Do
        End If
       
    End If
  Loop


Hope that helps

Sorry - I cut the source code with my solution # 4 - here is the complete code for #3:


Public Function DownloadTextFile(url As String) As String    

Dim oHTTP As WinHttp.WinHttpRequest    
Set oHTTP = New WinHttp.WinHttpRequest    
oHTTP.Open Method:="GET", url:=url, async:=False    
oHTTP.setRequestHeader "User-Agent", "MSIE 6.0"
oHTTP.setRequestHeader "Content-Type", "multipart/form-data; "
oHTTP.Option(WinHttpRequestOption_EnableRedirects) = True    
oHTTP.send    

Dim success As Boolean    
success = oHTTP.waitForResponse()    
If Not success Then        
  Debug.Print "DOWNLOAD FAILED!"        
  Exit Function    
End If      

Dim responseText As String    
responseText = oHTTP.responseText      
Set oHTTP = Nothing      
DownloadTextFile = responseText

End Function

Okay, my error, of course it is not going to work that easily from a web page.

Few things you could do.

(1) First you can change the column width to accomodate large files, using
the .columnWidth property of that cell.
Of course, that's a limited way to do it.


(2) Use the command:
Application.Workbooks.OpenText "http://www.google.com/"

This loads the various lines of the web page into SEVERAL lines, and may
be just what you need.


(3) If not good enough, you could write your own http request handler.
Sample function which should work here below

Public Function DownloadTextFile(url As String) As String    

Dim oHTTP As WinHttp.WinHttpRequest    
Set oHTTP = New WinHttp.WinHttpRequest    
oHTTP.Open Method:="GET", url:=url, async:=False    
oHTTP.setRequestHeader "User-Agent", "MSIE 6.0"
oHTTP.setRequestHeader "Content-Type", "multipart/form-data; "
oHTTP.Option(WinHttpRequestOption_EnableRedirects) = True    
oHTTP.send    

Dim success As Boolean    
success = oHTTP.waitForResponse()    
If Not success Then        
  Debug.Print "DOWNLOAD FAILED!"        
  Exit Function    
End If      


(4) You can also download "wget.exe" for Windows (browse for it in Google) and run this via a shell object in VBA. It will download the web page into a file. You can get that file then via the FreeFile / Input method.
But #3 above will be easier and faster.



Dim responseText As String    
responseText = oHTTP.responseText      
Set oHTTP = Nothing      
DownloadTextFile = responseText

End Function

You can use FreeFile and Input$ to get the text file,
something like this:

   Dim nSourceFile As Integer, sText As String
   'Close any open text files
   Close

   'Get the number of the next free text file
   nSourceFile = FreeFile

   'Write the entire file to sText
   Open sFile For Input As #nSourceFile
   sText = Input$(LOF(1), 1)
   Close