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]

Remove this line:

Dim fso As Scripting.filesystemobject

from the CreateFolder routine.

 

[Siyanna]

Thank you Rorya

you are a star

i knew it was something simple

so i guess it declared it twice which caused the error

ps

with this part of the code

OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"
Set OldFolder = fso.GetFolder(StartFolderPath)
would i be right in saying that by setting the folder path to the variable oldfolder, this gives the option to loop though andf get all attributes in the folder called old folder?

i guess oldfolder path is a string so i cant get its properties but putting that in an object allows me to get attributes?

thanks

 

[RoryA]

Correct - GetFolder returns an actual Folder object that has properties you can manipulate.

 

[Siyanna]

Thank You

So in my case, i get to work with the folder called Old Folder

Is there a way or function etc so i can without amending my string variable, get the previous folder and work from there

i.e

OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder" <<<<start from folder Excel Test without amending the variable or changing it?

would the only way to to this mean i have to create another variable?

 

[RoryA]

Sorry - not following you. What do you want to do?

 

[Siyanna]

Sorry - not following you. What do you want to do?

Sorry


I meant how do I use the variable old folder to be set to from the oldfolderpath but the previous folder

so instead of starting from folder old folder, start from excel test

 

[RoryA]

Do you mean something like this:

Set OldFolder = fso.GetFolder(OldFolderPath).ParentFolder

?

 

[Siyanna]

Yep -

Thank You

How would i do this in VBA with using a formula rather than ParentFolder? I have seen the Instr and Len used but cant figure it out

The Formula version in a spreadsheet would be =MID(SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))+1,255)

I am sure there is an easier way to write a vba formula to give me everything before the last "\"

Ps

Everything is working fine thanks your help, is there any chance you could help me to add some code to see if the file is open

I am using the folder as an example but i guess i am just trying to get everything before the "\" or even maybe a paticular occurence of "\"

 

[RoryA]

OldFolderPath = "C:\Users\Helal\Desktop\Excel Test\Old Folder"
parentPath = left$(OldFolderPath, instrrev(oldfolderpath, "\") - 1)

for example will get the parent folder path.

What file?