• ベストアンサー

Access VBAでExcel連携時のエラー(ワークシート指定時)

いつもお世話になっております。 現在、ExcelファイルのデータをAccessに取り込むVBでエラーが起こります。 (問題のおこる部分のみ、ソースを下記に記します。) ★の部分で、「実行時エラー1004 'Worksheets'メソッドは失敗しました:'_Global'オブジェクト」 というエラーが、出ます。 しかも、毎回ではなく、出たりでなかったりします。 これはなぜでしょうか? 定義の指定やappの指定に問題があるのでしょうか。 おわかりになる方がいらっしゃいましたら、ご指導願います。 宜しくお願いします。 --------------------------------------------------- Private Sub 取込み_Click() '■変数 Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim app As Object Dim iRow As Long Dim iUsed As Long Dim iCnt As Long (中略) '■接続 Set cn = CurrentProject.Connection '■Excelアプリの起動及びファイルオープン Set app = CreateObject("Excel.Application") app.Workbooks.Open filename:=CurrentProject.Path & "\h.xls" '■レコードセットの取得 Set rs = New ADODB.Recordset rs.Open "マスタ", cn, adOpenForwardOnly, adLockOptimistic '■DBへのレコード追加 'h.xlsの取り込みスタート行設定 iRow = 13 ''h.xlsの最終使用行 取得および設定 iUsed = Worksheets(1).UsedRange.Rows.Count'★ 'MsgBox "使用行は" & iUsed & "です。" (以下略)。。。。

noname#105246
noname#105246

質問者が選んだベストアンサー

  • ベストアンサー
  • 30246kiku
  • ベストアンサー率73% (370/504)
回答No.1

iUsed = Worksheets(1).UsedRange.Rows.Count'★ は少なくとも iUsed = app.Worksheets(1).UsedRange.Rows.Count'★ であるべきだと思います。 (これでエラーが無くなるかは別かもしれませんが)

noname#105246
質問者

お礼

>30246kikuさま ありがとうございました。 とりあえずエラーはでなくなりました。

その他の回答 (1)

回答No.2

Dim app As Object Dim sourceBook As Object Dim sourceSheet As Object Set app = CreateObject("Excel.Application") Set sourceBook = app.Workbooks.Open(CurrentProject.Path & "\h.xls") Set sourceSheet = sourceBook.Worksheets(1) iUsed = sourceSheet.UsedRange.Rows.Count せめてこれぐらいまでは参照をする。

noname#105246
質問者

お礼

>temtecomai2さま ありがとうございます。 AccessVBAに関してはだいぶ初心者で、 参照や受け渡しの部分がまだよくわかっていません。 戴いたご意見をもとに引き続き勉強します。

関連するQ&A

  • ACCESS VBA

    ACCESSで検索フォームを作りたいと思っています。 VBAを使って行きたいと思うのですが、うまくいきません。 希望としては、該当するレコードのデータを抽出したいです。 よろしくお願いいたします。 ※現段階でのソースを書いてみました。 最終的に行いたい処理とは違うのですが、根本的に間違っているようなので簡略化しました。 /------------------------------------------------/ Private Sub コマンド1_Click() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String '接続 Set cn = CurrentProject.Connection 'レコードセットを取得 Set rs = New ADODB.Recordset sql = "SELECT * FROM 従業員データ " & _ "WHERE 年齢=30" rs.Open sql, cn, adOpenDynamic, adLockReadOnly rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub /------------------------------------------------/

  • アクセス VBAのエラー

    以下のコードをwindowsXPで問題なく使っていましたが、windows7で使ったところ 「保存できません」というエラーメッセージが出ます。ただ全く同じコードを(だと思うのですが)リストボックスのダブルクリックで実行すると作動します。参考に二つのコードを書いておきます。 何か原因に心当たりのある方よろしくお願いします。 (コマンドボタン) Private Sub コマンド選択_Click() Dim namecode As String namecode = リスト会員 Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "MT_会員", cn, adOpenKeyset, adLockOptimistic rs.Find "会員IDkai = " & namecode rs!Selectedkai = True '-1 rs.Save Me!リスト会員.Requery リスト印刷会員.Requery rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub (ダブルクリック) Private Sub リスト会員_DblClick(Cancel As Integer) Dim namecode As String namecode = リスト会員 Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "MT_会員", cn, adOpenKeyset, adLockOptimistic rs.Find "会員IDkai = " & namecode rs!Selectedkai = True '-1 rs.Save Me!リスト会員.Requery リスト印刷会員.Requery rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub

  • VBAのデバックをどなたかお手伝いください。

    もちろん自分でも調べてはいるのですが、急いでいるため、もしどなたか教えてくだされば大変助かります。 この(下記の)Then 以降からがわかりません。 Do Until rs.EOF '該当レコードあり If rs!MCD = "3162" Then '--------------------------------------------- strcriteria = "CAT = '" & rs!CAT & "'" ' --- A rs2.Find strcriteria, 0, adSearchForward If rs2.EOF Then ' Else rs!仕入単価世代1 = rs!仕入単価 rs!仕入単価 = rs2!discount End If '--------------------------------------------- rs!更新日 = Now() rs.Update End If 情報が不足していればお答えします。どうぞ宜しくお願いいたします。 (補足)これより前に入力されているのは以下のものです。 Dim cn As ADODB.Connection Dim cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim strmsg As String Dim lngRet As Long Dim strcriteria As String Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset rs.Open "商品2_T", cn, adOpenKeyset, adLockOptimistic rs2.Open "商品2_T25discountてすと", cn2, adOpenKeyset, adLockOptimistic

  • Access2000のVBAで上書き更新

    お世話になります。 Access2000のVBAで作成しております。 Excelに記載した情報をAccessのテーブルに 取り込むために下記のソースを作成致しました。 既にテーブルに主キーが重複しないものを取り込むときは 正常に処理が走るのですが、 主キーが重複する場合は、重複する旨メッセージを出して 処理が止まります。 どのようにソースを修正すれば、「主キーが重複する場合、上書き更新」 するように処理が走るのでしょうか? 以上宜しくお願い致します。 --------------ソース------------------------------ Dim rs As New ADODB.Recordset Dim oApp As Object Dim iRow As Long Set oApp = CreateObject("Excel.Application") oApp.Workbooks.Open Filename:="hogehoge.xls" rs.Open "テーブル", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic iRow = 2 ' ★ While( ループ )   rs.AddNew   rs("ID") = oApp.Cells(iRow, 1)   rs("名前") = oApp.Cells(iRow, 2)   rs.Update   iRow = iRow + 1 Wend rs.Close oApp.Quit Set oApp = Nothing

  • アクセスVBA。ADO

    CSVから列を分割してテーブルにしたいかったので 下記のコードを記述しましたが、 Dim cn As ADODB.Connection Dim rs As New ADODB.Recordset Dim datacount As Long Set cn = New ADODB.Connection With cn .ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\;" .Properties("Extended Properties").Value = "text;HDR=YES;" .Open End With Set rs = cn.Execute("SELECT * FROM 構成マスタ.csv") datacount = rs.Fields.Count For i = 0 To datacount strsql = "SELECT " & rs.Fields(i).Name & " INTO " & rs.Fields(i).Name & " FROM 構成マスタ.csv;" cn.Execute strsql Next i rs.Close cn.Close Set rs = Nothing Set cn = Nothing SQLを実行するところで、「日付エラー」となってしまいます。 データには特に日付等はないのでエラーになる原因がわかりません。 どなたかご教示いただけますでしょうか。

  • recordsetが取得できなかった場合

    Access2003のVBAについて質問です。 recordsetが取得できなかった場合、どういった値が返されるのでしょうか? やりたい事は、recordsetが取得できた時、できなかった時でメッセージの表示を変えたいです。 ご教授よろしくおねがいします。 Public Sub Exsample() Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim SQL As String '接続 Set CN = CurrentProject.Connection 'レコードセットを取得 Set RS = New ADODB.Recordset SQL = "SELECT * FROM 生徒名簿 WHERE クラス = 'TS'" RS.Open SQL, CN, adOpenKeyset, adLockOptimistic

  • 【ACCESS2000】 VBAの更新処理に条件を加えたい。

    下記のような更新処理のVBAを組みました。 これにIDが5のものを更新するというのを加えるには どうすればよいでしょうか。 Dim cn As ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "tbl_D_売上", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect rs("入金方法") = 2 rs("入金方法名称") = "分割" rs.Update rs.Close Set rs = Nothing cn.Close Set cn = Nothing

  • ADO+ODBCでテーブルに接続する時のエラー

    VB6.0(SP6)から、ADOとODBCでMySQLに接続しました。 '-------------------------------------------- Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strCn As String Set cn = New ADODB.Connection strCn = "dsn=MySQL;uid=user;pwd=" cn.CursorLocation = adUseClient cn.Open strCn Set rs = New ADODB.Recordset rs.Open "select * from tbl;", cn, adOpenDynamic, adLockPessimistic Set DataGrid1.DataSource = rs '-------------------------------------------- データグリッドコントロールにテーブルの内容を表示させようとしているのですが、rs.Openの行でエラーが出てしまいます。エラーメッセージは「複数ステップの操作でエラーが発生しました。各状態の値を確認してください」です。 SQL文を変えて試してみると、何故かデータ型がDECIMALのカラムが含まれているとエラーになります。 DECIMALだといけないかと思うと、挿入は問題なく出来ます。 cn.Execute "insert into tbl (id,decimal_clm) values (10,900);" ご存知の方がいらっしゃいましたら宜しくお願いします。

    • ベストアンサー
    • MySQL
  • アクセスVBAの検索メソッドについてデバック

    前回もこちらで質問させていただいたことがある内容なのですが、引き続き、トラブルに悩まされています。仕組みがおかしくなっていたので、自分で触ってしまったことがまた悪影響だったのですが、、。 元々他の人が作ったものであり、いま、うまくいかない原因を探っているところです。まず、添付のシートにあるように、上の行のテーブルデータは"商品2_T"という名前のテーブル、下のデータは"商品2_T25discount"という名前のテーブルです。それぞれデータを引っ張ってきて、こちらのサイトに投稿するために貼り付けしました。 問題のコードをこちらに記載します。 Dim cn As ADODB.Connection Dim cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim strmsg As String Dim lngRet As Long Dim strcriteria As String Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset rs.Open "商品2_T", cn, adOpenKeyset, adLockOptimistic rs2.Open "商品2_T25discount", cn2, adOpenKeyset, adLockOptimistic MsgBox "更新を開始します  ", 64, 更新 Do Until rs.EOF '該当レコード摘出 If rs!MCD = Me!tx検索 Then '--------------------------------------------- strcriteria = "CAT = '" & rs!CAT & "'" ' --- A rs2.Find strcriteria, 0, adSearchForward If rs2.EOF Then ' Else rs!仕入単価 = rs2!discount End If '--------------------------------------------- rs!更新日 = Now() rs.Update End If rs.MoveNext Loop MsgBox "更新が完了しました  ", 64, 更新 (以上) やりたいことの説明ですが、まず、 If rs!MCD = Me!tx検索 Then とあるように、これはフォーム内にテキストボックスを用意していますので、ここに記入したMCDに一致するものに、検索をかける、ということです。そしてつぎに、strcriteria = "CAT = '" & rs!CAT & "'" 、(省略)rs!仕入単価 = rs2!discount とありますように、"CAT"を検索対象にし、前者のテーブルの"仕入単価"を、後者のテーブルの"discount"のデータで塗り替えます。 という私の解釈なのですが、(何せ他の人が作りましたので)正しいですよね? それで今試しているところなのですが、なぜかうまくいきません。何が間違っているのでしょうか? どういうエラーになるのかというと、これを実行すると、"更新日"というフィールドのみ、更新されて、仕入単価はそのままになります。ちなみに更新日が更新されているのは、このテキストボックスに入れたMCDが一致する全てのデータに対してですので、MCDが一致、には反応しているが、CATを検索して更新をかける、という動作に失敗しているように見えます。 しかし素人なのでこれ以上どうすればよいかがわかりません。どなたか少しでも解決策があれば教えていただけないでしょうか。

  • Accessのテーブルの文字列フィールドにVBAでアクセスするには?

    いつもお世話になっております。困っていることがありますので教えていただければ幸いです。 AccessのテーブルAに、メモ型のフィールドBがあり、1000文字くらいの文字が入っています。VBAの関数Cの中でそのテーブルにアクセスし、1000文字をいろいろ処理したいと思っています。見よう見まねで試しに Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim FileNum As Integer Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset rs.Open テーブルA, cn, adOpenDynamic, adLockOptimistic msgbox !フィールドB としてみたのですが、なぜか255文字までしか表示されません。VBAを用いてテーブル(やクエリ)にアクセスし、256文字以上の文字列を扱う方法はありませんでしょうか? 何かこちらで勘違いしているところがありましたら、ご指摘いただければと思います。よろしくお願いします。