Templates

This refers to the path of the local directory with add-in files.


Application.UserLibraryPath

Application.UserLibraryPath 

Options > VBA Code > Folder Paths


Application.TemplatesPath

This refers to the local Templates directory

Application.TemplatesPath 

Options > VBA Code > Folder Paths


Application.StartupPath

This refers to the local xlstart directory

Application.StartupPath 

Options > VBA Code > Folder Paths
There are no properties to determine the global templates and startup directories.


Application.NetworkTemplatesPath

Application.NetworkTemplatesPath 

Options > VBA Code > Folder Paths




Application.Repeat 




Windows


Total height in points of the active window

ActiveWindow.Height 


Total width in points on the active window

ActiveWindow.Width 


The position inside the main Excel window

ActiveWindow.Top 
ActiveWindow.Left


ActiveWindow.Split = False 


A maximised window has a top = -17 and a left = -275


New Workbook

This creates a new empty workbook with the default number of worksheets

Workbooks.Add 


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) 


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 wbk As Workbook 
Set wbk = Workbooks.Add
wbk.Range("A2").Value = "some text"

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



Specific Template

The Add method also lets you specify a template to use for your new workbook.
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.

Set wbk = Workbooks.Add (Template:="C:\Temp\"wbkTemplate.xls") 




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext