Solved: Opening workbooks within subfolders

[cdbrown]

Hi all,

I have some code in wb1 to open each wb, copy the ws then close the wb one by one within the folder I specify. It then runs some other code to manipulate the data.

[VBA] z = ActiveWorkbook.Sheets.Count
Set wb1 = ThisWorkbook

Set ff = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select a folder", 0, "c:\\")
If Not ff Is Nothing Then
GetFolder = ff.Items.Item.Path
Else
GetFolder = vbNullString
End If
MyFolder = GetFolder
If MyFolder = vbNullString Then
MsgBox "No folder selected. Please select a folder containing the PHAST output.", vbCritical
Exit Sub
End If

With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Set wb2 = Workbooks.Open(.FoundFiles(i))
Sheets(1).Copy After:=Workbooks(wb1.Name).Sheets(z)
If ActiveSheet.Name = "DYNAMIC RESULTS" Then
Size = Cells(12, 1)
Parts = Cells(22, 2)
If Size = "Base Case" Then
Size = "2mm"
End If
ActiveSheet.Name = Size & "-" & Parts & "ppm"
End If
If ActiveSheet.Name = "MAXIMUM CONCENTRATION " Then
Size = Cells(12, 1)
Parts = Cells(20, 3)
ActiveSheet.Name = Size & "-" & Parts & "ppm"
End If
If ActiveSheet.Name = "DETAILED DISPERSION REPORT" Then
Size = Cells(11, 1)
If Size = "Base Case" Then
Size = "2mm"
End If
ActiveSheet.Name = Size
End If
Application.CutCopyMode = False
Windows(wb2.Name).Close
z = z + 1
Next i
End With
[/VBA]

I would like to make wb1 a template which gets called from another workbook (eg mainwb). I would like mainwb to get the user to select a folder which will contain a number of subfolders (eg sub1,sub2,etc). In each subfolder there is 25 workbooks. Upon selecting the main folder I want the macro to open the template wb1, send the subfolder name to that template so that it then runs the original code it has. It would be good if it would save wb1 in the main folder but named using the subfolder the information came from - however I wouldn't mind if it prompted the user to save the file manually. Then move onto the next subfolder repeating the process for each of the subfolders.

Does this make sense? and if so would it be possible?

Regards
Craig

[malik641]

The template part of this...when you call wb1, are you going to recall it for as many workbooks there are in all the subfolders?


As far as searching subfolders, just add the following:
[vba]
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
End With[/vba]
Oh yeah, and welcome

[cdbrown]

Thanks Malik. Sort of - I would like the wb1 be called for each separate subfolder. I would like to get mainwb to open the wb1 and send the necessary info to wb1 so it can open the workbooks in the subfolder.

Hopefully I've got things clearer in my mind and will try to explain a bit better the process
There will be 4 macros in the mainwb with all being very similar but will open and call slightly different templates.
macro1 and 2 - prompt user to select a particular folder. Inside this folder will be a number of subfolders (there shouldn't be any xls files directly in this folder). I want the macro to somehow select the first subfolder (not sure of the order for this) open the template (wb1) and get macrowb1 to run on the subfolder. Then prompt the user to save the output file, close wb1. Select the next subfolder, open template wb1 and get macrowb1 to run on that subfolder... and so on until all subfolders are finished.

macro3 and 4 - prompt user to select a particular folder. Inside the folder will be a number of workbooks (no subfolders). I want the macro to select the first workbook, open the template (wb2) and get macrowb2 to run on the workbook. Prompt user to save the output file, close wb2. Select the next workbook, open the template wb2 and get macrowwb2 to run on that workbook...

I am having some trouble getting the macro in the template to run from the mainwb macro as I'm not sure of the correct coding. wb1 macro is called PHAST_INPUT() and I'd seen similar code to below but it doesn't work.
[vba]Windows(wb1.Name).Activate
Application.Run (wb1.Name"!PHAST_INPUT")[/vba]
Cheers
-cdbrown

[cdbrown]

Could anybody help out with using a macro to open a wb2 and then run the
macro in wb2?

[ALe]

to open a wk2
workbooks.open("nameofthefile.xls")

to run a macro of wk2
application.run "'nameofthefile.xls'!nameofthemacro"

[cdbrown]

Ok but I only know the name of the newly opened file by
Set wb2=ActiveWorkbook

Application.Run "wb2.Name!PHAST_INPUT" - of course returns an error that it can't find wb2.Name

[ALe]

[VBA]Dim Namewkb as string
Namewkb=wb2.name
application.Run Namewkb & "!PHAST_INPUT"
[/VBA]

[cdbrown]

[VBA]
Application.Run Namewkb & "!PHAST_INPUT"
Application.Run "'Toxic Gas - Gas.xls'!PHAST_INPUT"
[/VBA]
1st one returns "Error 1004 the macro 'Toxic Gas - Gas.xls!PHAST_INPUT' cannot be found" even though it opened that file and activated the window using the wb2.Name command. 2nd works but I'm trying to avoid actually hard naming the workbooks in the macros.

Thanks for the help so far.

I'll use the filename for the time being I think to get past that hurdle. Is it possible to send a variable like the folder name to the 2nd workbook. Currently wb2 prompts a user for the subfolder, but if I can get the main macro to send it. Will also need the 2nd macro to be set up to accept it.

[ALe]

and this?
[VBA]
Application.Run "'" & Namewkb & "'!PHAST_INPUT"
[/VBA]

[cdbrown]

That new code works great. Thanks.

Slowly getting this monster to work.

[ALe]

It was related to the sign '

sorry I haven't noticed it before

[cdbrown]

well you helped alot in getting that to run the next macro.
In this code it has the search loop to check in a specified folder and for every file it opens the template (although it's not a template file yet) and then start the template's macro. I want the template's macro to open that found files ( Set wb3 = Workbooks.Open(.FoundFiles(i)) ) instead of this macro. Is this possible? Note that this isn't the problem about looking into subfolders.

Code in main macro
[VBA] With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:="K:\Jet Fire - Gas.xls"
Set wb2 = ActiveWorkbook
Namewkb = wb2.Name
'Set wb3 = Workbooks.Open(.FoundFiles(i))
Windows(wb2.Name).Activate
Application.Run "'" & Namewkb & "'!OpenF" 'possible to add an argument to send the filename of FoundFiles(i) to OpenF for use??
Windows(wb2.Name).Close
z = z + 1
Next i
End With
[/VBA]

Code in OpenF
[VBA]
Set wb2 = ThisWorkbook
Application.ScreenUpdating = False
Set wb3 = Workbooks.Open(.FoundFiles(i)) 'i added this and don't think it works

'this copies the 2nd sheet in the new workbook and places it after the 1st sheet of the original
Sheets(1).Copy After:=Workbooks(wb2.Name).Sheets(3)

[/VBA]

[ALe]

you can't do [VBA]Set wb3 = Workbooks.Open(.FoundFiles(i)) [/VBA]

'cuz it won't be seen by OpenF

I suggest you store the name of .foundfiles(i) in a cell of the file containing the main macro and then you get it once you've opened the OpenF

[cdbrown]

I don't think I can say it enough but thank you thank you thank you thank you thank you thank you thank you.
I have successfully been able to run 1 of the macros which prompted the user for a folder, then open the template, run the template macro by opening up a file in that folder, save the results, close and move onto the next file. I can now apply that to 2 other subs.

Do you think it's possible for the main macro to save the folder name instead of the foundfiles(i)? Maybe return something from the SearchSubfolders? I've got it set up so the other macro will open the subfolder and work on the files in there so I just want the main macro to stay in the main folder.

[vba] With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Sheets("List").Select
ActiveSheet.Cells(2, 6).Value = .FoundFiles(i)
[/vba]

[cdbrown]

With some help in other threads as well as a little rethink of the overall plan, I now get the user to input some project information which I can use as part of the subfolder paths. This brings up a little problem in that some cases the relationship will exist but the folder does not (because it doesn't have to), but from the following code the template and it's macro is run which then crashes because it can't open any files.

[VBA]For x = 0 To Rows - 1
If Cells(7 + x, 6) = "G" Then
Folder = Cells(7 + x, 8)
Sheets("List").Select
Cells(5, 6).Value = Cells(4, 6) & "\" & Folder
Workbooks.Open Filename:="K:\Phast.xls"
Set wb2 = ActiveWorkbook
Namewkb = wb2.Name
Windows(Namewkb).Activate
Application.Run "'" & Namewkb & "'!PHAST_INPUT"
Windows(wb2.Name).Close
Sheets("Relat").Select
End If
Next x
[/VBA]
Is it possible to put in a check to see whether the folder exists - and if it doesn't moves to the next loop cycle? Cells(5,6) on "List" is a full path of the subfolder which could be used in the check.

Also I have this to create a new folder and save the JF wb. Is it possible to check that if it's already been created just to save the file in that subfolder, but if not create the subfolder and save?
[VBA]
Sheets("List").Select
Fname = Cells(1, 1)
Fname2 = Folder & "\" & Fname
MkDir Fname2
Sheets("Results").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:=Fname2 & "\JF - " & Fname
[/VBA]

Everyone's help has been invaluable so thank you very much.

Cheers
-cdbrown

[ALe]

[VBA]Function ChkFolderExists(strFolderName As String) As Boolean
'vero se la cartella passata esiste
On Error GoTo ErrHandler:
If Dir(strFolderName) <> "" Then
ChkFolderExists = True
Else
ChkFolderExists = False
End If
Exit Function
ErrHandler:
ChkFolderExists = False
End Function[/VBA]

[VBA]if chkfolderExists=false then goto Skip[/VBA]

Insert Skip: just before the end of the loop

[cdbrown]

I'm sorry but I don't quite understand what the above will do.

I would like to check if the path captured in Cell(5,6) at the start of the loop actually exists. If it does then carry on with the rest of the code in the loop, if not then go to Next (skipping over opening the Phast.xls and running it's macro). Do I put
If chkfolderExists=False Then Goto Skip

After the
Cells(5, 6).Value = Cells(4, 6) & "\" & Folder

At the end of the macro within Phast.xls it creates a new folder and saves the file in there. Would like to skip over the MkDir line if the path already exists.

[ALe]

[VBA]For x = 0 To Rows - 1
If Cells(7 + x, 6) = "G" Then
Folder = cstr(Cells(7 + x, 8).value)
if chkfolderexists(Folder)=false then goto Skip
Sheets("List").Select
Cells(5, 6).Value = Cells(4, 6) & "\" & Folder
Workbooks.Open Filename:="K:\Phast.xls"
Set wb2 = ActiveWorkbook
Namewkb = wb2.Name
Windows(Namewkb).Activate
Application.Run "'" & Namewkb & "'!PHAST_INPUT"
Windows(wb2.Name).Close
Sheets("Relat").Select
End If
Skip:
Next x[/VBA]

[cdbrown]

Thanks for that. I needed to change it a little as it's the string in Cells(5,6) that is the path I want to check for. I get it do do the check up it automatically goes straight to false when in the function as if it's not even checking.

[VBA]
For x = 0 To Rows - 1
If Cells(7 + x, 6) = "G" Then
CFolder = Cells(7 + x, 8)
Sheets("List").Select
Cells(5, 6).Value = Cells(4, 6) & "\" & CFolder
Folder = CStr(Cells(5, 6).Value)
If ChkFolderExists(Folder) = False Then GoTo Skip
Workbooks.Open Filename:="K:\Phast.xls"
Set wb2 = ActiveWorkbook
Namewkb = wb2.Name
Windows(Namewkb).Activate
Application.Run "'" & Namewkb & "'!PHAST_INPUT"
Windows(wb2.Name).Close
End If
Skip:
Sheets("Relat").Select
Next x[/VBA]

I've tried both strFolder and strFolderName.
[VBA]Function ChkFolderExists(strFolder As String) As Boolean

On Error GoTo ErrHandler:
If Dir(strFolder) <> "" Then
ChkFolderExists = True
Else
ChkFolderExists = False
End If
Exit Function
ErrHandler:
ChkFolderExists = False
End Function[/VBA]
When in debug I can put the mouse over strFolder and small text appears showing

strFolder = "K:\Output\TG\1\2"

This folder does exist however the ChKFolderExists = False each time.

Is there something wrong with the syntax because we know that the function has the strFolder as the correct path?

[ALe]

strFolder must have "\" at the end