Closing Specific Workbooks in Excel

[Hoth2001]

Hello all,

My code opens up 9 Excel Sheets (the paths for these files I call from an array. They are named WorkbookIn : WorkbookIn9)

I copy column B from each of these worksheets and paste their values into Columns A:I into another workbook (WorkbookOut)

 

I now want to close the 9 WorkbookIN sheets and have the WorkbookOut sheet to remain.

When I read the _Excel_Close or the _Excel_BookClose functions in the help documents, the syntax only asks for one object.......so I'm unclear on how to get the code to close the specific workbooks I want.

Here is the code I have:

expandcollapse popup

Local $sWorkbookIn = ($aArray[1][3])
Local $sWorkbookIn2 = ($aArray[2][3])
Local $sWorkbookIn3 = ($aArray[3][3])
Local $sWorkbookIn4 = ($aArray[4][3])
Local $sWorkbookIn5 = ($aArray[5][3])
Local $sWorkbookIn6 = ($aArray[6][3])
Local $sWorkbookIn7 = ($aArray[7][3])
Local $sWorkbookIn8 = ($aArray[8][3])
Local $sWorkbookIn9 = ($aArray[9][3])

Local $sWorkbookOut = "C:\Users\User\Desktop\ToGraph.xlsx"

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(0, "", "Error when opening Excel. @error = " & @error)

Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbookIn)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

Local $oWorkbookOut = _Excel_BookOpen($oExcel, $sWorkbookOut)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookOut. @error = " & @error)


;COPYING AND PASTING EACH OF THE 9 CHARTS INTO ONE CHART

_Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[1][4]) & ($aArray[1][5]), "A1")


Local $oWorkbookIn2 = _Excel_BookOpen($oExcel, $sWorkbookIn2)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn2.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn2.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("B1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[2][4]) & ($aArray[2][5]), "B1")


Local $oWorkbookIn3 = _Excel_BookOpen($oExcel, $sWorkbookIn3)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn3.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn3.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("C1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[3][4]) & ($aArray[3][5]), "C1")

Local $oWorkbookIn4 = _Excel_BookOpen($oExcel, $sWorkbookIn4)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn4.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn4.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("D1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[4][4]) & ($aArray[4][5]), "D1")


Local $oWorkbookIn5 = _Excel_BookOpen($oExcel, $sWorkbookIn5)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn5.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn5.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("E1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[5][4]) & ($aArray[5][5]), "E1")

Local $oWorkbookIn6 = _Excel_BookOpen($oExcel, $sWorkbookIn6)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn6.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn6.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("F1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[6][4]) & ($aArray[6][5]), "F1")

Local $oWorkbookIn7 = _Excel_BookOpen($oExcel, $sWorkbookIn7)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn7.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn7.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("G1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[7][4]) & ($aArray[7][5]), "G1")

Local $oWorkbookIn8 = _Excel_BookOpen($oExcel, $sWorkbookIn8)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn8.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn8.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("H1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[8][4]) & ($aArray[8][5]), "H1")


Local $oWorkbookIn9 = _Excel_BookOpen($oExcel, $sWorkbookIn9)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("I1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, "A:A")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("K1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)


_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, "C:C")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn9.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("L1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

_Excel_RangeWrite($oWorkbookOut, Default,($aArray[9][4]) & ($aArray[9][5]), "I1")

; PRIMARY & SECONDARY TOTALS


_Excel_RangeWrite($oWorkbookOut, Default, "=sum(A2:I2)", "M2")
_Excel_RangeWrite($oWorkbookOut, Default, "=-M2", "N2")

;CLOSE THE INDIVIDUAL PLANET CYCLE WORKSHEETS

_Excel_Close ($oExcel)

 

[JLogan3o13]

First off, you are mixing up worksheets and workbooks in your description; makes your request a bit confusing. Secondly, as you seem to have all of your workbooks defined in a variable, why can you not simply do something like this:

_Excel_BookClose($oWorkbookIn2)

If you're simply looking for fewer lines you could run _Excel_BookClose in a for loop