下記コードでエクセルのバインディングをしています。
シート１を元にグラフを作成していますが、矢印部分でエラー発生します。
エラー内容は
実行時エラー1004
sheetsメソッドは失敗しました。'_global'オブジェクト
と表示されます。
印刷ボタンをクリックされると下記コードが実行されるのですが
１回目は正常にいきます。印刷終了後、再度印刷ボタンをクリックすると
エラー発生します。
どうすれば改善されるでしょうか？
PC環境VB6.0、WindowsXPPROSP2
Dim xlApp As New Excel.Application, xlBook As New Excel.Workbook
xlApp.Application.Visible = True
xlApp.Application.ScreenUpdating = False
Set xlBook = xlApp.Application.Workbooks.Open(PCPATH & HYO)
xlBook.Charts.Add
xlBook.Sheets("Graph1").ChartType = xlColumnClustered
↓エラー発生
xlBook.Sheets("Graph1").SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & row), PlotBy:=xlColumns
↑
xlBook.Sheets("Graph1").Location Where:=xlLocationAsNewSheet
宜しくお願い致します
> excelバインディングで2回実行するとエラー発生
コーディングミスなどの理由により、暗黙のグローバルオブジェクトを
利用してしまうと、そのような現象になります。
これは参照設定している場合にのみ起こり得る現象なので、あえて
参照設定を外し、レイトバインドで実装するのも、一つの回避方法です。
(参照設定していない場合、誤ったコードをコンパイルエラーとして検出できる)
とりあえず下記では、参照設定している場合の修正方法について述べておきます。
> Dim xlApp As New Excel.Application, xlBook As New Excel.Workbook
この行には、2 つ問題があります。
(1) VB6 では、New を用いた変数宣言は、使用すべきでは無いとされています。
変数へのアクセスのたびに、Nothing判定が行われるため、
パフォーマンス上の問題がありますし、また、
If Not xlApp Is Nothing Then
のような Nothing 判定のコードが、利用できなくなるためです。
→変数宣言から New を取り除き、事後に Set xlApp = New Excel.Application としましょう。
(2) xlBook のインスタンスを New で生成すべきではありません。
ブックのインスタンスは、その後の .Workbooks.Open にて生成されるのですから、
それより前に生成しておくことには、まったく意味がありません。それどころか、
不要なオブジェクトが生成される事により、解放処理を複雑にするといった弊害が出ます。
→変数宣言から New を取り除きましょう。
> xlApp.Application.Visible = True
> xlApp.Application.ScreenUpdating = False
> Set xlBook = xlApp.Application.Workbooks.Open(PCPATH & HYO)
これらの行には、共通した一つの誤りがあります。
(3) xlApp と xlApp.Application は、同一のインスタンスを返します。
「Debug.Print xlApp Is xlApp.Application」は、True を返すはずです。即ち、
xlApp.Visible = True
xlApp.Application.Visible = True
xlApp.Application.Application.Visible = True
は、すべて同じ意味のコードであるということです。。
→Application プロパティへのアクセスは冗長です。xlApp変数を使いましょう。
> xlBook.Charts.Add
> xlBook.Sheets("Graph1").ChartType = xlColumnClustered
これらの行には、一つの曖昧さを含んだコードがあります。
(4) 新規に追加したチャートは、"Graph1" 以外の名前となる可能性があります。
→こういう時は、"Graph1" という固定名を使ってアクセスするのでは無く、
Set o = xlBook.Charts.Add()
o.ChartType = xlColumnClustered
のように、Add の戻り値を Chart 型の変数に受けて、それを利用しましょう。
> xlBook.Sheets("Graph1").SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & row), PlotBy:=xlColumns
この行が、今回の最大の問題点です。
(5) 名前付き引数Source に割り当てられた変数は、暗黙のグローバルオブジェクトへの
参照を含んでいます。
『Sheets("Sheet1")』というシート指定では、「どのブック上のシート」なのかが
明らかにされていません。たとえば Excel を同時に 2 つ開いて制御していたとしたら、
このコードでは、いずれの Sheet1 が操作対象となるのか、分かりませんよね。
このようなコードは、Excel の 2回目以降の実行を阻害したり、Excel 本体が
正しく終了せず、非表示のままタスクに残ってしまうなどの問題を引き起こします。
→Source:=Sheets("Sheet1").Range(…) ではなく、親オブジェクトも修飾させて
Source:=xlBook.Sheets("Sheet1").Range(…) のような構文を使ってください。
この他、Sheets, Worksheets, Range, Cells, Charts, Selection などといった
プロパティも、親オブジェクトの指定忘れによる問題を引き起こしやすいので、
合わせてチェックしておきましょう。
親切、丁寧に教えて頂き有難うございます。同時に勉強になりました。
大変感謝しております。
ご指摘されていた事に従い、
New を取り除きSet xlApp = New Excel.Application
として
xlBook.Sheets("Graph1").SetSourceData Source:=xlBook.Sheets("Sheet1").Range("A1:B" & row), PlotBy:=xlColumns
とした所、見事に改善されました。
魔界の仮面弁士様がおっしゃる通りだと思います。
バイディングをもっと理解しなければいけませんね。