Net download through VBA - wrong extension?

[corquando]

Greetings, Board.

I use this:

Sub Downlodez()
 
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
 
            On Error Resume Next
                Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
                If Err.Number <> 0 Then
                    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
                End If
            On Error GoTo 0
 
            MyFile = "http://thatsite.com/sites/NoBigFile.xlsm"
 
            WHTTP.Open "GET", MyFile, False
            WHTTP.Send
            FileData = WHTTP.ResponseBody
            Set WHTTP = Nothing
 
            If Dir("G:\Huge\Medium\Small", vbDirectory) = Empty Then _
                    MkDir "G:\Huge\Medium\Small"
 
            FileNum = FreeFile
            Open "G:\Huge\Medium\Small\NoBigFile.xlsm" For Binary Access Write As #FileNum
                Put #FileNum, 1, FileData
            Close #FileNum
 
 
End Sub

And it downloads the files I specify quite nicely.

However, when I attempt to open the downloaded files with a "Workbooks.Open" commend, I get a Runtime 1004 error sayng the file format or extension is not valid. I am naming the exact file and extension in the dowload code, as you can see. It also advises to insure the file has not been corrupted and that the extension matches the format. Unknown how to do that . . .

When I manually download the file it opens just fine so, I am corn-fused.

The file does contain macros and Pivot Tables, but the process doesn't demand links, as the macros obtain the data necessary through simple copy-n-paste commands.

What's the incredibly obvious part I can't see?

Thanks.

 

[wigi]

If I paste this link:

http://www.thatsite.com/sites/NoBigFile.xlsm

into an address bar, I get an error saying that "The requested URL /sites/NoBigFile.xlsm was not found on this server."

 

[corquando]

Yeah - that's a pretend name. My company has problems with the public at large accessing their actual internal files. Can't imagine why . . .

May hap's the fact it's a SharePoint site may be important, though. So, in fact, thanks for asking. That small tidbit might just be the crux of the biscuit.

 

[wigi]

Consider:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                                           (ByVal pCaller As Long, _
                                            ByVal szURL As String, _
                                            ByVal szFileName As String, _
                                            ByVal dwReserved As Long, _
                                            ByVal lpfnCB As Long) As Long

Sub DownloadCPIFile()
    
    On Error Resume Next
    URLDownloadToFile _
                    0, _
                    "http://thatsite.com/sites/NoBigFile.xlsm", _
                    "C:/NoBigFile.xlsm", 0, 0
                    
    If Err.Number <> 0 Or Dir("C:\NoBigFile.xlsm") = "" Then
        MsgBox "Downloading the file produced an error: " & Err.Description, vbCritical, "Status"
    Else
        MsgBox "Downloading the file was a success.", vbInformation, "Status"
    End If
    
End Sub

 

[RSXchin]

try this too.

Sub open_website()
Workbooks.Open Filename:= "http://thatsite.com/sites/NoBigFile.xlsm"
End Sub

 

[corquando]

Cool! I'll give 'em a shot. Thanks in advance!

 

[corquando]

I tried the second one first, because I liked the simplicity of just opening the book from the source, but it was much too reasonable and logical, so it didn't work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
But the first one did. All I had to do was disable the UpdateLinks on Open after downloading and viola! So, chalk up another reason that this is the Best. Website. Ever.

Thanks bzillions!<o></o>