Creating more than one superior
I tried this code to create an excel sheet.
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.visible = True
ExcelObject.WorkBooks.Add
ExcelObject.Sheets(1).Cells(1,1).value = "My first excel"
But I want more than one excel to be generated. So, I tried this code -
Set ExcelObject = CreateObject("Excel.Application")
For x= 1 to 5
ExcelObject(x).visible = True
ExcelObject(x).WorkBooks.Add 'Adds a workbook to an excel object
x=x+1
ExcelObject.Sheets(1).Cells(1,1).value = "My first excel"
But it doesn't work. Please, help!
source to share
There are some errors in your code:
-
you skipped it
Next x
-
You don't need to increment
x
as you are using the operatorFor..Next
.Next x
will do it for you.
Working code:
For VB.NET
:
Dim ExcelObject() As Object
ExcelObject = New Object(5) {}
For x = 1 To 5
ExcelObject(x) = CreateObject("Excel.Application")
ExcelObject(x).visible = True
ExcelObject(x).WorkBooks.Add() 'Adds a workbook to an excel object
ExcelObject(x).Sheets(1).Cells(1, 1).value = "My first excel"
Next x
For VBScript
:
Dim ExcelObject(5)
For x = 1 To 5
Set ExcelObject(x) = CreateObject("Excel.Application")
ExcelObject(x).visible = True
ExcelObject(x).WorkBooks.Add() 'Adds a workbook to an excel object
ExcelObject(x).Sheets(1).Cells(1, 1).value = "My first excel"
Next
source to share
May want to explore collections. In the example above, where you are using ExcelObject(x).visible = True
--- You haven't created 5 ExcelObjects
yet, but when you created it.
There are several ways to do this. But playing with what you have when you add the book. You create copies of books in your book collection. So instead of referring to ExcelObject(x)
=> you will need to refer to books from elements
ExcelObject.WorkBooks.item(x)
source to share