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

 

[Siyanna]

That exactly what i was after. The formula is so much easier in vba than the spreadsheet

Basically when looping through in my code to copy a file over, i want to also check to see if the file i am currently looping over is open, if it is then dont copy it and carry on with the next file

ps Does DIR function give me the same thing ie full path "C:\Users\Helal\Desktop\Excel Test\Old Folder" or "OldFolder"

 

[RoryA]

Dir would only give the file/folder name not full path.

You can use a simple error handler:

Dim wb as Workbook
on error resume next
set wb = workbooks("some workbook name")
on error goto 0
If not wb is nothing then msgbox "workbook is open"

 

[Siyanna]

Thank you Rorya (LEGEND)

I am getting a null string with this, i expected it to return OLd Folder

OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"
FolName = Dir(OldFolderPath)

 

[Siyanna]

Hi Rorya - i am nearly there

i have a quick question with this amended code - it works but when i step through the code, i get this file name test2.xls but when i run again, i get ~$test.xls

what on earth is that and how can i overcome this?

thank you

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)


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

 

[RoryA]

Part 1:

OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"
FolName = Dir(OldFolderPath, vbDirectory)

Part 2:
That's the name of the temp file that is created when you open the test.xls workbook. Check the file name and if it starts with a tilde (~), skip it.

 

[Siyanna]

Hi RoryA

I guess i had a few more q's posted after my initial post so i will put it into 1. Thank you you and when you are free can you just explain these please

2. I have a quick question with this amended code - it works but when i step through the code, i get this file name test2.xls but when i run again, i get ~$test.xls

what on earth is that and how can i overcome this?

Dim fil As Scripting.File
Dim OldFolder As Scripting.Folder
Dim SubFol As Scripting.Folder

Set OldFolder = fso.GetFolder(StartFolderPath)


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

3. With this line of code Set wb = Workbooks(fil.Name)
say i had another filename exactly the same from another folder and that was open (so say i had test2 in Old Folder and in VBA FILES FOLDER)

Would the fil.Name ignore the VBA FILES FOLDER FILE (test2) which i would need to do or would it treat it as the same Test2 for both files

If yes then do i need to specify full Path and file name in Set wb = workbooks(?) and which is the easiest way to do this? i tried OldFolder & "\" & fil.name but this dont work

Thank you so much once again

 

[Siyanna]

What does this part do - vbDirectory

 

[Siyanna]

Part 2:
That's the name of the temp file that is created when you open the test.xls workbook. Check the file name and if it starts with a tilde (~), skip it.

Would i need to write some code to check for the "~" also?

 

[RoryA]

1&2 already answered.

3. No, you'd need a test on the path:

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

 

[RoryA]

vbDirectory specifies that you are looking for a folder, not a file.