お世話になっております。
以下のようなサンプルプログラムでVBからExcelのセルのコピーを行う方法を
テストしました。
Private Sub Command1_Click()
Dim AppXLS As Object
Set AppXLS = CreateObject("excel.application.9")
With AppXLS.Application
.Workbooks.Add '=AppXLS.Application.Workbooks.Add
.Visible = True
.Worksheets.Add
With .Workbooks(1).Worksheets(1).Range("A1") '=AppXLS.Application.Workbooks(1).Worksheets(1).Range("A1")
.Value = "ABCDEFG"
.Font.Size = 20
.Font.ColorIndex = 3
.Columns("A:A").AutoFit
End With
With .Workbooks(1).Worksheets(1).Range("B2")
.Value = "HIJKLMN"
.Font.Size = 20
.Font.ColorIndex = 5
.Columns("A:A").AutoFit
End With
End With
Worksheets("Sheet4").Range("A1:B2").Copy _
Destination:=Worksheets("Sheet1").Range("E5")
End Sub
結果は私の望んだとおりになりましたので実際のプログラムへ反映させてみました。
実際のプログラムは別のExcelで作られた役10000行のデータをVBの変数に取り込み
VB上でデータを加工して新規で立ち上げたExcelに貼り付けたものです。
その貼り付けたデータを貼り付けたシートとは違うシートへコピーするために
End Subの前の2行を使って実行しようとしましたが以下のようなエラーが発生してしまいました。
実行時エラー'1004':
'Worksheets'メソッドは失敗しました。:'_Global'オブジェクト
参考書など見ているのですがどこをどう処理したらよいのか分かりません。
どなたかご教授願えますでしょうか。
ちなみにVB6とExcel2000です。
よろしくお願いいたします。
End With
Worksheets("Sheet4").Range("A1:B2").Copy _
Destination:=Worksheets("Sheet1").Range("E5")
End Sub
を
.Worksheets("Sheet4").Range("A1:B2").Copy _
Destination:=.Worksheets("Sheet1").Range("E5")
End With
End Sub
とすれば大丈夫ですね
36NETさま。
ご回答ありがとうございます。
Worksheets〜の部分も最初のWith AppXLS〜が必要なのですね。
私のパソコン上では修正前のプログラムでも普通に動作していたので
単独で動くものだと思っておりました。
その辺を本番用のプログラムに修正掛けてみたいと思います。
36NETさま。
無事解決いたしまた。
ありがとうございました。
また何かありましたらよろしくお願いいたします。
> With AppXLS.Application
> '=AppXLS.Application.Workbooks.Add
「AppXLS.Application」という記述は、単に「AppXLS」で十分だと思いますよ。
| 「Debug.Print AppXLS Is AppXLS.Application」を実行してみてください。
| Is演算子での比較結果が True と表示されると思います。すなわち、これらは
| 同一のインスタンスを表しているわけです。
| 結局は同じオブジェクトなので、.Applicationを指定するのは冗長的です。
> .Worksheets.Add
この部分、With ブロックを展開すると、
AppXLS.Worksheets.Add
のような意味になりますよね。(または、AppXLS.Application.Worksheets.Add)
Worksheetsオブジェクトを操作するときは、
ApplicationオブジェクトのWorksheetsプロパティ
を使うのではなく、
WorkbookオブジェクトのWorksheetsプロパティ
を使った方が安全かと思います。
| 「Debug.Print TypeName(AppXLS.Worksheets.Parent)」を実行してみてください。
| データ型として "Workbook"が表示されると思います。すなわち、
| Worksheetsの親オブジェクトは、本来はApplicationオブジェクトではなく、
| Workbookオブジェクトである事が推測されます。
| それゆえ、Applicationではなく、Workbookから辿った方が適切かと。
AppXLS.Worksheets でも操作は可能ですが、その場合は
「現在アクティブになっているワークブック」を操作する事になるので、
万一、アクティブなワークブックが(ユーザー操作やExcelマクロなどが理由で)
切り替わってしまった場合に、期待通りの動作とならない可能性があります。
例えば今回の場合は、直前に .Workbooks.Add() したWorkbookを操作するので、
AppXLS.Visible = True
Set objWorkbook = AppXLS.Workbooks.Add()
objWorkbook.Worksheets.Add
のように、操作対象のWorkbookを変数に受けて、そのWorkbookのWorksheetsを
操作するようにすると、処理が曖昧にならずに済みますよ。
> With .Workbooks(1).Worksheets(1).Range("A1")
この 〜.Worksheets(1) で操作されるワークシートは、
直前で .Worksheets.Add されたシートの事ではなく、
別のシートを操作する事になる可能性が高いですが、
それで大丈夫なのでしょうか?
> .Columns("A:A").AutoFit
この部分、With ブロックを展開すると、
objWorksheet.Range("A1").Columns("A:A").AutoFit
のようになりますね。
間違ってはいないと思いますが、この場合の操作対象は
「A1セル」というよりも「A列全体」なので、個人的には、
objWorksheet.Range("A1").EntireColumn.AutoFit
の方が適切な気がします。あるいは、より単純に
objWorksheet.Columns("A").AutoFit
もしくは、
objWorksheet.Columns(1).AutoFit
でしょうか。
> Worksheets("Sheet4").Range("A1:B2").Copy _
> Destination:=Worksheets("Sheet1").Range("E5")
>> Worksheets〜の部分も最初のWith AppXLS〜が必要なのですね。
そうですね。Worksheets〜 の部分は、36NETさんが回答されたように、
親オブジェクトを指定するようにしてください。
元のコードを生かすなら、
AppXLS.Workbooks(1).Worksheets("Sheet4").Copy 〜〜
ワークブックを変数に確保しているなら、
objWorkbook.Worksheets("Sheet4").Copy 〜〜
という感じでしょうか。
そもそも、親オブジェクトを修飾せずに Worksheets〜 や Range〜で書き始めると、
そのままではコンパイルエラーになってしまうのです。
この時、Excelを参照設定しているのであれば、Worksheets〜 などで書き始めても
エラーにならないのですが、その場合、VBが暗黙でApplicationオブジェクトを作成して、
そのWorksheets を操作しようとするため、今回のように操作が失敗したり、
『Excelが終了しない』などの問題を引き起こす事があります。
[OLEオートメーションで起動した Excel が終了しない]
http://www.interq.or.jp/www-user/komurak/progtec/030.htm#1
[Excelが開放されない]
http://www.bcap.co.jp/hanafusa/VBHLP/ExcelErr.htm
それと、Worksheets("Sheet4") の Sheet4 という記述ですが、これは
環境によっては実行時エラーが発生する可能性があるので、注意してください。
Excelを手動で起動し、[ツール]メニューの[オプション]を開いてみてください。
その[全般]タブで、『新しいブックのシート数』が3以下に設定されていれば、
新規ブックの作成時に Sheet4 が生成されないため、エラーになります。
なお、この『新しいブックのシート数』については、
AppXLS.SheetsInNewWorkbook を使って、取得/変更する事ができます。
魔界の仮面弁士さま。
大変詳しいご説明ありがとうございました。
> With AppXLS.Application
> '=AppXLS.Application.Workbooks.Add
この部分、確かにWorkbookのほうが自然ですよね。
＞AppXLS.Visible = True
＞ Set objWorkbook = AppXLS.Workbooks.Add()
＞ objWorkbook.Worksheets.Add
こうすることによって関連付けも分かりやすくなりました。
そこで、
> With .Workbooks(1).Worksheets(1).Range("A1")
ここでご指摘された部分ですが、
上のプログラムを参考にして、下のようにしてみました。
AppXLS.Visible = True
Set objWorkbook = AppXLS.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets.Add()
With objWorksheet.Range("A1")
.Value = "ABCDEFG"
.Font.Size = 20
.Font.ColorIndex = 3
.Columns(1).AutoFit
End With
まだまだ理解できていないとお叱りを受けるかと思いますが、これからもよろしくお願いいたします。