VBA to open file from HTTPS location

[stephena]

I "borrowed" this from a post online which appears to address the issue i have opening files from our internal https site:

Private Sub CommandButton21_Click()
Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

mainUrl = "https://mycompany.com/"
fileUrl = "https://mycompany.com/sdd_template.docx"
filePath = "C:\Users\me\Desktop\sdd_template.docx"

myuser = "[email protected]"
mypass = "******"

strAuthenticate = "start-url=%2F&user=" & myuser & "&password=" & mypass & "&switch=Log+In"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "POST", mainUrl, False 'WHTTP.Open "POST", fileUrl, False
WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.Send strAuthenticate

'Then you have to GET direct file url
WHTTP.Open "GET", fileUrl, False
WHTTP.Send

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

'Save the file
FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

It executes perfectly however the downloaded Word document is corrupt and cannot be used.

Anybody tried similar and succeeded?

Thanks

Steve

[westconn1]

if you do not open the fie and put filedata is the downloaded file still corrupt?

you can not really just write data to a word document, as a text file, you should be using Word automation, to do that, or possibly xml

[stephena]

Thanks, at this stage I am not attempting to write anything to the document just download it from the HTTPS location to the local machine.

[westconn1]

try using urldownloadtofile api instead of the above
there are many examples in this and the vb6 forums

[stephena]

Tried this...

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
 
Private Sub CommandButton21_Click()

fileurl = "https://#####.com"

myuser = "######"
mypass = "#####"
myResult = URLDownloadToFile(0, fileurl, "C:\Users\" & Environ("username") & "\Desktop\" & "cdm.docx", 0, 0)

If myResult <> 0 Then
    MsgBox "Error downloading " & mylink & Chr(10) & Error(myResult)
Else
    MsgBox "File has been downloaded"
End If

End Sub

Downloads but 'Could not open file as its corrupt' error when trying to open it from my desktop.

[westconn1]

i tested your code to download a docx worked fine, document opened OK

can you post a sample docx to test with, not sure what other problem can be occurring, could the file be encrypted?

[stephena]

I believe my issue is down to the fact the file location is behind our company Single Sign On authentication. But i cannot find a way to bypass this currently

[westconn1]

can you use ftp?