Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: yaccoff on January 28, 2018, 01:36:12 am

Title: VBScript Excel formatting cells undefined Excel constant
Post by: yaccoff on January 28, 2018, 01:36:12 am

Hi there,

Using a VBScript i am writing an SQL query of the DB and exporting the output to Excel

set oExcel = CreateObject ("Excel.Application")

With the active workbook selected I want to format the header row, but the use of xlSolid xlCentre,etc generates an error 'undefined variable' Is there something I can import to access xlSolid?

With oWorksheet.Cells.Rows(1)
         .Font.Bold = True
         .Font.Name = "Calibra"
         .Font.Size = 11
'         .Interior.Pattern = xlSolid
         .Interior.Color = 15773696
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
End With

Title: Re: VBScript Excel formatting cells undefined Excel constant
Post by: qwerty on January 28, 2018, 02:33:40 am

Note that this is a help place for EA issues, not for Excel. You should ask the guys at StackOverflow.

q.

Title: Re: VBScript Excel formatting cells undefined Excel constant
Post by: yaccoff on January 28, 2018, 03:51:50 am

Hi there, I thought about that, but its not an Excel question. Under standard VB I wouldn't need to provide anything to access xlSolid, as it would be in the environment. So really I want to know if the EA has a !INC that would provide xlSolid, etc.

Title: Re: VBScript Excel formatting cells undefined Excel constant
Post by: yaccoff on January 28, 2018, 03:59:57 am

As an interim step incase there isny any !INC I replace the xlSolid enum with its actual value from

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/constants-enumeration-excel (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/constants-enumeration-excel)

Title: Re: VBScript Excel formatting cells undefined Excel constant
Post by: Geert Bellekens on January 29, 2018, 03:39:23 pm

Yaccoff,

You are not using VB, but VBScript. And regardless of EA, VBScript won't know about constants define by Excel or any other library.
All you can do is define the constants yourself in a similar way as Local Scripts.EAConstants-VBScript

Geert