open and close all excel files in directory

[Guest]

As the main (referred) file's name has been changed I need to do some (the
same) replacement for all excel files in the different directories (change
reference in the vlookup formula). I can create/enter manually somewhere and
use the list of the files (but it can be very big) to use the actual files'
names to open the file then do the replacement and close it.

But, probably there is some way can exist to open all excel files from the
given directory one by one.

Can anybody advise whether it's possible?

Thanks

Function fnOpenFile …
On Error GoTo ErrorHandling_Err
Dim strOpenFile As String
Dim xlapp As New Excel.Application
Dim xlbook As New Excel.Workbook

xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False

‘*********** some loop ??? **************

strOpenFile = ???

‘**************************************

Set xlbook = xlapp.Workbooks.Open(strOpenFile)

‘***************Do some replacement***************
……
‘******************************************
xlapp.ActiveWorkbook.Save
xlapp.Quit

ErrorHandling_Err:
If Err Then

xlapp.AskToUpdateLinks = True
DoCmd.SetWarnings True
xlapp.DisplayAlerts = True
fnProductOpen = False
MsgBox "Error number " & Err.Number & ": " & Err.Description
logFileIsOpend = FileLocked(strOpenFile)

If logFileIsOpend = False Then
' Nothing
Else
xlapp.ActiveWorkbook.Save
xlapp.Quit
End If
Resume ErrorHandling_Exit
End If
End Function

 

[Tom Ogilvy]

Dim sName as String, bk as Workbook
sName = dir("C:\MyFolder\*.xls")
do while sName <> ""
set bk = workbooks.open("C:\MyFolder\" & sName)
' process bk

bk.close Savechanges:=True
' or bk.SaveAs "C:\MyNewFolder\" & sName
sName = Dir()
Loop

Is some basic code that will open each workbook in a directory

 

[Guest]

Thank you very much, Tom.
Everything is working perfectly.