0

I'm having an issue with my code:

Sub lalalala ()

        Dim s5 As Worksheet
        Set s5 = ThisWorkbook.Sheets("Test")
    
        Dim DesktopPath As String
        Dim DesktopPathMAIN As String
        Dim DesktopPathSUB As String
        Dim file As String
        
        Dim sfile As String
        Dim sDFolder As String
        
        Dim oFSO As Object
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        
        
        DesktopPath = Environ("USERPROFILE") & "\Desktop\"
        DesktopPathMAIN = DesktopPath & "THE FINAL TEST"
        
        If Dir(DesktopPathMAIN, vbDirectory) = "" Then
            Shell ("cmd /c mkdir """ & DesktopPathMAIN & """")
        End If
        
        
        lastrow = s5.Range("B" & s5.Rows.Count).End(xlUp).Row
        Set rng = s5.Range("B1:B" & lastrow)
        
        For Each c In rng
            If Dir(DesktopPathMAIN & "\" & c.Offset(, 5).Value, vbDirectory) = "" Then
        Shell ("cmd /c mkdir """ & DesktopPathMAIN & "\" & c.Offset(, 5).Value & """")
            End If
        Next c
        
        
        lastrow = s5.Range("G" & s5.Rows.Count).End(xlUp).Row
        Set rng = s5.Range("G1:G" & lastrow)
        
        For Each c In rng
        
        sDFolder = DesktopPathMAIN & "\" & c.Value & "\"
        sfile = s5.Range("H1").Value & c.Offset(, -2).Value
        
        Call oFSO.CopyFile(sfile, sDFolder)
        
        Next c           
        
        End Sub

When i run the macro it causes error code 76, but if i run again it works perfectly. I realized it happens when the cell changes the value of the destination folder at this line sfile = s5.Range("H1").Value & c.Offset(, -2).Value. But it only happens 1 time, if i run again it works perfectly.

How can i fix that?

thank you

Black Mamba
  • 247
  • 1
  • 12
  • 1
    What is the exact error-message you see with the error-code 76? – Dai Nov 05 '22 at 02:51
  • `error 76: path not found`. `sfile = s5.Range("H1").Value & c.Offset(, -2).Value`. The `s5.Range("H1").Value` is an absolute path. The `c.Offset(, -2).Value` is a relative path. The error `76` happens when the `c.Offset(, -2).Value` has a different path. But if i run again it works normally. – Black Mamba Nov 05 '22 at 03:01
  • You haven't posted **all** of your code - where is `s5` declared? Or your `Sub`/`End Sub` keywords? – Dai Nov 05 '22 at 03:02
  • `Set oFSO = CreateObject("Scripting.FileSystemObject")` <-- Don't do this. As you're using VBA (and not VBScript) you're missing out on strong-typing. You can do `Set oFSO = New Scripting.FileSystemObject` and you'll get code-completion in the editor. See here: https://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba – Dai Nov 05 '22 at 03:03
  • Yes i didn't is very big. `Dim s5 As Worksheet` `Set s5 = ThisWorkbook.Sheets("Test")` – Black Mamba Nov 05 '22 at 03:03
  • 1
    See https://www.myonlinetraininghub.com/vba-shell#:~:text=Asynchronous%20Execution,a%20text%20file%20in%20Notepad or https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function. If you're calling Shell to create a folder there's no guarantee that will be complete before you try to do anything with the new folder. VBA has `MkDir` so maybe just use that or the fso `CreateFolder` method. – Tim Williams Nov 05 '22 at 03:11
  • @TimWilliams Thank you! Changed to `mkdir` and it worked. – Black Mamba Nov 05 '22 at 03:25

0 Answers0