0

Code is working properly, but when I try to open file it is corrupt. I cannot open it, but when I do open it in notepad I have several lines of html code, so it seems like it is not downloading file exactly but some other sharepoint webpage components or other stuff.

Option Explicit

#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" ( _
          ByVal pCaller As LongLong, _
          ByVal szURL As String, _
          ByVal szFileName As String, _
          ByVal dwReserved As LongLong, _
          ByVal lpfnCB As LongLong) As LongLong
#Else
  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
#End If


Function DownloadFile(Url As String, SavePathName As String) As Boolean
    DownloadFile = URLDownloadToFile(0, Replace(Url, "\", "/"), SavePathName, 0, 0) = 0
End Function

Sub Demo()
Dim strUrl As String, strSavePath As String, strFile As String
strUrl = "https://zzzzzzz.ll.xlsx"
'SharePoint Path for the file
strSavePath = "C:\Users\username\Desktop\" 'Folder to save the file
strFile = "CompanySalesReport" & ".xlsx"

If DownloadFile(strUrl, strSavePath & strFile) Then
    MsgBox "File saved to: " & vbNewLine & strSavePath
Else
    MsgBox "Unable to downloaf file:" & vbNewLine & strFile & vbNewLine & "Check url string and that document is shared", vbCritical
End If
End Sub
Kokopas
  • 31
  • 1
  • 6
  • After you download it, first check under File...Properties to see if there was an "Unblock" attribute added to it that you need to manually clear first. – braX Nov 21 '22 at 11:29
  • I don't see such attribute, done according to this link https://thegeekpage.com/unblock-a-file-blocked-by-windows-10/#:~:text=1%20%E2%80%93%20Right%20click%20on%20the,to%20help%20protect%20this%20computer.%E2%80%9D – Kokopas Nov 21 '22 at 11:32
  • I get error that there is a problem with this formula. I did url with "" and without, the same goes with location. I am using the funtcion which Tragamor provided. – Kokopas Nov 21 '22 at 11:48
  • https://stackoverflow.com/a/70126741/8422953 – braX Nov 21 '22 at 12:00
  • I also tried to run function in VBA and error I get is Funtcion call on left-hand side of assignment must return Variant or Object. It seems that I will have to try do it in Python or other tool, I do not have Power Automate – Kokopas Nov 21 '22 at 12:04
  • If you use the "Get Link" on sharepoint it will have a bunch of other stuff in the direct file URL. You may have to remove everything after the .xlsx for it to download properly. – Tragamor Nov 21 '22 at 13:33
  • I did that before posting but thanks for hint – Kokopas Nov 21 '22 at 14:04
  • 1
    Open the file directly in Excel using Workbooks.Open, then SaveAs to the desktop? – Tim Williams Nov 21 '22 at 16:53
  • Problem solved. I did another macro which opens a workbook from SharePoint and then saves it locally. Thank you for your input. – Kokopas Nov 22 '22 at 09:27

0 Answers0