PDA

View Full Version : [SOLVED] Iterate Directory's Opening Workbook



jo15765
06-11-2014, 08:46 AM
I want to go through my C: drive and all subfolders looking for any file with a .xls extension, open the file and find the word "Jo Mitchell" and replace it with "Joe Mitchell" how can this be achieved via VBA?

ranman256
06-11-2014, 11:36 AM
yes, Google "Scripting.FileSystemObject" to get all and any file /folder functions.
But I'm afraid you'd have to open the file, search the file, then close it.
BETTER may be to run this in windows (Windows already does this in the SEARCH function)
send the results to a file.

(you may even have to use the old DOS command and pipe the results to a file)
Dont re-invent the wheel.

jo15765
06-11-2014, 11:51 AM
yes, Google "Scripting.FileSystemObject" to get all and any file /folder functions.
But I'm afraid you'd have to open the file, search the file, then close it.
BETTER may be to run this in windows (Windows already does this in the SEARCH function)
send the results to a file.

(you may even have to use the old DOS command and pipe the results to a file)
Dont re-invent the wheel.

Unfortunately I need to open each file as I need to do a find/replace on some text within the file. I found a recursive file search code, I just need to figure out a way to pass file names to open to the recursive search. This is what I found


sPath = "C:\"
Set myFolder = FSO.GetFolder(sPath)
For Each mySubFolder In myFolder.SubFolders
For Each myFile In mySubFolder.Files
If myFile.Name = .........This is where I will have to get creative on passing an array to this
Set openedWB = Workbooks.Open(....Open the array name that is above)
.....Do A Find Replace
Exit For
openedWB.Save
openedWB.Close
End IF
Next
Next

ranman256
06-11-2014, 11:55 AM
That looks 'bout right.

jo15765
06-11-2014, 11:59 AM
That looks 'bout right.

Any suggestions on how to pass an array of fileNames to the If myFile.Name = line of code?

So for example if the array of file names to check was "Monday", "Tuesday", "Wednesday" how could I have the If statement check against an array?

snb
06-11-2014, 01:27 PM
sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\*.xls /b/s/a").stdout.readall,vbcrlf)

For j=0 to ubound(sn)-1
with getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.close -1
end with
next
End Sub

jo15765
06-11-2014, 01:53 PM
sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\*.xls /b/s/a").stdout.readall,vbcrlf)

For j=0 to ubound(sn)-1
with getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.close -1
end with
next
End Sub

WHOA....now when I try to open those Excel files that it replaced in it just opens Excel and will not actually open the workbook itself anymore?

snb
06-11-2014, 01:58 PM
It does, look at tab 'View' / unhide.

jo15765
06-11-2014, 02:00 PM
It does, look at tab 'View' / unhide.

Is there a way to set it by default to not hide the sheet? As it would take me quite some time to manually change all of the workbooks back to normal?

snb
06-11-2014, 03:11 PM
Please do not quote !

Yes, you will have to amend the code I posted.

jo15765
06-11-2014, 07:07 PM
M8 - you have me totally stumped on how to keep the sheet visible. I tried changing the DOS commands at the top and nothing, I tried to change the close - 1 (changed to multiple numbers) and nothing, I even attempted to add in .Sheets(1).Hide = False & .Sheets(1).Visible = True And STILL nothing!

snb
06-12-2014, 03:28 AM
Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\*.xls /b/s/a").stdout.readall,vbcrlf)

For j=0 To UBound(sn)-1
With getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.visible=true
.close -1
End With
Next
End Sub

jo15765
06-12-2014, 07:07 AM
I am getting a Run-time error '438' Object doesn't support this property or method on the
.Visible = TrueAnd I also tried to use
.Sheets(1).Visible = True which does not throw the error, but still leaves the sheet hidden as well.

EDIT --
And if it matters this is Excel 2007 running Win 7 64 bit

snb
06-12-2014, 09:38 AM
Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\*.xls /b/s/a").stdout.readall,vbcrlf)

For j=0 To UBound(sn)-1
With getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.application.visible=True
.close -1
End With
Next
End Sub

jo15765
06-12-2014, 10:18 AM
That shows the Excel Workbook while making the replace but does not actually keep the worksheet visible.

EDIT --
I also tried to couple that with a macro to unhide sheets and fail.

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

For j=0 To UBound(sn)-1
With getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.application.visible=True
Call UnhideWorksheets
.close -1
End With
Next
End Sub
Public Sub UnhideWorksheets()
Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

snb
06-12-2014, 12:28 PM
Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\*.xls /b/s/a").stdout.readall,vbcrlf)

For j=0 To UBound(sn)-1
With getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.windows(1).visible=True
.close -1
End With
Next
End Sub

jo15765
06-12-2014, 01:04 PM
By George you got it! Thank you so so so much. Speed wise, how fast is this procedure? I have a directory over 4 GB that I will be running this procedure on and was just curious guestimation of how long it would take.

snb
06-12-2014, 02:55 PM
Without the visibility element it is fast as lightning, since the screen won't be bothered to perform/adjust/show anything.

You better create a macro to make hidden workbooks visible after they have been opened.
I have no clue how fragmented/defragmented your disk is.

jo15765
06-12-2014, 04:10 PM
grr....I am forced to use the visibility parameter due to the workbook needing to be visible when opened (as it will be opened by others not just myself)

Edit --
@ any rate tho, thank you for all your help!

jo15765
06-12-2014, 05:26 PM
Okay --- 2 more ? and I am done I promise, lol. What would I need to do if I needed to search a directory with spaces in the filename? I tried to add in the %20 between the spaces but it just ended. What if my directory was C:\User\User 0123\ ---- and can I set it to ignore prompts? For instance there are a few workbooks that are pswd protected can I set it to just click okay or to keep on moving past that workbook w/o erroring?

Or if I should open a new thread for that, I can do that also.

snb
06-13-2014, 01:28 AM
As far as I know your question was about searching a drive (it's name can't contain a space by definition),
- You will have to add some quotationmarks in the command.
- You can't check beforehand whether a workbook is password protected or not. To prevent errors when opening passwordprotected files you can introduce 'on error resume next'


Sub M_snb()
on error resume next
sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\*.xls"" /b/s/a").stdout.readall,vbcrlf)

For j=0 To UBound(sn)-1
With getobject(sn(j))
.sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"
.windows(1).visible=True
.close -1
End With
Next
End Sub

jo15765
06-14-2014, 08:27 AM
This is by far the fastest method I have seen, but the problem is when a pswd protected workbook is opened it will sit there and wait for the password to be entered. The 'On Error Resume Next' statement is not avoiding it?


EDIT -
It looks ilke this syntax is treating RAR files that contain Excel files as a subdirectory which is why I am getting the password prompt. Is there a way to exclude the .rar files from the search (or ignore a file with a password)?

snb
06-14-2014, 09:27 AM
If Not .Sheets(1).Protection Is Nothing Then .sheets(1).cells.replace "Jo Mitchell","Joe Mitchell"

At least on my system zip files won't be considered to be subdirectories.
Did you use ""C:\ *.xls "" in the command ?