- ベストアンサー
Excel VBA、ファイルのオープンについて
どうにも解決が付かず、困ってしまっています、どなたかご回答をお願いいたします。 デスクトップ上にAというフォルダがあり、その中に二つのファイルa.xls、b.xlsがあるとします。 今、a.xlsを開いて、その中に「データ入力表」というシートを作り、そこに入力したデータをマクロを貼り付けたボタン一つでb.xlsへデータ蓄積できるようにしようと考えています。蓄積データが大量になることが予想されるので、別ファイルとしたかったのですが、そうすると、何度もb.xlsにデータを転記する必要があるので、b.xlsはその都度開いたり閉じたりせずに開いたままにしておきたいわけです。 そこで、ボタンに貼り付けるマクロには、 ・最初にデータ転記する時にb.xlsを開く ・以降のデータ転記時にはb.xlsは開いたままにする ように、IF文で分岐を付けたいのですが、最初にファイルを開くのは Set wb = Workbooks.Open("b.xls") で良いわけですが、「b.xlsを開いたままデータを転記する」場合がどうにもうまくいきません。 ファイルをオブジェクト変数にセットするように、 Set wb = Workbook("b.xls") とすると「インデックスが有効範囲にありません」と怒られ、パスの設定か?と思い、 myDir.Workbook("b.xls") とすれば「オブジェクトが必要です」 と断られてしまいます。 この、転記先となるb.xlsを開いたままデータを転記する場合には、オープンしてあるb.xlsはどうセットしたら良いのでしょうか?、というのが今回の質問です。 まだ初心者のため要点が掴みづらい文章となってしまい、お答えしにくいかとは思いますが、どなたかこの混迷状態をご理解頂けた方、ご返答を宜しくお願いいたします。
- bairi
- お礼率100% (5/5)
- オフィス系ソフト
- 回答数5
- ありがとう数6
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
#3さんのつけたしになっちゃいますが、変数の使い方がきちんと分かっていれば、モジュールレベルの変数という方法もあると思います。 Public 変数だと、残し忘れがありますが、どちらでも良いと思います。 Dim wb As Workbook 'モジュールレベルの変数 Sub test_open() '開ける時 If wb Is Nothing Then Set wb = Workbooks.Open("b.xls") End If End Sub Sub Sheet_Copy() 'コピーするとき If Not wb Is Nothing Then ThisWorkbook.Sheets(1).Cells.Copy wb.Sheets(1).Range("A1") 'ここは工夫してください。 End If End Sub Sub Test_Close() '閉じる時(ユーザーが閉じてしまった場合に、エラー・トラップを入れておく) On Error Resume Next If Not wb Is Nothing Then wb.Save wb.Close End If Set wb = Nothing End Sub
その他の回答 (4)
- quit123
- ベストアンサー率21% (4/19)
No.3です。 No.4のWendy02さんの回答で何故エラーが出るのかお分かりですよね。 If wb Is Nothing を使うときは、オブジェクト変数wbは モジュールレベルかパブリックレベルでないといけません。 そこを気付いてもらうためにわざとその説明を抜かしました。 この際に”変数の摘要範囲”をしっかりと理解するようにしてください。 プログラムを書くためには必須ですから。
お礼
No.4さんへのお礼にも書きましたが、お二人のおかげでエラーの謎が解けました。本当にありがとうございました。 なかなか成書にも問題解決の糸口となるヒントが見つからず、付け焼き刃のプログラム書きではやはり基本的な部分、今回のような変数の適用範囲のようなエラーの解決には限界がありました。 おっしゃるとおり、もう一度基本的な変数の使用法に関して勉強しておかなければならないことを痛感させられました。 本当にご回答ありがとうございました。
- quit123
- ベストアンサー率21% (4/19)
こんなんでも出来るのでは? If wb Is Nothing Then Set wb = Workbooks.Open("b.xls") End If
お礼
ご回答有り難うございます。 これだと、やはりb.xlsを開いたままの状態でマクロを実行すると、「b.xlsはすでに開かれています。二重に開くと・・・」のアラートが出てしまいます。 これが出ないようにしたいんですけどね~・・・
- papayuka
- ベストアンサー率45% (1388/3066)
エラーは#1さんの回答で回避出来ると思います。 開くファイル名やパスが同じなら、サブルーチン化してあげるとか。 開いているブック内から b.xls を探して、あれば変数にセット、 無ければこのマクロが書かれたファイルと同じフォルダ内にある b.xls を開いてセットします。 '-------------------------------------------- Sub Test() Dim wb As Workbook Call BookOpenCheck(wb) MsgBox wb.Name End Sub '-------------------------------------------- Sub BookOpenCheck(wb As Workbook) Dim twb As Workbook, Flg As Boolean Flg = False For Each twb In Workbooks If twb.Name = "b.xls" Then Set wb = twb Exit Sub End If Next twb If Not Flg Then Set wb = Workbooks.Open _ (ThisWorkbook.Path & "\b.xls") End If End Sub
お礼
ご返答有り難うございます。 一度開いてあるファイルをもう一度オープンしようとするとエラーが出ますよね?最初にデータを転記した後はb.xlsは開いたままになっているので、次のデータを入力・転記する時には、b.xlsは開いたままになっていますからこの状態で(b.xlsをオープンせずに)変数にセットしたい、ということで、IF分岐を考えたのですが・・・ サブルーチンを使ってファイルを探してあれば変数にセット、というのは思いつきませんでした。大変参考になりました、ありがとうございました。
- BLUEPIXY
- ベストアンサー率50% (3003/5914)
>Workbook("b.xls") Workbooks("b.xls") かと思います
お礼
ご返答ありがとうごじました。 初歩的ミスで申し訳ありませんでしたが、 Workbooks("b.xls") と、"s"を入れてある状態で「インデックス範囲が有効範囲にありません」というエラーメッセージがでるのですね~・・・ どうしてこういうエラーが出るのかが・・・。
関連するQ&A
- エクセル VBA 読み取り専用になっているファイルを開く場合
エクセルVBAを取り組んでいるのですが、 "A"のファイルでマクロを作動させて、"B"のファイルを開くマクロを組んでいます。 その時に誰かが"B"のファイルを開いていた場合強制的に読み取り専用で開いてしまいます。 読み取り専用で開こうとした場合には、マクロを停止させるようにしたいのですが、どういう形にしたら良いでしょうか? ちなみに"B"ファイルを開いているマクロを下に乗せておきますので、変更しなければならない等がありましたら、教えていただけますと幸いです。 For Each wb In Workbooks If InStr(wb.Name, "B") > 0 Then flag = True Next wb If flag = True Then Else Workbooks.Open Filename:="Z:\B.xls" End If
- ベストアンサー
- オフィス系ソフト
- ドライブが違ってもファイルが正常に開けるようする
windows7 Excel2007を使って、みようみまねでマクロ作成の初心者です。 現在 遠方の知人とEXCELブックのやり取りをしています。 フォルダの中にA・B・Cブックとマクロ記入用のDブックが入っており これをフォルダごと送っています。双方とも以下のマクロで開いています。 Sub Eァイルを開く() Dim wb As Workbook On Error Resume Next '開いて作業中の場合 Set wb = Workbooks("Eファイル.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open(ThisWorkbook.Path & "\Bフォルダ名\Eファイル.xls") End If End Sub 変更 私のマイドキュメントを外付けハードディスクのHドライブに移動する。 ¥H¥マイドキュメント¥計算処理 知人のマイドキュメントを外付けハードディスクのFドライブに移動する。 ¥F¥マイドキュメント¥計算処理 そしてやり取りは、マクロ記入のDブックのみとする。 そしてやりたいことは、双方のパソコンでエラーなくファイルを開けるようにしたいのです。試行錯誤的にコード書きましたがうまくいきません。どうコードを書いたらよろしいでしょうか? Sub Eファイルを開く() Dim wb As Workbook On Error Resume Next ChDrive "F" ''フォルダが存在するかどうか調べます Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FolderExists("..\計算処理") Then '開いて作業中の場合。 Set wb = Workbooks("Eファイル.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open(ThisWorkbook.Path & "\Bフォルダ名\Eファイル.xls") End If Else ChDrive "H" ChDir "計算処理" '開いて作業中の場合。 Set wb = Workbooks("Eファイル.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open(ThisWorkbook.Path & "\Bフォルダ名\Eファイル.xls") End If Set FSO = Nothing End If End Sub
- ベストアンサー
- Visual Basic
- エクセルVBAでファイル作成
エクセルVBAで、データをフィルタで抽出し、別なBOOKの指定シートに転記し、名前をつけて保存したいのです。一応、下記のコードでテストは成功しました。 しかし下記コードでは1回ごとにデータの転記先を開かなくてはなりません。 処理する件数が多いので、できればいちいち新たに開き直さなくともよい方法はないでしょうか? 最初から開いておいて、転記後名前をつけて保存すると、開いていたファイルが閉じてしまい、うまくいきません。 別BOOKでなく別シートに転記して、そのシートをMoveして別ファイル保存ならわかるのですが、今回はどうしても別BOOKに転記しなければなりません。 よろしくお願いします。 Option Explicit Sub データ分割転記() Dim myPth As String, fname As String Dim myRng As Range, myC As Range Dim i As Long, x As Long Dim wb(2) As Workbook Dim ws As Worksheet Dim t As Single t = Timer Set wb(0) = ThisWorkbook myPth = wb(0).Path With wb(0).Sheets("Key") Set myRng = .Range("A2", .Range("A2").End(xlDown)) 'KeyData End With For Each myC In myRng Application.EnableEvents = False Set wb(1) = Workbooks.Open(Filename:=myPth & "\20150806TEST.xlsm") Set ws = wb(1).Sheets("List") With wb(0).Sheets("DATA") .Range("A1:J1").AutoFilter Field:=4, Criteria1:=myC.Value .Range("A2", .Range("A2").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy ws.Range("A9") .ShowAllData End With With ws x = .Cells(Rows.Count, "A").End(xlUp).Row myC.Offset(, 2).Value = x '行数確認 .Range("A9").Value = 1 If x > 9 Then .Range("A9").AutoFill Destination:=.Range("A9:A" & x), Type:=xlFillSeries '連番 End If End With wb(1).SaveAs Filename:=myPth & "\作成ファイル\" & myC.Value & ".xlsm" wb(1).Close (False) Application.EnableEvents = True i = i + 1 Next MsgBox i & "件を完了" _ & vbCrLf & Timer - t & " Sec." End Sub *Application.EnableEvents = False を使っているのば別BOOKが持つイベントマクロを作動させないためです。
- ベストアンサー
- Excel(エクセル)
- VBAでご相談です!
Excel2010使用。 VBA初心者です。 VBAでご相談させて下さい。 複数のファイルを1つにまとめる 作業をしたいと思い、ググったところ あるサイトで下記のコードを見つけました。 ただ、このコードでは、ファイルをダイアログから 選択する形になります。 これを、ファイルを指定した状態で実行させたいと思い、 自分で試してみたのですが、上手くいきませんでした。 同一フォルダ内には4つのファイルがあり、全て同じ様式の シートが複数あります。ただ、フォルダ名が毎月変更になります。 この同一フォルダ内のデータの中の特定のシートを一つのシートに まとめたいと考えているのですが、可能でしょうか? 可能であれば、アドバイスいただけるとありがたいです。 Sub sample() Dim myPath As String Dim wb_A As Workbook, wb_B As Workbook Dim i As Long, s As Long myPath = Application.GetOpenFilename(("Excel ファイル (*.xls), *.xls"), , "データを蓄積するブックを選択して下さい。") If myPath = "False" Then Exit Sub Set wb_A = Workbooks.Open(myPath) myPath = Application.GetOpenFilename(("Excel ファイル (*.xls), *.xls"), , "データを取得するブックを選択して下さい。") If myPath = "False" Then Exit Sub Set wb_B = Workbooks.Open(myPath) With wb_B For i = 1 To .Worksheets.Count 'wb_Bループ For s = 1 To wb_A.Worksheets.Count 'wb_Aループ '同じ名前のシートがあるとき データコピー If .Worksheets(i).Name = wb_A.Worksheets(s).Name Then .Worksheets(i).Range("A1").CurrentRegion.Copy _ wb_A.Worksheets(i).Range("A65536").End(xlUp).Offset(1) Exit For End If '同じ名前のシートが無いとき シートコピー If s = wb_A.Worksheets.Count Then .Worksheets(i).Copy Before:=wb_A.Sheets(1) End If Next s Next i wb_B.Close False MsgBox "完了" End With End Sub ※長文、説明下手で申し訳ありませんが よろしくお願いします。 <参考URL> http://www.excel.studio-kazu.jp/kw/20040709212700.html
- ベストアンサー
- その他MS Office製品
- Excel VBA Auto_Openについて質問です。
Excel VBA Auto_Openについて質問です。 A.xls Sub Auto_Open() Application.OnKey "{RETURN}", "ENTER_Key" Application.OnKey "{ENTER}", "ENTER_Key" End Sub でENTERキーでセルを任意の場所に動かしたいと考えています。 ENTER_Key()でうまく動かす事は出来たのですが、Auto_Openが止まらず、Excelを落とさずに、違うファイル(B.xls)を開いてA.xlsをクローズしてもB.xlsでENTERを押すと、エラー1004となってしまいます。 個人的にはAuto_Openが動いてしまっている感じがします。 エラーがでるので同コードをWorkbook_open()に移したのですが、今度は「マクロが使用できないか、無効になっている・・・」の画面になります。 原因や対策、またはENTERキーでセルを任意の場所に動かす方法等のアドバイスよろしくお願いします。 ちなみに、セル動きは1回目は1行下がり、2回目は右に4つ移動です。 このルーチンは出来ていると思います。 それではよろしくお願いいたします。 m(_ _)m
- ベストアンサー
- その他(プログラミング・開発)
- VBA マクロ付きExcelファイルを無効で開く
タイトルの通り、マクロを含むExcelファイルを、マクロ無効でオープンしたいのですが、そういうことは出来るでしょうか? 新規ファイル作成.xlsというマクロがあって、temp.xlsというマクロを含んだファイルをnew.xlsというファイル名でコピーし、それをオープンしてデータを追加して終了。 次にそのnew.xlsをオープンした時にはtemp.xlsに含まれるマクロが実行される・・・ というようなことをしたいと思っています。 もしくは新規ファイル作成.xlsのマクロから、直接new.xlsのファイルにコーディングが可能であれば、それでも良いのですが・・・ データベースから表を作成し、作成後の変更データを再度データベースへ戻す、ということがやりたいのですが、上記の方法しか思いつきません。 もし上記の方法が出来ない場合は、別の方法がありますでしょうか? どなたかアドバイス、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- VBA 簡潔なコードを書くために
現在、下記のようなコード書いて利用しています。 このコードを他の人がに転用する時に、指定箇所さえ書き換えれば簡単に転用できる!と言うようにしたいのです。 例えば >Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName) >Set Copydata = SH1.Range("Z1").Resize(100, 1) といった指定するような箇所(" "で囲った所)を先にまとめて定義しておくにはどう記述したらよいのでしょうか。 宜しくお願いします。 ------------------------------------------------------------- Private Sub 読込ボタン_Click() '他のBookからデータを転記するマクロ Dim SH2 As Worksheet, SH1 As Worksheet Dim GYO As Range, Copydata As Range Dim myDir As String, myName As String, myBook As Workbook Set SH2 = ThisWorkbook.Worksheets("情報シート") '集計用のBookがあるフォルダ名を指定(このBookを格納している場所) myDir = ThisWorkbook.Path '他Bookのファイル名を指定(*.xls) myName = Dir(myDir & "\" & "*.xls") Do While myName <> "" 'このBook以外を対象 If myName <> ThisWorkbook.Name Then '転記先[情報シート]の最終行を取得 Set GYO = SH2.Range("A65536").End(xlUp).Offset(1) '他のBookを開いて変数に格納 Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName) '転記元を取得(Z列1行を基点に100行コピー) Set SH1 = myBook.Worksheets("回答内容") Set Copydata = SH1.Range("Z1").Resize(100, 1) '転記先の最終次行に転記(行列入替で貼付) Copydata.Copy GYO.PasteSpecial Paste:=xlPasteValues, Transpose:=True '開いた他Bookを閉じる myBook.Close End If myName = Dir() Loop End Sub -------------------------------------------------------------
- ベストアンサー
- Visual Basic
- ファイルを開かずにカスタムドキュメントプロパティを操作したい
初めて質問させていただきます。 エクセルファイルの「右クリックメニュー」-「プロパティ」-「カスタム」の情報をVBを使って操作する方法を探しております。 通常は、workbook.CustomDocumentPropertiesを利用するかと思うのますが、 Dim wb As Workbook Set wb = workbooks.Open Filename:="c:\aaa.xls") '(1) 'Set wb = Workbooks("c:\aaa.xls") '(2) 'Set wb = CreateObject("c:\aaa.xls") '(3) debug.print wb.CustomDocumentProperties("顧客") (1)~(3)の方法でオブジェクトを取得した場合、 一度ファイルを開く行為が行われているようです。 しかしながら、私が「カスタムドキュメントプロパティ」を参照しようとしてるファイルにはパスワードがかかっていることがあります。 複数のファイルを一括で処理しようとした際にファイルを開くとパスワードを求められ、プログラムが停止してしまうのが不都合となります。 ただ、エクスプローラなどで「カスタムドキュメントプロパティ」の内容を見るときはファイルを開く動作が不要と思われます。 (少なくともパスワードは求められません。) なんらかの方法があるかと思うのですが心当たりのある方、お助けいただければと思います。
- ベストアンサー
- Visual Basic
- VBA 実行時エラーで、"プロパティまたはメソッド
・Sheet1(コード) Private Sub CommandButton1_Click() Call aaa End Sub ・Module1(コード) Sub aaa() Dim wb As Workbook Dim ws As Worksheet Workbooks.Open ("c:\test.xls") Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") wb.ws.Range("A2").Value = "CCC" End Sub wb.ws.Range("A2").Value = "CCC"の部分で 以下の実行エラーが出ます。 ------------------------------------------------------------------------ 実行時エラー'438': オブジェクトは、このプロパティまたはメソッドをサポートしていません。 ------------------------------------------------------------------------ Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") の部分で特にエラーも出ないので、オブジェクトの取得は成功していると 思うのですが、WorkSheetオブジェクトのwsからRangeメソッドを呼ぶことが できません。 動かない原因と対策を教えてください!!
- ベストアンサー
- その他(プログラミング・開発)
- エクセル マクロ 簡素化
マクロ初心者です。 下記のデータのコピペする、マクロを使用しています。 下記にはAAAとBBBの2つのエクセルへのコピペのみしか記述していませんが、 その下に50ファイル分のファイル名、コピー元、コピー先だけが違うマクロが並んでいます。 メンテナンスや更新に手作業で行っているので、非常に時間がかかります。 例えば、別のシートにファイル名、コピー元、コピー先の一覧を作成し、 そのシートでファイル名、コピー元、コピー先を修正し、コピペができるようになるなど、 どうにかして簡素化したいのですが、どのように実現すればいいか、教えてくださると助かります。 ---------------------------------------------------------------------- Private Sub CommandButton1_Click() Dim wb As Workbook Application.ScreenUpdating = False Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\AAA.xls", UpdateLinks:=0) ThisWorkbook.Sheets("BBB").Range("A1:B1").Copy wb.Sheets("CCC").Range("A1:B1").PasteSpecial (xlPasteValues) Application.CutCopyMode = False Application.DisplayAlerts = False wb.Close (True) Application.DisplayAlerts = True Application.ScreenUpdating = True Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\BBB.xls", UpdateLinks:=0) ThisWorkbook.Sheets("BBB").Range("A2:B2").Copy wb.Sheets("CCC").Range("A2:B2").PasteSpecial (xlPasteValues) Application.CutCopyMode = False Application.DisplayAlerts = False wb.Close (True) Application.DisplayAlerts = True Application.ScreenUpdating = True 'アイテム名、コピー元、コピー先、だけがちがう、同じようなマクロが50ファイル分ある。 End Sub
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございました。 No.3さんとNo.4さんのご回答で、ようやくエラーの謎が解けました。変数のセットについてもう一度勉強し治してくる必要がありますね、ほんとうにご回答有り難うございました。