Hello folks
I wrote the code below, unfortunately, it works half the time. It's supposed to download some documents from a sharepoint site and save them. Sometimes it gets downloaded, sometimes it does not. I can't tell why. I would like to insert a code snippet where I can see what is happening. However, I am not sure how to do that. I commented my code to make it easier.
Any help is appreciated
Here is my code:
I wrote the code below, unfortunately, it works half the time. It's supposed to download some documents from a sharepoint site and save them. Sometimes it gets downloaded, sometimes it does not. I can't tell why. I would like to insert a code snippet where I can see what is happening. However, I am not sure how to do that. I commented my code to make it easier.
Any help is appreciated
Here is my code:
Code:
Option Explicit
'Private function declaration for downloading the files from the Sharepoint site
'Function works with Sub download_files()
Private Declare PtrSafe 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
Dim Ret As Long
Sub import_files()
'Step 1
'This procedure imports all of the files from the Sharepoint site . Then deletes all
'of the extra data sheets on the import files. The procedure ends by copying the
'imported files into the template workbook.
Dim sDestSheet As String
Dim strSourceFile As String
Dim N As Long
Dim strLink As String
Dim strUrl As String
Dim strPath As String
'Import the files from the Sharepoint site
strSourceFile = ThisWorkbook.Name
strLink = "https://sharepoint.teleperformanceusa.com/HumanResources/HRSSReporting/SitePages/Home.aspx"
On Error Resume Next
For N = 1 To 5 Step 1
strUrl = strLink & GETIMPORTFILE(N) 'Access the url to Sharepoint and find the file
strPath = sPATH & GETIMPORTFILE(N) 'Save the file to the path of the workbook and save with the same name.
sDestSheet = GETDESTSHEET(N)
Ret = URLDownloadToFile(0, strUrl, strPath, 0, 0) 'Return the file
'If the files are on the worbookpath, then
If Len(Dir(strPath)) > 0 Then
With Workbooks.Open(strPath) 'Open each of the imported files on the path
Application.DisplayAlerts = False 'Disable application notifications
Sheets("Segments").delete 'Delete the Segments worksheet
Sheets("Summary").delete 'Delete the Summary worksheet
.Worksheets(1).Cells.Copy Workbooks(strSourceFile).Worksheets(sDestSheet).Range("A1") 'Copy each workbook to the corresponding worksheet on the template
.Close savechanges:=True 'Save the changes to the imported files.
End With
End If
Next
End Sub