0

This is a follow up to this question and great answer:

Copy files with progress bar

So I added the code from Siddharth Rout's answer and it does exactly what I want to happen with a minor exception. When I copy the files, I am looping through each file in the directory and copying it up as long as it is not *List.xml. Because I am replacing an existing library the 97% of the documents are pre-existing and I get prompted to replace existing documents each time.

Is there a way to get it to prompt me to choose to replace for all files? Do I need to reformat/structure the sequence of my code?

Function UploadToSharepoint(Folderpath As String, Foldername As String, Filenames() As String, SharepointLinks() As String) As Boolean

    'upload file to sharepoint library based on the folder name
    Dim SharePointLib As String
    Dim LocalAddress As String
    Dim DestinationAddress As String
    Dim xCounter As Long
        
    On Error GoTo loadFailed
    
Pickafolder:
    
    Folderpath = FolderPick
    
    Foldername = Left(Folderpath, Len(Folderpath) - 1)
    Foldername = RIght(Foldername, Len(Foldername) - InStrRev(Foldername, "\"))
    
    Select Case Foldername
        Case "OPSS", "SSP", "OPSD", "MTOD", "SSD"
        
            SharePointLib = "\\my.company.com\Subsite\" & Foldername & "\"
            
        Case "West", "Eastern", "Northeastern", "Northwestern", "Head Office"
        
            SharePointLib = "\\my.company.com\Subsite\NSP\" & Foldername & "\"
            
        Case "NSP", "NSSP"
        
            MsgBox "Pick the NSP regional sub folder:  West, Eastern, Northeastern, Northwestern, Head Office"
            GoTo Pickafolder
        
        Case Else
            
            MsgBox "Inappropriate directory to upload from. Please select one of the CPS download directories"
            GoTo Pickafolder
     
     End Select
      
    Filenames = GetFilesDir(Folderpath)
    ReDim SharepointLinks(LBound(Filenames) To UBound(Filenames))
    
    For xCounter = LBound(Filenames) To UBound(Filenames)
        
        LocalAddress = Folderpath & Filenames(xCounter)
        DestinationAddress = SharePointLib & Filenames(xCounter)
'**********************************************************    
        Call VBCopyFolder(LocalAddress, DestinationAddress)
'**********************************************************        
        SharepointLinks(xCounter) = "#http:" & Replace(DestinationAddress, "\", "/") & "#"
        
    Next xCounter
    
    UploadToSharepoint = True
    
    Exit Function
    
loadFailed:
    UploadToSharepoint = False

End Function

And by the looks of things I am not excluding the file I was referring to earlier...must be doing that else where.

Update

Based on comment received at the linked question, the solution is to declare a public constant at the start:

Public Const FOF_NOCONFIRMATION As Long = &H10

and then in the copy procedure change the line of code to:

.fFlags = FOF_SIMPLEPROGRESS Or FOF_NOCONFIRMATION

Now, this does solve the problem of being constantly asked to confirm the replacement. I am very happy about this. The problem now is the progress window displays for the first file to be copied then disappears but fails to reappear for subsequent files. The remaining files still get copied and the prg carries on like it's supposed to. The whole point of the progress bar though was to let people know that "THINGS" were still happening in the background and now that is not happening. Is there something I need to adjust?

Update 2

After running my code and choosing a source directory on the network drive instead of the local computer, the copy window is popping up for every single file like I was expecting. I notice that sometimes the progress bar closes before reaching 100%. This leads me to believe that since the file sizes are so small that when it is copying from my local drive to sharepoint, the operation completes so fast that it does not have time to draw and update the progress window before its time to close it.

Community
  • 1
  • 1
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • 1
    The documentation for using the `SHFILEOPSTRUCTA` of the `SHFileOperationA` system calls out in the [REMARKS section](https://learn.microsoft.com/en-us/windows/desktop/api/shellapi/ns-shellapi-_shfileopstructa#remarks) that *"You must ensure that the source and destination paths are double-null terminated."* Do normal VBA `String` types guarantee this termination? – PeterT May 30 '19 at 19:54
  • Thanks for this information @PeterT. I am a relative beginner for VBA code. I do not even know what a double-null termination CURRENTLY is. But now I know something to look into. I also did not know that what I was doing from the previous question was a system call out. Defining a type was a new experience for me. I will be reading that remarks section shortly and googling double null too. Thank you again! – Forward Ed May 30 '19 at 20:05
  • 1
    You can guarantee a double-null at the end of a string by using `myString = myString & vbNullChar & vbNullChar`. See [this page](https://bettersolutions.com/vba/strings-characters/builtin-constants.htm). I can't say this is the problem in your code, but it's something to investigate. – PeterT May 30 '19 at 20:21
  • @PeterT I was thinking about the concept of doing exactly what you are showing. I did not know the exact syntax so I was looking stuff up. I stumbled on to [this Q](https://stackoverflow.com/questions/5630961/null-values-for-variables-in-vba) which would seem to indicate that you cant have a null in a string. this still apply?, I can always modify my code to include the null null when the string is finally used for the copy. Or as stated in the question use Variant instead of String. – Forward Ed May 30 '19 at 20:39
  • It turns out that VBA `String` variables ***are*** double-null terminated (see [this article on VBA Internals](https://bytecomb.com/vba-internals-string-variables-and-pointers-in-depth/)). While I'm not 100% certain, I believe that passing a `String` variable to the `SHFileOperationA` function will access the double-null terminated string. So that's probably not your problem. Interesting to investigate though. I'm learning something :) – PeterT May 30 '19 at 21:10
  • @PeterT doenst that article say the string has a 2 byte Null at the end? Not two 1 byte nulls? ie wouldn't you need `mystring & vbNullChar`? – Forward Ed May 30 '19 at 22:32
  • I think it's dependent on the `SHFileOperation` function and whether it's expecting 16-bit ASCII/UNICODE string or the 8-bit ASCII coded string with two single-byte nulls. I didn't dig into the documentation deep enough. – PeterT May 30 '19 at 22:51

0 Answers0