Code to open up files in folder and sub-folder

[Howardc]

I Have the folowing code where I want to open up all files in a folder C:\pull as well as all the folders within C:\pull where the name contains ACCNTS(P). These are all XLS files

See code below, which does nothing when acticated


Option Explicit

Sub Open_Files_Pull()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String

Set fso = CreateObject("scripting.FileSystemObject") ' late binding
'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

Set fldStart = fso.GetFolder("C:\Pull") ' <-- use your FileDialog code here

Mask = "(p)*.xls"
Debug.Print fldStart.Path & "\"
ListFiles fldStart, Mask
For Each fld In fldStart.SubFolders
ListFiles fld, Mask
ListFolders fld, Mask
Next
End Sub


Sub ListFolders(fldStart As Object, Mask As String)
Dim fld As Object 'Folder
For Each fld In fldStart.SubFolders
Debug.Print fld.Path & "\"
ListFiles fld, Mask
ListFolders fld, Mask
Next

End Sub

Sub ListFiles(fld As Object, Mask As String)
Dim fl As Object 'File
For Each fl In fld.Files
If fl.Name Like Mask Then
Debug.Print fld.Path & "\" & fl.Name
End If
Next
End Sub

[snb]

Sub M_snb()
c00="C:\Pull\" & replace(createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*.xls"" /a/b").stdout.readall,vbcrlf,vbcrlf & "C:\Pull\") & vbcrlf
sn=split(c00 & createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*ACCNTS(P)*.xls"" /s/a/b").stdout.readall,vbcrlf)

for j=0 to ubound(sn)
msgbox sn(j)
next
End sub

[Howardc]

Thanks for the reply

When running your code, I get "Compile Error: Syntax error" and the following code is highlighted


c00="C:\Pull\" & replace(createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*.xls"" /a/b").stdout.readall,vbcrlf,vbcrlf & "C:\Pull\") & vbcrlf
sn=split(c00 & createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*ACCNTS(P)*.xls"" /s/a/b").stdout.readall,vbcrlf)

Kindly test & amend code

[snb]

Why don't you ?

[Howardc]

Unfortunately, I don't understand your code

I have tried to write my own code.

If I copy the files from the sub-folders to C:\extract it works, but the code does not open the files containing the name ACCNTS (P) from the sub-folders

It would be appreciated if you could amend my code as i'm battling to code the code sorted out to open these files in the sub-folders within C:\extract



Sub Open_Files()

Sheets("Workspace").Select

Dim rngCell As Range
Dim zFPath As String
Dim zFSpec As String
Dim Folder As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
zFPath = "C:\extract\"
Folder = Dir(zFPath, vbDirectory)

For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
zFSpec = zFPath & Folder & rngCell.Value & ".xls"
Workbooks.Open zFSpec

Next rngCell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[Ingolf]

You should post a link to the other thread so other posters know what others have tried to help with

list files in folder and sub-folder (http://www.mrexcel.com/forum/excel-questions/800934-list-files-folder-sub-folder.html#post3917019)
Open up workbooks in folder and sub-folders (http://www.mrexcel.com/forum/excel-questions/801121-open-up-workbooks-folder-sub-folders.html)

and here:
Opening up files in folder and sub-folder | Windows Secrets Lounge (http://windowssecrets.com/forums/showthread.php/163895-Opening-up-files-in-folder-and-sub-folder)

[alansidman]

Howard;

Please read this link:

http://www.excelfox.com/forum/f25/message-to-cross-posters-1172/#post5326

[Howardc]

My apologies-have read the link to Cross Posters and agree 100% with the sentiment. Will post solution on all the sites for once found, where I have cross posted


My code is partly working.