Excel VBS / CreateObject font color or forecolor

Aspose.Cells Product Family
1

I would like to make A1 and B1 from the code below red ('column1' and 'column2' in the excel file). How do I accomplish this?

Dim oLic, oWorkbook, oSheets, oSheet, oCells

Set oLic = CreateObject("Aspose.Cells.License")
oLic.SetLicense ("c:\testing\aspose.total.lic")

Set oWorkbook = CreateObject("Aspose.Cells.Workbook")

Set oSheets = oWorkbook.Worksheets

Set oSheet = oSheets.Item(0)

Set oCells = oSheet.Cells

oCells.item_3("A1").PutValue_5 ("Column1")
oCells.item_3("B1").PutValue_5 ("Column2")

oCells.item_2(1, 0).PutValue_5 ("Column1 - Row1")
oCells.item_2(1, 1).PutValue_5 ("Column2 - Row1")

oWorkbook.Save_5 "C:\testing\test1234.xls", 3

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please see the following code segment. It sets the fill color of the cell C3 as green. You can convert the following code into VB script for your needs.

Please also see the following documentation article relating to using Aspose.Cells in VB script

http://www.aspose.com/docs/display/cellsnet/ASP+++VBScript

VB.NET


Dim workbook As Workbook = New Workbook()


Dim worksheet As Worksheet = workbook.Worksheets(0)


Dim cell As Cell = worksheet.Cells(“C3”)


Dim style As Style = cell.GetStyle()

style.Pattern = BackgroundType.Solid

style.ForegroundColor = Color.Green

cell.SetStyle(style)


workbook.Save(“F:\Shak-Data-RW\Downloads\output.xlsx”)


3

How do I set the stl1.ForegoundColor from the document you have attached explaining vbscript?

See hightlighted text below...

Dim Workbook
Set Workbook = CreateObject("Aspose.Cells.Workbook")
Dim sheet
Set sheet = Workbook.Worksheets.Item(0)
Dim cells
Set cells = sheet.cells
Dim cell1
Set cell1 = cells.Item_2(0, 0)
Call cell1.PutValue_5("Testing...")
Dim cell2
Set cell2 = cells.Item_2(4, 2)
Call cell2.PutValue_3(100)
'methods
Dim methods
Set methods = CreateObject("EagleCW.Methods")
Dim strTest As String
strTest = methods.AsposeTestString()
MsgBox (strTest)

'Define style and specify attributes
Dim stl1
Set stl1 = cell1.Style
stl1.Font.Name = "Courier New"
stl1.Font.IsBold = True
stl1.ForegroundColor = ??????
Dim stlflag
Set stlflag = CreateObject("Aspose.Cells.StyleFlag")
stlflag.All = True
stlflag.FontBold = True
stlflag.FontName = True
'Define range of cells
Dim range
Set range = cells.CreateRange("A1", "C5")
range.Name = "MyRange"
'Apply style to the range: A1:C5
Call range.ApplyStyle((stl1), (stlflag))
'Apply style to the first row
Call cells.ApplyRowStyle(0, (stl1), (stlflag))
'Apply style to the first column: A
Call cells.ApplyColumnStyle((0), (stl1), (stlflag))
Call Workbook.Save_4("c:\testing\vbsTest.xls")Dim Workbook
Set Workbook = CreateObject("Aspose.Cells.Workbook")
Dim sheet
Set sheet = Workbook.Worksheets.Item(0)
Dim cells
Set cells = sheet.cells
Dim cell1
Set cell1 = cells.Item_2(0, 0)
Call cell1.PutValue_5("Testing...")
Dim cell2
Set cell2 = cells.Item_2(4, 2)
Call cell2.PutValue_3(100)
'methods
Dim methods
Set methods = CreateObject("EagleCW.Methods")
Dim strTest As String
strTest = methods.AsposeTestString()
MsgBox (strTest)

'Define style and specify attributes
Dim stl1
Set stl1 = cell1.Style
stl1.Font.Name = "Courier New"
stl1.Font.IsBold = True
stl1.ForegroundColor = ??????
Dim stlflag
Set stlflag = CreateObject("Aspose.Cells.StyleFlag")
stlflag.All = True
stlflag.FontBold = True
stlflag.FontName = True
'Define range of cells
Dim range
Set range = cells.CreateRange("A1", "C5")
range.Name = "MyRange"
'Apply style to the range: A1:C5
Call range.ApplyStyle((stl1), (stlflag))
'Apply style to the first row
Call cells.ApplyRowStyle(0, (stl1), (stlflag))
'Apply style to the first column: A
Call cells.ApplyColumnStyle((0), (stl1), (stlflag))
Call Workbook.Save_4("c:\testing\vbsTest.xls")

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please create the System.Drawing.Color object and set it in ForegroundColor property. You may try the following code.

Dim stColor
Set stColor = CreateObject(“System.Drawing.Color”)
stl1.ForegroundColor = stColor.Green

5

I get this error:

Run-time error '429':

ActiveX component can't create object

When this code runs:

Set stColor = CreateObject("System.Drawing.Color")Set stColor = CreateObject("System.Drawing.Color")

Any other ideas?

6

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your issue for investigation in our database. We will look into it and provide you a sample code if possible. Please spare us some time. Once, we will have some update for you, we will let you know asap.

This issue has been logged as CELLSNET-41924.

7

Hi,

Thanks for using Aspose.Cells.

Please check the following posts:

8

I tried all three examples attached with Aspose Cells in Excel vba and neither of them work.

Any other ideas?

9

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your comments in our database against this issue. We will look into it and advise you. Once, there is some update for you, we will let you know asap.

10

Hi,

Thanks for using Aspose.Cells.

Please try setting the color with int value such as Color = 0xFF000.

11

I tried setting the color to an int value and still does not work.

12

Hi,

Thanks for using Aspose.Cells.

We have logged your comments in our database. We will look into it and advise you. Once, there is some update for you, we will let you know asap.

13

Hi,

Please try the latest version/fix: Aspose.Cells for .NET v7.5.3.2 (attached).

Please try Style.ForegroundArgbColor attribute.

Thank you.

14

The issues you have found earlier (filed as CELLSNET-41924) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
15

Can you please provide a sample how i would use this in an Excel macro using vbscript?

Where would I add Style.ForegroundArgbColor?

Thanks!

Dim oLic, oWorkbook, oSheets, oSheet, oCells, oStyle, oCell1, oStyleFlag, oRange, oMethods
Dim stringTest As String
Dim intTest As Integer



Set oLic = CreateObject("Aspose.Cells.License")
oLic.SetLicense ("c:\testing\aspose.total.lic")

Set oWorkbook = CreateObject("Aspose.Cells.Workbook")

Set oSheets = oWorkbook.Worksheets

Set oSheet = oSheets.Item(0)

Set oCells = oSheet.cells
oCells.Item_3("A1").PutValue ("Column1")
oCells.Item_3("A1").PutValue ("Column1")
oCells.Item_3("B1").PutValue ("Column2")

oCells.Item_2(1, 0).PutValue ("Column1 - Row1")
oCells.Item_2(1, 1).PutValue ("Column2 - Row1")

oWorkbook.Save_5 "C:\testing\test1234.xls", 3

16

Hi,


Thanks for your query.

I have evaluated your scenario/case configuring Classic Asp on IIS. Please see the following sample code that works fine as I tested on my pc. Please refer to the code segment below that runs fine and the output file is generated fine on my end. I have applied blue color as cells shading color for a range and a whole row. I have also attached the output file for your reference.

Sample code:

Test.asp page :

<%@ LANGUAGE = VBScript %>


<% Option Explicit %>


Aspose.Cells classical ASP sample



<%


'Create a Workbook object
Dim xls
Set xls = CreateObject(“Aspose.Cells.Workbook”)
'Put data into the workbook
Dim sheet
'Get the first worksheet (default worksheet)
Set sheet = xls.worksheets.item(0)
Dim cells
Set cells = sheet.cells
Dim cell
Set cell = cells.item_3(“A1”)
cell.PutValue “Column1”

'Define style and specify attributes
Dim stl1
set stl1= cell.GetStyle
stl1.Font.Name = "Courier New"
stl1.Font.IsBold = True
’Set the blue color as fill color for cell(s)
stl1.ForegroundArgbColor = 255
’Set the pattern as solid background type
stl1.Pattern = 1
Dim stlflag
set stlflag = CreateObject(“Aspose.Cells.StyleFlag”)
'stlflag.All = True
stlflag.FontBold = True
stlflag.FontName = True
stlflag.CellShading = True
’Define range of cells
Dim range
set range = cells.CreateRange (“A1”,“C5”)
range.Name = “MyRange”
'Apply style to the range: A1:C5
call range.ApplyStyle ((stl1),(stlflag))
'Apply style to the first row
call cells.ApplyRowStyle(0,(stl1),(stlflag) )


'Save the document or Excel file

xls.Save_4 “e:\test2\outbook4.xlsx”


%>





Thank you.