Set a Workbook Name from VB

[Guest]

Hi all,

I cannot manage to set a workbook name before save & close. This is my code:
######################################
Set appExcel = CreateObject("Excel.Application")
With appExcel
.WindowState = xlMaximized
.SheetsInNewWorkbook = 3
.Add
.DisplayAlerts = False
End With

appExcel.Workbooks(1).Name="MyFirstBook.xls"
######################################
Unfortunately, when executing the last instruction an error is raised, as
this is a Read Only property. There's a default name which I need to custom,
so I've searched for another one but they all seem to be Read Only ones!! How
would you deal with it?

Many thanks in advance.

 

[john]

Hi all,

I cannot manage to set a workbook name before save & close. This is my code:
######################################
Set appExcel = CreateObject("Excel.Application")
With appExcel
.WindowState = xlMaximized
.SheetsInNewWorkbook = 3
.Add
.DisplayAlerts = False
End With

appExcel.Workbooks(1).Name="MyFirstBook.xls"
######################################
Unfortunately, when executing the last instruction an error is raised, as
this is a Read Only property. There's a default name which I need to custom,
so I've searched for another one but they all seem to be Read Only ones!! How
would you deal with it?

Many thanks in advance.

This might work:

Workbooks(1).SaveAs Filename:="MyFirstBook.xls", FileFormat:=xlNormal

john

 

[Guest]

Workbooks(1) is the first workbook opened, so if you are running your macro
from an Excel workbook and it is the first one opened, it will be
workbooks(1).
If you use the syntax: Workbooks.Add then you could use this from
the VBA help files:

Sub AddNew()
Set NewBook = Workbooks.Add
With NewBook
.Title = "All Sales"
.Subject = "Sales"
.SaveAs Filename:="Allsales.xls"
End With
End Sub