I have a folder with 10 excel files + 1 file with macro. I open only macro. How can I get the list of all other files in folder where situated macro file (with vba) and how can I open and close them one by one?
2
-
you should examine the examples of excel-help to the topics of filesystemobject. this is a bigger task, you might want to look into this code too: cpearson.com/excel/GetFileName.aspxJook– Jook2012-09-24 13:51:48 +00:00Commented Sep 24, 2012 at 13:51
-
Read up on DIR function.Siddharth Rout– Siddharth Rout2012-09-24 14:17:21 +00:00Commented Sep 24, 2012 at 14:17
Add a comment
|
1 Answer
This should get you on the right track. These macros will loop through folder and open or close all files in the folder.
Sub LoopThroughFilesAndOpen()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
strPath = "put your directory here"
strFile = Dir(strPath)
While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend
'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
ActiveSheet.Cells(i, 1).Value = colFiles(i)
Workbooks.Open Filename:=strPath & colFiles(i)
Next i
End If
End Sub
Sub LoopThroughFilesAndClose()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
strPath = "put your directory here"
strFile = Dir(strPath)
While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend
'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
ActiveSheet.Cells(i, 1).Value = colFiles(i)
'Workbooks.Close Filename:=strPath & colFiles(i)
Workbooks(colFiles(i)).Close SaveChanges:=False
Next i
End If
End Sub
Good Luck. - just put in the directy you want e.g. - "C:\myfolder\"
EDIT/ADDITION:
If you want the macros to automatically use the directy of the active workbook(the one that your running the macro from) then you can just use these versions of the above macros:
Sub LoopThroughFilesAndOpen()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
strPath = ActiveWorkbook.Path & "\"
strFile = Dir(strPath)
While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend
'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
ActiveSheet.Cells(i, 1).Value = colFiles(i)
Workbooks.Open Filename:=strPath & colFiles(i)
Next i
End If
End Sub
Sub LoopThroughFilesAndClose()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
strPath = ActiveWorkbook.Path & "\"
strFile = Dir(strPath)
While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend
'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
ActiveSheet.Cells(i, 1).Value = colFiles(i)
'Workbooks.Close Filename:=strPath & colFiles(i)
Workbooks(colFiles(i)).Close SaveChanges:=False
Next i
End If
End Sub
Which changes this line:
strPath = "put your directory here"
To:
strPath = ActiveWorkbook.Path & "\"
Note:
These two macros were taken and modified from: http://www.vadriano.com/excel-vb/2007/04/21/how-to-loop-through-files-in-a-folder/
3 Comments
Stepan1010
They will also put the names of each file in the active workbook/worksheet cells - you can just delete that code if you don't want that. It happens from this code: ActiveSheet.Cells(i, 1).Value = colFiles(i) -Good Luck
Doug Glancy
Nice answer. I forgot that Dir loops this way. One thing that you indicated, but is worth emphasizing, is that strPath must end with a "\", or better yet,
Application.PathSeparator.Stepan1010
Yea, I was googling "how to get the string directory of the active workbook".