Hello guys,

I wonder if it's possible to make a macro that will open all excel
files from the directory where the file I'll use for macro launching is
stored.

F. ex.: If I put my excel file with macro on desktop (and then run the
macro), it'll open all excel files that are on desktop. Then, if I'll
move this file to the folder D:/My Folder and re-run the macro, it'll
open all the excel files that are in D:/My Folder.

So, the goal would be to modify the below-mentionned macro in its
fourth line
---------------------------------------------------
Sub openAllfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "D:\My Folder"

'= instead of a precise, fixed location, it should give "location
where the current excel file is
'saved"

..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook

'= Probably I should put here some "if" code in order not to re-open
the file I'm currently using,
'like If wbk.Name < ThisWorkbook.Name /.../ End If
'can you confirm?

Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
Next i
End With
End Sub
---------------------------------------------------

Thanks for any help on this...
Mark

Private Sub OpenFiles()
Dim sPath as string, sName as String
sPath = thisworkbook.Path
if right(sPath,1) < "\" then _
sPath = sPath & "\"
sname = dir(sPath & "*.xls")
do while sName < ""
if sName < thisworkbook.name then
workbooks.Open sPath & sName
end if
sName = dir()
Loop
end Sub

Call this from the workbook.Open event if you want it to be fired when the
workbook is opened.



--
Regards,
Tom Ogilvy

" wrote:

Hello guys,

I wonder if it's possible to make a macro that will open all excel
files from the directory where the file I'll use for macro launching is
stored.

F. ex.: If I put my excel file with macro on desktop (and then run the
macro), it'll open all excel files that are on desktop. Then, if I'll
move this file to the folder D:/My Folder and re-run the macro, it'll
open all the excel files that are in D:/My Folder.

So, the goal would be to modify the below-mentionned macro in its
fourth line
---------------------------------------------------
Sub openAllfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "D:\My Folder"

'= instead of a precise, fixed location, it should give "location
where the current excel file is
'saved"

..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook

'= Probably I should put here some "if" code in order not to re-open
the file I'm currently using,
'like If wbk.Name < ThisWorkbook.Name /.../ End If
'can you confirm?

Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
Next i
End With
End Sub
---------------------------------------------------

Thanks for any help on this...
Mark