Why am i getting runtime error '91' withblock not set?

[Siyanna]

Hi,

Confused to why i am getting this error

I'm just trying to loop over all excel files and copy them but i get an error in the highlighted part in bold

Thank You

Dim fso As Scripting.filesystemobject
Dim NewFolderPath As String


Sub CreateFolder()


Dim fso As Scripting.filesystemobject
Set fso = New Scripting.filesystemobject


Dim OldFolderPath As String


NewFolderPath = "C:\Users\Helal\Desktop\Excel Test\New Folder"
OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"


    If fso.FolderExists(NewFolderPath) Then
        MsgBox "It exists"
    Else
        fso.CreateFolder (NewFolderPath)


    End If


Call CopyExcelFiles(OldFolderPath)




End Sub


Sub CopyExcelFiles(StartFolderPath As String)


Dim fil As Scripting.File
Dim OldFolder As Scripting.Folder
Dim SubFol As Scripting.Folder
 
[U][B]Set OldFolder = fso.GetFolder(StartFolderPath)[/B][/U]


For Each fil In OldFolder.Files
    If Left(fso.GetExtensionName(fil.Path), 2) = "xl" Then
       If fso.FileExists(NewFolderPath & "\" & fil.Name) Then
          MsgBox "File Already Exists"
       Else
          fil.Copy NewFolderPath & "\" & fil.Name
       End If
    End If
Next


For Each SubFol In OldFolder.SubFolders
    Call CopyExcelFiles(SubFol.Path)
Next


End Sub

 

[RoryA]

You could use something like:

If Left(fso.GetExtensionName(fil.Path), 2) = "xl" And Left$(fil.name, 1) <> "~" Then

 

[Siyanna]

Thank you - i thought i may need to look at the path also so it dont get mixed up with the same file name in the wrong directory

Many Many Many Many Thanks


Left$<< what does the $do after the left</pre>

 

[Siyanna]

Last 1


aint this line of code the same as below? Set wb = Workbooks(OldFolder.path & "\" & fil.Name)


Set wb = Workbooks(fil.Name)
On Error GoTo 0
If Not wb Is Nothing and wb.Path = OldFolder Then
MsgBox "workbook is open"
end if

i thought i could do in 1 line of code but obv that dont work

 

[RoryA]

The workbooks collection is only indexed by file name, not including the path.

 

[Siyanna]

Thank you


Left$<< what does the $do after the left

 

[RoryA]

It's the String version of the Left function - it returns a String rather than a Variant.

 

[Siyanna]

---

Thank you

ahhh so if I had this

dim myvar as string

myvar = Left$(fil.name, 1)

would I need $ as I have defined Myvar as string?

where can I get a list of all these shorthand dims

 

[RoryA]

You don't ever need to use Left$ rather than Left. It's not a Type declaration character like using:

Dim myvar$

 

[Siyanna]

Oh ok but I guess it's good practice to get used to it so the left function knows its working with a string

where do I get a list of the shorthand type declarations?

So I guess to be extra, with all functions used, u could do
instr$ or right$ or len$ to show that you are working with a string ?

 

[Siyanna]

Hi RoryA,

Can you please advise why this move method wont work, i am just trying to move OldFolder to the NewFolder Created

I added the line of code in Bold but it says it already exists in that folder when it does not

Dim fso As Scripting.filesystemobject
Dim NewFolderPath As String


Sub CreateFolder()


Set fso = New Scripting.filesystemobject


Dim OldFolderPath As String


NewFolderPath = "C:\Users\Helal\Desktop\Excel Test\New Folder"
OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"




    If fso.FolderExists(NewFolderPath) Then
        MsgBox "It exists"
    Else
        fso.CreateFolder (NewFolderPath)
        


    End If


Call CopyExcelFiles(OldFolderPath)




End Sub


Sub CopyExcelFiles(StartFolderPath As String)


Dim fil As Scripting.File
Dim OldFolder As Scripting.Folder
Dim SubFol As Scripting.Folder
 
Set OldFolder = fso.GetFolder(StartFolderPath)
[U][B]OldFolder.Move NewFolderPath[/B][/U]


For Each fil In OldFolder.Files
    If Left(fso.GetExtensionName(fil.Path), 2) = "xl" Then
       If fso.FileExists(NewFolderPath & "\" & fil.Name) Then
          MsgBox "File Already Exists"
       Else
          Dim wb As Workbook
          On Error Resume Next
          Set wb = Workbooks(fil.Name)
          On Error GoTo 0
             If Not wb Is Nothing Then
                MsgBox "workbook is open"
             Else
                fil.Copy NewFolderPath & "\" & fil.Name
             End If
       End If
    End If
Next


For Each SubFol In OldFolder.SubFolders
    Call CopyExcelFiles(SubFol.Path)
Next


End Sub