How to insert and error check in the code?

[jmazorra]

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:

  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

 

[baitmaster]

My hunch is that you are looping through your files by number, then making changes to them. If your changes change the order in any way then your loop will start processing in the wrong order. Try looping through them in reverse order, or referring to every object rather than item number. Does DIR work on SharePoint folders? If so then create your objects that way instead and work with actual objects.

why is there an On Error Resume Next? This is a broad-brush error handler that masks all problems, not just the one you are trying to avoid. I would use it sparingly, only to resolve the expected error - so in this case I think you use it to ignore problems where say file 5 doesn't exist - so use it, get the file details, then switch error handling off again and only proceed if the settings were captured.

Finally, for general error handling, ensure your Immediate and Locals windows are both visible. Step through your code a line at a time using F8, and see how your variables change (Locals window). Use [debug.print "variable x = ", x] to write lines of information to the Immediate window. Use [debug.assert {your test here}] to break the code when the test returns FALSE - the assertion must be true in order to continue, and this line stops when your assertion fails.

So I might reorganise a section of code as follows

For N = 1 To 5 Step 1

    On Error Resume Next
        strUrl = "": strUrl = strLink & GETIMPORTFILE(N) 'Access the url to Sharepoint and find the file
        strPath = "": strPath = sPATH & GETIMPORTFILE(N) 'Save the file to the path of the workbook and save with the same name.
        sDestSheet = "": sDestSheet = GETDESTSHEET(N)
    On Error GoTo 0


    Debug.Print "Loop " & N, strUrl, strPath, sDestSheet