Excel Concept Question

[FontanaS]

Hello,

I have a question. I have an excel worksheet that will have a macro that opens up 9 other workbooks in a specified directory. I will also know the names of the 9 workbooks that need to be opened.

In the macro, is it easier to write out the filenames and path to open, or is there a way to open all files in a certain directory (using some kind of for loop)?

I was intially thinking to just write 9 open statements in the code since i know the path and filename. Only problem, that's static and if they ever add another filename in the future, I will have to add it to the code (but they say they won't)

THANKS.

 

[Sam6284]

This function returns all files in a given directory. You might be able to adapt the following for your needs by replacing the Debug statement with the statement to open the workbook. You might want to perform a test first to see if the current file is indeed an XLS file.
[tt]
Function FileList()
Dim strPath As String, sFileName As String
sPath = "C:\Windows\Desktop\" ' Set the path.
sFileName = Dir(sPath, vbNormal) ' Retrieve the first entry.
Do While sFileName <> &quot;&quot; ' Start the loop.
'Ignore the current directory and the encompassing directory.
If sFileName <> &quot;.&quot; And sFileName <> &quot;..&quot; Then
If (GetAttr(sPath & sFileName) And vbNormal) = vbNormal Then
Debug.Print sFileName
End If
End If
sFileName = Dir ' Get next entry.
Loop
End Function
[/tt]
Hope this helps.

 

[Sam6284]

This function returns all files in a given directory. You might be able to adapt the following for your needs by replacing the Debug statement with the statement to open the workbook. You might want to perform a test first to see if the current file is indeed an XLS file.
[tt]
Function FileList()
Dim strPath As String, strFileName As String
strPath = &quot;C:\Windows\Desktop\&quot; ' Set the path.
strFileName = Dir(sPath, vbNormal) ' Retrieve the first entry.
Do While strFileName <> &quot;&quot; ' Start the loop.
'Ignore the current directory and the encompassing directory.
If strFileName <> &quot;.&quot; And strFileName <> &quot;..&quot; Then
If (GetAttr(strPath & strFileName) And vbNormal) = vbNormal Then
Debug.Print strFileName
End If
End If
strFileName = Dir ' Get next entry.
Loop
End Function
[/tt]
Hope this helps.

 

[fneily]

Why don't you just make a Workspace? Initially open all 9 files, arrange them as you want, then do File, Save as Workspace. Give it a name and save it. Then the next time you enter excel, do File , open click on your workspace name and viola (oops, that's French, sorry).

Neil

P.S. Did you know to close all the workbooks at the same time you hold down your Shift key, click File and you'll see Close ALL.