How to set background color in Excel by VBScript

(0) Share

Report

Posted on by Miyuki

Hi, All.

I try to set background color in Excel by VBScript while I look into the following website.

Power Automate for desktop「Excelセルの背景に色付け」 #Excel - Qiita

It seems that my flow can get each variable correctly.（cf → Fig.1）
But VBScript doesn't work.
I must make wrong program. But I can't find what makes this problem.（cf → Fig.2）
If someone has advice, I hope you will help me.
Regards,
〔Fig.1〕

〔Fig.2〕

Categories:

Power Automate Desktop

All responses (3)

Answers (1)

Sort by

CU31080729-3 502 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi,
you have doing some mistake into your code you can fallow this below code and try it.
' Create Excel Application object Set objExcel = CreateObject("Excel.Application") ' Make Excel visible (for testing purposes) objExcel.Visible = True ' Open an existing workbook Set objWorkbook = objExcel.Workbooks.Open("D:\Temp Folder\Test.xlsx") ' Select a worksheet Set objWorksheet = objWorkbook.Worksheets("Sheet1") ' Define the cell to be colored Set objCell = objWorksheet.Range("A1") ' Change the background color of the cell objCell.Interior.Color = RGB(255, 0, 0) ' Red color ' Save the workbook (if needed) objWorkbook.Save ' Close the workbook and Excel application objWorkbook.Close objExcel.Quit ' Clean up objects Set objCell = Nothing Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Chetan Ninghot
kinuasa 803 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
こんにちは。
コメントが日本語だったので日本語で回答させていただきます。
コードを見たところ、XlCellsを取得する処理の親オブジェクトの指定が抜けているようです。

Dim XlApp Dim XlSheet Dim XlCells Set XlApp = GetObject(%FileName%).Application Set XlSheet = XlApp.Worksheets(%SheetName%) Set XlCells = XlSheet.Range(XlSheet.Cells(5, "A"), XlSheet.Cells(5, "E")) XlCells.Interior.Color = RGB(255, 255, 0)

下記のようにWithステートメントを使用しても良いでしょう。

Dim XlApp Dim XlSheet Dim XlCells Set XlApp = GetObject(%FileName%).Application Set XlSheet = XlApp.Worksheets(%SheetName%) With XlSheet Set XlCells = .Range(.Cells(5, "A"), .Cells(5, "E")) End With XlCells.Interior.Color = RGB(255, 255, 0)

また、Applicationオブジェクトを使用する特別な理由が無いのであれば、GetObjectで取得できるWorkbookオブジェクトをそのまま使えば良いかと思います。

Dim XlBook 'Excel.Workbook Dim XlSheet Dim XlCells Set XlBook = GetObject(%FileName%) Set XlSheet = XlBook.Worksheets(%SheetName%) Set XlCells = XlSheet.Range(XlSheet.Cells(5, "A"), XlSheet.Cells(5, "E")) XlCells.Interior.Color = RGB(255, 255, 0)
Verified answer

Miyuki 73 on at

Like
a
(0)

Report
Copy link

Link copied!

@Chetan_Ninghot
@kinuasa
Thanks for your quick reply a lot.
I ended up using ChatGPT and got the hoped answer a little while ago.
I can resolve my problem by the following.
I should have made the use of ChatGPT before I posted on this...
-----------
@kinuasa 様
アドバイスありがとうございます。
Excel上の範囲指定は、こちらの投稿後に更にググった結果、誤った記述だと分かりました。
kinuasa様のアドバイスも試させていただこうと思います。
VBScriptの知見が全くない故に、アドバイスは大変助かります。
改めて有難うございました。