Visual Basic User's ClubコミュのAccessからExcelへエクスポート (エラー)

はじめまして。

VB, VBA, ASPを使った開発をしているものです。経験は数ヶ月で、少しずつできるようになってきてはいるものの、まだまだ厳しいです。

下記、どなたか教えていただけないでしょうか？

アプリケーション：Access

要件：Excelへ2つの種類のreportをはきだす。

問題：1回目は問題なくレポートへ抽出したデータがはきだされます。２回目以降下記のようなエラーがでます。一度アプリを終了して、再度ログインするとできます。

エラー：Worksheetsメソッドが失敗しました。Globalオブジェクト

自分なりの検証：
Excelオブジェクトをつくり、テンプレートへのアクセスが２回目以降、なんらかの問題でできないのではないか？１回目のときに、ちゃんと閉じられていないのでは？なんかしらExcel側に問題があるように思われるのですが。

*********************************************
*別モジュールに下記で定義してない変数をpublicで定義.

Sub test()

dim i as long
dim strSQL as string

On Error GoTo ErrList

strSQL = xxxx

Set rs1 = db.OpenRecordset("strSQL, dbOpenSnapshot")

strXlsS = "C:\Test.xlt"
strxlSheet = "20061026"

Set xlApp = CreateObject("Excel Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(strXlsS)

with Worksheets(strxlSheet)

.Range("A2").value = "As of" & " " & Now()

i = 5

While Not rs1.EOF

.Range("B" & i).value = rs1.Field("Total").value
(excelへの書き込み)

i = i + 1

rs1.MoveNext

Wend

End with

xlBook.SaveAs Filename = "C:\TestReport"
xlBook.Close
xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
Set XlSheet = Nothing

Exit Sub

ErrList:
MsgBox Err.Description, vbCritical

End Sub

もう１つExcelへレポートをはきだしています。これは、抽出条件が別のものなんですが、変数は全部同じです。変数を変えてやっても解消されないので、変数が問題ではないみたいです。。。

どうぞよろしくお願いいたします。

コメント(10)

最初
全て
最新の40件

[1] mixiユーザー 10月26日 23:00

まずは、Option Explicitをオススメしますが……

んで、
＞Worksheetsメソッドが失敗しました
と云われているのであれば、それを調べては？

上の中で、Worksheetsと云う「キーワード」は
＞with Worksheets(strxlSheet)
ってトコだけですが、Withと共に使っているんですから、コレはオブジェクトかなんかですよね？
「何のオブジェクトか」判りませんが。
少なくとも、Excelのアプリケーションオブジェクトの中にあるコレクションではないですね:p

[3] mixiユーザー 10月27日 00:11

ながさわさん、
アドバイスありがとうございます。Option Explicitはもちろんかいてます(ここでは省略しました)。自動ででてくるように設定しています。

＞Worksheetsメソッドが失敗しました
と云われているのであれば、それを調べては？

これも調べました。解決できるようなことは見つけられなく、今回、ここで相談させていただきました。

また、With Worksheets("sheet名の変数")をはずして、Worksheets("xxx").Range...からしてもエラーになるのでこれは問題ではないのかと。

なかなか手ごわいです:P

J.J.さん、
コメントありがとうございます。
質問の意味が？変数の定義のことでしたら、別モジュールにまとめて書いてあります。recordsetや、SQL文,そのほかConstで定義したものなど。

[4] mixiユーザー 10月27日 00:21

>Worksheets(strxlSheet)
これだけだと、どのワークブックのどのシートを設定しているのか
PGM内で分からないと思います。

よってJ.Jさんの「インスタンスの生成は何処に？」って質問が出てくるかと。

[5] mixiユーザー 10月27日 00:56

xlBook.Worksheets(strxlSheet)
じゃダメなんですかね？

あとテンプレート形式を開く場合は
先にSaveAsでファイル名を決めないと
仮のファイル名が付いちゃって見失いそうな予感…

[7] mixiユーザー 10月27日 19:45

hiro@愛知さん、

そうですね。かなり曖昧なことが原因だったようです。ありがとうございます。

へろさん、

アドバイスありがとうございました。先にSaveしたものや、killメソッドを使ったりいろいろしましたがだめだったんです。

びおふぇちさん、
アドバイスありがとうございました。そのとおりですね。情報が少なかったですよね。皆さんにご迷惑おかけしてしまいました。

エラーの発生行の把握、１回目、２回目の比較、検証、いろいろ駆使しました。もちろんMicroSoftサポートや、ネットでも調べました。日本サイトでみつからないときは英語サイト行ったり。。。でも私の調べ方が悪いのか、みつかりませんでした。が、お陰様で、完成しました。

[8] mixiユーザー 10月27日 19:52

***ご報告***

おかげさまで解決でき、完成できました。ありがとうございました。問題は、Objectのつくりすぎと曖昧さだったようです。Excelオブジェクトをつくる書き方から変えました。

ちなみに。。。

Private Sub Excel_Click()

Dim i As Long
Dim strSQL As String
Dim pswdBox As String

On Error GoTo ErrList

pswdBox = InputBox("Please Enter Password")

If Not (pswdBox = "pswd") Then
MsgBox "Not Autorized"
Exit Sub
End If

strSQL = SQL文

Set rs1 = DB.OpenRecordset(strSQL, dbOpenSnapshot)

strxlSheet = "Sheet1"

Set xlApp = CreateObject("Excel.application")

With xlApp
.ScreenUpdating = False
.Visible = True
.UserControl = True
.Workbooks.Open Filename:="C:Test.xlt"

.Worksheets(strxlSheet).Range("D3").Value = "As of" & " " & Now()

i = 6

While Not rs1.EOF

.Worksheets(strxlSheet).Range("B" & i).Value = rs1.Fields("ID").Value
.Worksheets(strxlSheet).Range("C" & i).Value = rs1.Fields("Total").Value

i = i + 1

rs1.MoveNext

Wend

.Worksheets(strxlSheet).Range("A1").Select

.ScreenUpdating = True

End With

Set xlApp = Nothing

Exit Sub

ErrList:
MsgBox "アプリケーションを終了し、再ログインしてください" & vbCrLf & "Code : " & Err.Number & vbCrLf & _
"Error : " & Err.Description, vbCritical, vbMsgBoxSetForeground

End Sub

[10] mixiユーザー 10月29日 00:07

J.J.さん、ありがとうございます。
勉強になります。このようなアドバイスを頂くことによって、スキルがアップできていくのだと思います。感謝です！！

ログインすると、みんなのコメントがもっと見れるよ