Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

Topic

New Reply

In Word 2003, the following code works like a charm:

If Dialogs(wdDialogFileNew).Show = -1 Then
‘some code here
End If

This shows the FileNew dialog where you can pick a template and, after clicking OK, a new doc is created based on that template.
I tried something similar in Excel:

If Application.Dialogs(xlDialogWorkbookNew).Show = -1 Then
‘some code here
End If

This doesn’t work as I expected.
First, it doesn’t create a new workbook based on the chosen template. It inserted an extra Sheet in the current workbook. And there was no sign of the custom commandbar that should be created and made visible. (The code to do that works fine in Excel97 for 2 years.)

Second, Excel insists that Application. should be in front of Dialogs(…). If not, it complains with:

Compile error: Sub or Function not defined

Third, if this code is called when there’s NO workbook already open, this error occurs:

Runtime error 1004: Property Show of class Dialog cannot be (found / loaded ?)
(in Dutch: Eigenschap Show van klasse Dialog kan niet worden opgehaald)

What am I doing wrong?

Reply | Quote

Replies

Jan,

To display the Templates dialog, you need

Application.Dialogs(xlDialogNew).Show

This one will also work if no workbook is open.

Note: in general, Excel VBA is more finicky than Word VBA, for reasons not known to me. You may also have noticed that IntelliSense is much less Intelli in Excel. For example, no list of properties and methods pops up if you type ActiveSheet. but it does if you type ActiveWorkbook.

Reply | Quote

And fast as always, Hans!

(Sometimes I get the feeling you’re looking over my shoulder when I’m typing…)

Reply | Quote

And fast as always, Hans!

(Sometimes I get the feeling you’re looking over my shoulder when I’m typing…)

Reply | Quote

In reference to no Intellisense when typing ActiveSheet, the reason why you don’t get “Intellisense” for some objects, like ActiveSheet, is because the Application ActiveSheet property returns a generic Object data type; this object may be one of five types of sheets (although a workbook has a Sheets collection, there is no Sheet object) – WorkSheet, Chart, etc. For example, this sub:

Public Sub TestSheetType()

Dim obj As Object

Application.Workbooks.Add
ActiveWorkbook.Sheets.Add Type:=xlWorksheet, After:=Sheets(Sheets.Count)
ActiveWorkbook.Sheets.Add Type:=xlExcel4MacroSheet, After:=Sheets(Sheets.Count)
ActiveWorkbook.Sheets.Add Type:=xlChart, After:=Sheets(Sheets.Count)

Debug.Print "Sheet Objects:"
For Each obj In ActiveWorkbook.Sheets
Debug.Print obj.Index & " " & obj.Name & " (" & GetSheetType(obj.Type) & ")"
Next obj

Debug.Print vbCrLf & "Sheet Total: " & ActiveWorkbook.Sheets.Count
Debug.Print "Worksheet Count: " & ActiveWorkbook.Worksheets.Count
Debug.Print "Chart Count: " & ActiveWorkbook.Charts.Count
Debug.Print "Macro Sheet Count: " & ActiveWorkbook.Excel4MacroSheets.Count

Set obj = Nothing
End Sub

Public Function GetSheetType(lSheetType As Long) As String

Select Case lSheetType
Case xlChart ' -4109
GetSheetType = "Chart"
Case xlWorksheet ' -4167
GetSheetType = "Worksheet"
Case xlDialogSheet ' -4116
GetSheetType = "Dialog Sheet"
Case xlExcel4MacroSheet ' 3
GetSheetType = "Excel4 Macro Sheet"
Case xlExcel4IntlMacroSheet ' 4
GetSheetType = "Excel4 Intl Macro Sheet"
Case Else
GetSheetType = "Unknown"
End Select
End Function

Returns the following results:

Sheet Objects:
1 Sheet1 (Worksheet)
2 Sheet2 (Worksheet)
3 Chart1 (Excel4 Macro Sheet)
4 Macro1 (Excel4 Macro Sheet)

Sheet Total: 4
Worksheet Count: 2
Chart Count: 1
Macro Sheet Count: 1

Note when you loop thru Sheets collection, a Chart sheet returns a bogus sheet type, but when xlChart specified with Add method, a chart sheet was inserted (although NOT in the position specified by “After” arg, as shown in test results; juggling sequence in which sheets inserted did not change this behavior). Worksheets always returned correct sheet type. Anyway since ActiveSheet can represent one of five different objects, with different properties/methods, that’s why there’s no “Intellisense” (the same way a generic Object variable will have no Intellisense). The Application ActiveWorkbook property specifically returns a Workbook object – there’s no ambiguity, so Intellisense works. Based on these results I would term Excel VBA as “flakey” in addition to “finicky.”

Reply | Quote

That’s a valid point, but still… If I type

Workbooks("Book1").Worksheets("Sheet1").

or

Workbooks("Book1").Charts("Chart1").

IntelliSense doesn’t kick in either, although I have now specified that I am using a Worksheet or Chart object. There is no ambiguity.

Reply | Quote

I would guess the reason why there’s no IntelliSense when you type something like ActiveWorkbook.Worksheets.Item(1). (or use the abbreviated syntax, where “Item” is implied), when you look at the Worksheets or Charts Item property in Object Browser, it is defined as follows:

Property Item(Index) As Object
read-only
Member of Excel.Worksheets

Property Item(Index) As Object
read-only
Member of Excel.Charts

The Item property returns a generic Object, rather than a specific object like Worksheet or Chart – why, who knows, AFAIK there’s only one type of Worksheet or Chart sheet. Possibly for some reason related to “backwards-compatability”, the reason there’s still archaic artifacts in the object model like Macro and Dialog sheets. In comparison, if you examine the Item property for the Windows collection, it returns a specific object type (Window), thus Intellisense is provided when accessing an item in collection, as shown in attached pic.

Property Item(Index) As Window
read-only
Member of Excel.Windows

Anyway what I usually do is just declare variable specifically As Worksheet, then can use Intellisense. Incidentally if using ActiveSheet, and need to test what type of sheet is active, found VBA TypeName function to be more reliable:

? TypeName(ActiveSheet)
Worksheet
? TypeName(ActiveSheet)
Chart

? TypeName(ActiveWorkbook.Sheets(1))
Worksheet
? TypeName(ActiveWorkbook.Sheets(4))
DialogSheet

Reply | Quote

Thanks again. Microsoft’s decision to define items of the Worksheets and Charts collections as Object make IntelliSense less “intelligent” than it could be.

> Anyway what I usually do is just declare variable specifically As Worksheet

Yes, that is what I do too, even when I want to use ActiveSheet.

Reply | Quote

Thanks for the clarification! Learned a lot. Again.
About 95% of my developing is done in Word VBA, and just occasionally in Excel VBA.

Reply | Quote

Thanks for the clarification! Learned a lot. Again.
About 95% of my developing is done in Word VBA, and just occasionally in Excel VBA.

Reply | Quote

I would guess the reason why there’s no IntelliSense when you type something like ActiveWorkbook.Worksheets.Item(1). (or use the abbreviated syntax, where “Item” is implied), when you look at the Worksheets or Charts Item property in Object Browser, it is defined as follows:

Property Item(Index) As Object
read-only
Member of Excel.Worksheets

Property Item(Index) As Object
read-only
Member of Excel.Charts

The Item property returns a generic Object, rather than a specific object like Worksheet or Chart – why, who knows, AFAIK there’s only one type of Worksheet or Chart sheet. Possibly for some reason related to “backwards-compatability”, the reason there’s still archaic artifacts in the object model like Macro and Dialog sheets. In comparison, if you examine the Item property for the Windows collection, it returns a specific object type (Window), thus Intellisense is provided when accessing an item in collection, as shown in attached pic.

Property Item(Index) As Window
read-only
Member of Excel.Windows

Anyway what I usually do is just declare variable specifically As Worksheet, then can use Intellisense. Incidentally if using ActiveSheet, and need to test what type of sheet is active, found VBA TypeName function to be more reliable:

? TypeName(ActiveSheet)
Worksheet
? TypeName(ActiveSheet)
Chart

? TypeName(ActiveWorkbook.Sheets(1))
Worksheet
? TypeName(ActiveWorkbook.Sheets(4))
DialogSheet

Reply | Quote

That’s a valid point, but still… If I type

Workbooks("Book1").Worksheets("Sheet1").

or

Workbooks("Book1").Charts("Chart1").

IntelliSense doesn’t kick in either, although I have now specified that I am using a Worksheet or Chart object. There is no ambiguity.

Reply | Quote

In reference to no Intellisense when typing ActiveSheet, the reason why you don’t get “Intellisense” for some objects, like ActiveSheet, is because the Application ActiveSheet property returns a generic Object data type; this object may be one of five types of sheets (although a workbook has a Sheets collection, there is no Sheet object) – WorkSheet, Chart, etc. For example, this sub:

Public Sub TestSheetType()

Dim obj As Object

Application.Workbooks.Add
ActiveWorkbook.Sheets.Add Type:=xlWorksheet, After:=Sheets(Sheets.Count)
ActiveWorkbook.Sheets.Add Type:=xlExcel4MacroSheet, After:=Sheets(Sheets.Count)
ActiveWorkbook.Sheets.Add Type:=xlChart, After:=Sheets(Sheets.Count)

Debug.Print "Sheet Objects:"
For Each obj In ActiveWorkbook.Sheets
Debug.Print obj.Index & " " & obj.Name & " (" & GetSheetType(obj.Type) & ")"
Next obj

Debug.Print vbCrLf & "Sheet Total: " & ActiveWorkbook.Sheets.Count
Debug.Print "Worksheet Count: " & ActiveWorkbook.Worksheets.Count
Debug.Print "Chart Count: " & ActiveWorkbook.Charts.Count
Debug.Print "Macro Sheet Count: " & ActiveWorkbook.Excel4MacroSheets.Count

Set obj = Nothing
End Sub

Public Function GetSheetType(lSheetType As Long) As String

Select Case lSheetType
Case xlChart ' -4109
GetSheetType = "Chart"
Case xlWorksheet ' -4167
GetSheetType = "Worksheet"
Case xlDialogSheet ' -4116
GetSheetType = "Dialog Sheet"
Case xlExcel4MacroSheet ' 3
GetSheetType = "Excel4 Macro Sheet"
Case xlExcel4IntlMacroSheet ' 4
GetSheetType = "Excel4 Intl Macro Sheet"
Case Else
GetSheetType = "Unknown"
End Select
End Function

Returns the following results:

Sheet Objects:
1 Sheet1 (Worksheet)
2 Sheet2 (Worksheet)
3 Chart1 (Excel4 Macro Sheet)
4 Macro1 (Excel4 Macro Sheet)

Sheet Total: 4
Worksheet Count: 2
Chart Count: 1
Macro Sheet Count: 1

Note when you loop thru Sheets collection, a Chart sheet returns a bogus sheet type, but when xlChart specified with Add method, a chart sheet was inserted (although NOT in the position specified by “After” arg, as shown in test results; juggling sequence in which sheets inserted did not change this behavior). Worksheets always returned correct sheet type. Anyway since ActiveSheet can represent one of five different objects, with different properties/methods, that’s why there’s no “Intellisense” (the same way a generic Object variable will have no Intellisense). The Application ActiveWorkbook property specifically returns a Workbook object – there’s no ambiguity, so Intellisense works. Based on these results I would term Excel VBA as “flakey” in addition to “finicky.”

Reply | Quote

Jan,

To display the Templates dialog, you need

Application.Dialogs(xlDialogNew).Show

This one will also work if no workbook is open.

Note: in general, Excel VBA is more finicky than Word VBA, for reasons not known to me. You may also have noticed that IntelliSense is much less Intelli in Excel. For example, no list of properties and methods pops up if you type ActiveSheet. but it does if you type ActiveWorkbook.

Reply | Quote