Creating


The Workbooks Collections

The Workbooks collection consists of all currently open workbook objects.
Members can be added to the workbooks collection in a number of different ways.
You can create a new empty workbook based on the default workbook template or you can create a new workbook based on a different template.


New Workbook

This creates a new empty workbook based on the default template Book.xls.

Workbooks.Add 

This method will create a new workbook with the name BookX where X is the corresponding sequence number.
The new workbook will obviously be the active workbook so you can refer to it using the ActiveWorkbook property.

ActiveWorkbook.SaveAs FileName:="C:\Temp.xls" 

It is possible to create a new workbook that contains just a single worksheet:

Workbooks.Add(xlWBATemplate.xlWBATWorksheet) 

It is also possible to create a new workbook that contains just a single chart sheet:

Workbooks.Add(xlWBATemplate.xlWBATChart) 

Specific Template

The Add method also lets you specify a template to use for your new workbook.
The Add method allows you to specify a template for the new workbook.
The template does not have to be saved as a template (with a .xlt extension) it can be a normal workbook (.xls extension).
When the argument is a string specifying the folder location of an existing workbook the new workbook is created using this workbook as the template.

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Add (Template:="C:\Temp\MyTemplate.xls")

This method will create a new workbook with the name MyTemplateX where X is the corresponding sequence number.


Reference to New Workbook

A better approach is to use the return value from the Add method to create an object variable referring to the workbook.

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Add
objWorkbook.Range("A2").Value = "some text"

This can be useful for keeping track of temporary workbooks without the need to save them.


Copying

VB.FileCopy("C:\temp\One.xlsm", "C:\temp\Copy.xlsm" 


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext