A
Alex
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
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