Zenn
📜

Power Automate Desktop VBScriptでExcelを操作する

2023/09/25に公開
VBA
Power Automate
tech

凝ったことをしないならVBScript？

Excelの書式設定を少し整えたい。でもVBA使ってマクロブックで保存するほどでもないし、
かといってショートカットキーやマウスカチカチするのもちょっとな……。
ということがあったのでVBScript使ってみました。

使い方

公式サイトに上がっているサンプルコードが参考になります。

https://learn.microsoft.com/ja-jp/power-automate/desktop-flows/how-to/autofit-excel-columns-vbscript

Excel 列を自動調整するサンプル
'Opens the Excel file'
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = True

'Selects the specified sheet'
Set objSheet = objWorkbook.Sheets("SheetName")

'Autofits the columns of the sheet'S
for col=1 to 19
objSheet.columns(col).AutoFit()
next

'Saves and closes the Excel file'
objWorkbook.Save
objWorkbook.Close SaveChanges = True

VBSがVBAと違うのは、Excelアプリケーションを開いて閉じる処理が必要ってところですかね。
…………こんな説明でいいんでしょうか。説明へたくそですよね。詳しくは公式サイトをご覧ください。

書式設定をする

今回は、データテーブルを新規シートに出力したあとに
罫線の追加、見出しを中央揃え、セルの塗りつぶしの3つを行いたいと思います。
最終的にこのような見た目にしたい。

Column1 Column2 Column3
データ データ データ
データ データ データ

というわけで、実際に操作しながらマクロを記録してみます。

コード全体
記録されたコード
Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1:C1").Select
    Range("C1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
End Sub
罫線の部分
    Selection.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

あほみたいに長いコードが出てきました。ここから不要な部分を削除して短く…していこうかと思ったのですが、面倒くさいので最初から解答を見ちゃいます。
以下のサイトが参考になります。

ちなみにですが、このサイトをご存じでない方がいたら、VBAやExcel全般についてめちゃくちゃ詳しく説明されているのでぜひ見に行ってみることをおすすめします。[1]

http://officetanaka.net/excel/vba/tips/tips51.htm

単純に格子状に罫線を引くだけならこれでOK

格子罫線を引くお手本
Sub Macro5()
    Range("B2:C5").Borders.LineStyle = True
End Sub

ということで、これを参考にしてVBScriptのコードを作っていきます。

VBScript
Set dataAll = objSheet.UsedRange
dataAll.Borders.LineStyle = True

できました。UsedRangeで使用済みセル範囲を取得してdataAllに代入。
dataAllに格子罫線を引いています。

UsedRangeについて知りたい方は、この記事など参考になります。
https://www.limecode.jp/entry/syntax/getlastrow

見出し

記録されたコード。

見出しの部分
    Range("A1:C1").Select
    Range("C1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

冒頭のRangeでセル範囲を固定させるのも
ここを変数にして使うのもいまいちなので
別の方法で見出しを取得したいと思います。Resizeを使います。

表全体がわかっているところから見出し部分を取得するなら、
表全体.Resize(1) とすると、表の1行目だけ取得できます。

NG
Set header = dataAll.Resize(1)
With header
    .HorizontalAlignment = xlCenter
    .Interior.ThemeColor = xlThemeColorAccent6
    .Interior.TintAndShade = 0.799981688894314
End With

という感じで作ってみましたが、実はこれ、VBScriptでは動きません。
VBScriptでは、xl定数は使えないので数値に置き換える必要があります。
置き換えた結果がこちら。

OK
Set header = dataAll.Resize(1)
With header
    .HorizontalAlignment = -4108
    .Interior.ThemeColor = 10
    .Interior.TintAndShade = 0.8
End With

これで動きます。……やっぱり数値だとわかりにくい……。

こういう記事もありました。これは良い。定数を自分で定義しちゃうのもありですよね。
https://ken3code.hatenablog.jp/entry/2018/07/11/035213

定数を定義した
Const xlCenter = -4108
Const xlThemeColorAccent6 = 10
Set header = dataAll.Resize(1)
With header
    .HorizontalAlignment = xlCenter
    .Interior.ThemeColor = xlThemeColorAccent6
    .Interior.TintAndShade = 0.8
End With
全体
'Opens the Excel file'
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = True

'Selects the specified sheet'
Set objSheet = objWorkbook.Sheets("%SheetName%")

Set dataAll = objSheet.UsedRange
dataAll.Borders.LineStyle = True

Const xlCenter = -4108
Const xlThemeColorAccent6 = 10
Set header = dataAll.Resize(1)
With header
    .HorizontalAlignment = xlCenter
    .Interior.ThemeColor = xlThemeColorAccent6
    .Interior.TintAndShade = 0.8
End With

'Saves and closes the Excel file'
objWorkbook.Save
objWorkbook.Close SaveChanges = True

VBScriptを使ったその他のサンプル

https://learn.microsoft.com/ja-jp/power-automate/desktop-flows/how-to/unhide-excel-worksheets-vbscript
https://learn.microsoft.com/ja-jp/power-automate/desktop-flows/how-to/convert-excel-pdf-vbscript
https://learn.microsoft.com/ja-jp/power-automate/desktop-flows/how-to/extract-text-word-document

あとがき

PADじゃなくてVBA? VBSの記事になっちゃいました。

サンプルの列を自動調整する方法ですけど、アクションで用意されているので実際に使う場合はそれ使ったほうが良いと思います。

脚注

  1. VBAエキスパート 公式テキストの執筆もされているとてもすごいお方です。 ↩︎

Discussion