OPEN AND CLOSE automaticlly Workbooks

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

OPEN AND CLOSE automaticlly Workbooks

Post by sal21 »

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....

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: OPEN AND CLOSE automaticlly Workbooks

Post by HansV »

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

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: OPEN AND CLOSE automaticlly Workbooks

Post by Rudi »

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.

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: OPEN AND CLOSE automaticlly Workbooks

Post by sal21 »

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
TKS! Work fine...