三流君 ken3のmemo置き場

メモ置き場、保管庫として利用。まとまっていませんがヨロシク

挨拶・自己紹介:
失敗続きのAB型の変わり者 :三流プログラマー Ken3です
フリーのエンジニア・個人事業主です・・と書くと聞こえはイイが(それとなくカッコよく聞こえるが)、 現在は小さな案件の受注請負 と 短期派遣 で 日々つつましく?ほそぼそと暮らしてます。
(※詳細は[三流君 三流プログラマーとは?]を見てください)


広告:


XXXXXXさんへ Excel VBA コードを VBSで動作させる時にエラーが発生する件

Excel VBA を VBS に変換する作業中に
「オブジェクトでサポートされていないプロパティまたはメソッドです」
エラーが発生しました。

質問をいただきました。

ここから下質問文※一部カット

VBAで無事複数メール送信の構文が出来ました。
そして、一番の難題であるVBSへの変換ですが、
まず私はVBAのマクロをこう作りました。
Enum
宛先 = 1
氏名
成績
件名
添付ファイル1
添付ファイル2
添付ファイル3
End Enum
Sub メール作成()
Dim ol As New Outlook.Application
Dim m As mailItem
Dim MaxRow: MaxRow = Range("A1").End(xlDown).Row
For i = 2 To MaxRow
Set m = ol.CreateItemFromTemplate("c:\work\test.oft")
m.To = Cells(i, 列.宛先).Value
m.Subject = Cells(i, 列.件名).Value
m.Attachments.Add "c:\work\" & Cells(i, 列.添付ファイル1).Value
m.Attachments.Add "c:\work\" & Cells(i, 列.添付ファイル2).Value
m.Attachments.Add "c:\work\" & Cells(i, 列.添付ファイル3).Value
m.HTMLBody = Replace(m.HTMLBody, "□□", Cells(i, 列.氏名).Value)
m.HTMLBody = Replace(m.HTMLBody, "●●", Cells(i, 列.成績).Value)
m.SaveAs "c:\work\" & Cells(i, 列.氏名).Value & ".msg"
Next i
End Sub

これで無事動きます。

さて、これをVBSに変換します。

Option Explicit

Dim ex, bk, m, ol, sh

Set ex = GetObject(,"Excel.Application")

Set bk = ex.Workbooks(1)

Set sh = CreateObject("Outlook.Application")

For i = 2 To sh.Range("A1").End(-4121).Row

Set m = ol.CreateItemFromTemplate("C:\work\test.oft")

m.TO = sh.Cells(i,"A").Value

m.Subject = sh.Cells(i,"D").Value

m.Attachments.Add "C:\work\" & sh.Cells(i,"E").Value

m.Attachments.Add "C:\work\" & sh.Cells(i,"F").Value

m.Attachments.Add "C:\work\" & sh.Cells(i,"G").Value

m.HTMLBody = Replace(m.HTMLBody,"□□", sh.Cells(i,"B").Value)

m.HTMLBody = Replace(m.HTMLBody,"●●", sh.Cells(i,"C").Value)

m.SaveAs"C:\work\" & sh.Cells(i,"G").Value & ".msg"

Set m = Nothing

Next
ol.Quit
Set ol = Nothing
MsgBox("Finished!")

と助言頂きました。
何度やっても、

For i = 2 To sh.Range("A1").End(-4121).Row

の部分で「オブジェクトでサポートされていないプロパティまたはメソッドです」と
出てしまいます。

  • 4121の変換も今一つ理解出来ないのですが、ご返答頂けると幸いです(><)

1.定数 xlDownを疑う

元のVBA
Dim MaxRow: MaxRow = Range("A1").End(xlDown).Row
For i = 2 To MaxRow

エラーの場所VBS:
For i = 2 To sh.Range("A1").End(-4121).Row

なので、初見は-4121
xlDown の定数・値 を 素直に疑う

Excelで値を見てみると、
xlDown = -4121であっているみたいです。

定数の確認方法は、下記の動画を見てください。
【VBS】VBSでExcelの定数xlDownなどを使う方法 例.End(xlDown).Rowで最終行【三流君】 - YouTube
www.youtube.com

2.エラーは意外なミス?単純ミス?

「オブジェクトでサポートされていないプロパティまたはメソッドです」
なので
sh.Range("A1").End(-4121).Row
sh.
ん?
単純ミス?かも?
>Set bk = ex.Workbooks(1)
>
>Set sh = CreateObject("Outlook.Application")
>
>For i = 2 To sh.Range("A1").End(-4121).Row

参考にしたVBS作成者の気持ちになると※気持ちは関係ないか
bkがブック
shがシート
なので、
For i = 2 To sh.Range("A1").End(-4121).Row
2からsh.RangeシートのA1の最後の行を判断
としたいのに、
元のshが
>Set sh = CreateObject("Outlook.Application")

shのさしているのが、Outlook.Applicationになっている。

パターンだとshはシートなので
VBA最初の一歩(その5)変数と宣言:Excel VBA|即効テクニック|Excel VBAを学ぶならmoug
みたいに

[シート]
Set Sh = ActiveWokrbook.Worksheets("Sheet1")
Set Sh = Wb.Worksheets("Sheet1") 'ブックを参照する変数を用いた場合
Set Sh = Worksheets("sheet1") 'シート名による特定
Set Sh = Worksheets(1) 'インデックスによる特定
Set Sh = Sheet1 'コード名(オブジェクト名)による特定

あら シートの変数名ws派も多いですね
Office TANAKA - VBAの変数[Setを使うケース]
そんな余談は置いといて、

なので
>Set sh = CreateObject("Outlook.Application")

Set sh = bk.Worksheets("Sheet1") 'ブックを参照する変数を用いた場合
bkがブックなので、
sh = bk.Worksheets("Sheet1")でブックのシート1とか設定すると、

For i = 2 To sh.Range("A1").End(-4121).Row
2からsh.RangeシートのA1の最後の行を判断

通るかも。

以上、単純ミスを疑ってみました。

あとは、パターン的に
>Set sh = CreateObject("Outlook.Application")

ol
Set ol = CreateObject("Outlook.Application")
を追加するのかな?

Option Explicit

Dim ex, bk, m, ol, sh

Set ex = GetObject(,"Excel.Application") '開いているExcel

Set bk = ex.Workbooks(1)   'ブック

Set sh = bk.Worksheets(1)  'シートを指定

Set ol = CreateObject("Outlook.Application") '変数olで

For i = 2 To sh.Range("A1").End(-4121).Row  'シート最後までループ


一か所 予想しただけで、まだまだあるかもしれませんが、
解決の糸口となれば幸いです。  三流プログラマー Ken3

Ken3 ホームページ 目次

分類:HPを大きく分けると4つの柱(分類)です。
・[Excel/Access VBA]の解説
・[ASP(Active Server Pages)]の解説。
・[元コンビニ店長時代の話]が弟に巻き込まれ、失敗した脱サラ、畑違い?の仕事で失敗。
・[プログラマーの愚痴]では、あまり見せたくない三流プログラマーの内面かな。
三流君を踏み台にする
主に上記4つの分類でHP作成やメルマガの発行を行ってます。
※更新頻度が落ちていて情報の鮮度が悪いです。

三流解説動画の再生リスト
https://www.youtube.com/user/ken3video/playlists

本当に三流なんです(笑):たまにスゴイですねなんて言われることもありますが、
真実は→ [三流君の真実は...] ←を初めに見てくださるとわかると思います。
(からくりは、成功例↑しか載せてなくて ヒドイ失敗例はお蔵入り迷宮入りが多かったりします)