2 Part Question

S

Steved

Hello from Steved

1st Question

Is it possible to to a selected folder and have
a macro to open and close every workbook in that folder.

2nd question

The 1st question needs to be yes.

Can the macro have a formula in it that will allow
"find" as an example "the value would be 4444"
 
C

Chip Pearson

Steved,

Try something like

Dim FName As String
Dim FoundCell as Range
Dim WB As Workbook
ChDrive "C:" '<<< CHANGE
ChDir "C:\Test" '<<< CHANGE
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
Set FoundCell = WB.Worksheets(1).Find(what:="the value is
444")
If Not FoundCell Is Nothing Then
' do something with FoundCell
Else
' not found
End If
WB.Close SaveChanges:=True ' or False
FName = Dir()
Loop


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
S

Steved

Firstly Thankyou

I am receiving this error

Object doesn't support this property or this method

It will open the first file then the above error
Am I not understanding something here.

Sub Tests()

Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
ChDrive "C:" '<<< CHANGE
ChDir "C:\Wtt" '<<< CHANGE
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
Set FoundCell = WB.Worksheets(1).Find(what:="the
value is 2402")
If Not FoundCell Is Nothing Then
' do something with FoundCell
Else
' not found
End If
WB.Close SaveChanges:=True ' or False
FName = Dir()
Loop

End Sub
 
C

Chip Pearson

Steved,

Set FoundCell = WB.Worksheets(1).Find(what:="the

should be

Set FoundCell = WB.Worksheets(1).Cells.Find(what:="the



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top