I have 3 Workbooks in the same directory.
for example in c:\mydir\:
Workbooks1.xls
Workbooks2.xls
Workbooks3.xls
each workbook in Workbook_Open have a macro.
i need to open Workbooks1.xls, execute the macro in Workbook_Open to the end of macro close it, open the Workbooks2.xls execute the macro in Workbook_Open to the end of macro close it, open the Workbooks3.xls... ecc
how to based the first workbook is Workbooks1.xls?
i hop i'm clear....
OPEN AND CLOSE automaticlly Workbooks
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: OPEN AND CLOSE automaticlly Workbooks
Does this do what you want?
Code: Select all
Sub OpenAndClose()
Const strFolder = "C:\MyDir\"
Dim varFile As Variant
Dim wbk As Workbook
Application.ScreenUpdating = False
For Each varFile In Array("Workbook1.xls", "Workbook2.xls", "Workbook3.xls")
Set wbk = Workbooks.Open(strFolder & varFile)
wbk.Close SaveChanges:=False ' or True if you want to save the workbook
Next varFile
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: OPEN AND CLOSE automaticlly Workbooks
In addition to Hans's code, this code uses the Dir function to process all Excel files in the folder, no matter what their name is...
Code: Select all
Sub ProcessBooks()
Dim sPath As String, sExt As String, sFile As String
sPath = "C:\MyDir\"
sExt = "*.xls*"
sFile = Dir(sPath & sExt)
Do While sFile <> ""
If sFile <> ThisWorkbook.Name Then
Workbooks.Open Filename:=sPath & sFile
' Code goes here...
Workbooks(sFile).Close False
End If
sFile = Dir
Loop
MsgBox "Completed processing each workbook in folder: " & vbNewLine & sPath, vbInformation
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: OPEN AND CLOSE automaticlly Workbooks
TKS! Work fine...HansV wrote:Does this do what you want?
Code: Select all
Sub OpenAndClose() Const strFolder = "C:\MyDir\" Dim varFile As Variant Dim wbk As Workbook Application.ScreenUpdating = False For Each varFile In Array("Workbook1.xls", "Workbook2.xls", "Workbook3.xls") Set wbk = Workbooks.Open(strFolder & varFile) wbk.Close SaveChanges:=False ' or True if you want to save the workbook Next varFile Application.ScreenUpdating = True End Sub