Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4741 / 13644 ツリー ←次へ | 前へ→

【54777】Workbooksの引数でフルパス指定が原因? jax_6 08/3/29(土) 16:29 質問[未読]
【54778】Re:Workbooksの引数でフルパス指定が原因? jax_6 08/3/29(土) 16:43 発言[未読]
【54780】Re:Workbooksの引数でフルパス指定が原因? jax_6 08/3/29(土) 17:38 質問[未読]
【54781】Re:Workbooksの引数でフルパス指定が原因? n 08/3/29(土) 17:57 発言[未読]
【54782】Re:Workbooksの引数でフルパス指定が原因? jax_6 08/3/29(土) 18:32 お礼[未読]
【54783】Re:Workbooksの引数でフルパス指定が原因? n 08/3/29(土) 19:27 発言[未読]
【54792】Re:Workbooksの引数でフルパス指定が原因? jax_6 08/3/30(日) 12:12 お礼[未読]
【54793】Re:Workbooksの引数でフルパス指定が原因? jax_6 08/3/30(日) 12:24 発言[未読]

【54777】Workbooksの引数でフルパス指定が原因?
質問  jax_6  - 08/3/29(土) 16:29 -

引用なし
パスワード
   [Excel2003 WindowsXPsp2]
複数ファイルから、特定のセルを抜き出すプログラムを書いています。
・下記の Workbooks(filePath).Activate でエラーになるのは何故でしょうか
 (インデックスが有効範囲にありません。(Error 9))
・MSDNで一次資料のリファレンスを探そうとしたのですが発見できませんでした。宜しければURLを教えて頂けないでしょうか。(情けないお願いで申し訳ありません)

# VBA は今日初めて挑戦してみました。プログラムの経験は JavaScript(中級)のみです。書き方がかなり違うし変数に型があるしで頭とか目とか心が折れかかり質問させて頂きました。宜しくお願い致します。
----------------------
Sub 抽出実行ボタン()
  Workbooks("PickCells.xls").Worksheets(1).Activate
  
  ' ↓抽出対象のフォルダパス・ファイルパスが書いてある列
  Dim filePathCol As String
  filePathCol = "B"
  
  ' ↓抽出対象のファイルが列挙してある最初の行
  Dim counter_file As Integer
  counter_file = 8
  sv_counter_file = counter_file
  
  ' ↓抽出対象のセルが列挙してある列
  Dim cellPlaceCol As String
  cellPlaceCol = "D"
  
  ' ↓抽出対象のセルのシートが列挙してある列
  Dim cellSheetPlaceCol As String
  cellSheetPlaceCol = "E"
  
  ' ↓抽出対象のセルが列挙してある最初の行
  Dim counter_cell As Integer
  counter_cell = 8
  sv_counter_cell = counter_cell
  
   ' ↓内容のあるセルだけ処理したい場合はこのやり方で「普通」か?
  Do While ActiveSheet.Range(filePathCol + CStr(counter_file)).Value <> ""
    Dim filePath As String
    filePath = ActiveSheet.Range(filePathCol + CStr(counter_file)).Value
     ' ↑ループ判定でも見てるのにまた同じものを見るのは・・
     ' JavaScriptなら条件節で変数への代入も同時に書ける
     ' 例 if( a=b.value ){ alert(a); }
    
    Workbooks.Open Filename:=filePath
    
    Workbooks("PickCells.xls").Worksheets(1).Activate
    
    Do While ActiveSheet.Range(cellPlaceCol + CStr(counter_cell)).Value <> ""
      
      Dim cellPlace As String
      cellPlace = ActiveSheet.Range(cellPlaceCol + CStr(counter_cell)).Value
      
      Dim cellSheet As Integer
      cellSheet = ActiveSheet.Range(cellSheetPlaceCol + CStr(counter_cell)).Value
      
      MsgBox (filePath)
      Workbooks(filePath).Activate
      Worksheets(cellSheet).Activate
      
      Dim targetValue As String
      targetValue = ActiveSheet.Range(cellPlace).Value
      
      Workbooks(PickCells.xls).Worksheets(2).Activate
      If counter_file = sv_counter_file Then
        ActiveSheet.Cells(1, counter_cell - sv_counter_cell + 1).Value = cellPlace + "(" + cellSheet + ")"
      End If
      ActiveSheet.Cells(counter_file - sv_counter_file + 2, counter_cell - sv_counter_cell + 1).Value = targetValue
      
      Workbooks("PickCells.xls").Worksheets(1).Activate
      counter_cell = counter_cell + 1
    Loop
    
    Workbooks(filePath).Close
    
    Workbooks("PickCells.xls").Worksheets(1).Activate
    counter_file = counter_file + 1
     ' ↑counter++ や += は無い?
  Loop
End Sub

【54778】Re:Workbooksの引数でフルパス指定が原因...
発言  jax_6  - 08/3/29(土) 16:43 -

引用なし
パスワード
   二点目の一次資料の件については「ヘルプ」がかなり充実しておりこちらで十分かと思いましたので解決致しました。

【54780】Re:Workbooksの引数でフルパス指定が原因...
質問  jax_6  - 08/3/29(土) 17:38 -

引用なし
パスワード
   とりあえず動くようになりました。>>0ではカウンターの初期化処理が抜けていたり、いろいろ問題があったのでもう一度動くようになったコードを載せます。
お騒がせ致しました。

>>0で質問していた箇所は、アクティブになっていないことを利用して
Workbooks(2).Activate と書くことで動くようになりました。
が、興味+名前の方が確実なのとで、できればファイル名で指定したいのですが、フルパスしかない場合は文字列操作でファイル名の部分だけ抽出するしかないのでしょうか。
引き続き宜しくお願い致します。
-----------------------
Sub 抽出実行ボタン()
  Workbooks("PickCells.xls").Worksheets(1).Activate
  
  ' ↓抽出対象のフォルダパス・ファイルパスが書いてある列
  Dim filePathCol As String
  filePathCol = "B"
  
  ' ↓抽出対象のファイルが列挙してある最初の行
  Dim counter_file As Integer
  counter_file = 8
  sv_counter_file = counter_file
  
  ' ↓抽出対象のセルが列挙してある列
  Dim cellPlaceCol As String
  cellPlaceCol = "D"
  
  ' ↓抽出対象のセルのシートが列挙してある列
  Dim cellSheetPlaceCol As String
  cellSheetPlaceCol = "E"
  
  ' ↓抽出対象のセルが列挙してある最初の行
  Dim counter_cell As Integer
  counter_cell = 8
  sv_counter_cell = counter_cell
  
   ' ↓内容のあるセルだけ処理したい場合はこのやり方で「普通」か?
  Do While ActiveSheet.Range(filePathCol + CStr(counter_file)).Value <> ""
    Dim filePath As String
    filePath = ActiveSheet.Range(filePathCol + CStr(counter_file)).Value
     ' ↑ループ判定でも見てるのにまた同じものを見るのは・・
     ' JavaScriptなら条件節で変数への代入も同時に書ける
     ' 例 if( a=b.value ){ alert(a); }
    Workbooks.Open Filename:=filePath
    
    Workbooks("PickCells.xls").Worksheets(1).Activate

    Do While ActiveSheet.Range(cellPlaceCol + CStr(counter_cell)).Value <> ""

      Dim cellPlace As String
      cellPlace = ActiveSheet.Range(cellPlaceCol + CStr(counter_cell)).Value
      
      Dim cellSheet As Integer
      cellSheet = ActiveSheet.Range(cellSheetPlaceCol + CStr(counter_cell)).Value
      
      ' ↓↓問題箇所→Workbooks(filePath).Activateと書くとエラー
      Workbooks(2).Activate
      ' ↑↑問題箇所→Workbooks(filePath).Activateと書くとエラー
      Worksheets(cellSheet).Activate
      
      Dim targetValue As String
      targetValue = ActiveSheet.Range(cellPlace).Value

      Workbooks("PickCells.xls").Worksheets(2).Activate
      
      ' ↓抽出結果の見出しを作っている部分
      If counter_file = sv_counter_file Then
        ActiveSheet.Cells(1, counter_cell - sv_counter_cell + 1).Value = cellPlace + "(" + CStr(cellSheet) + ")"
      End If
      
      ActiveSheet.Cells(counter_file - sv_counter_file + 2, counter_cell - sv_counter_cell + 1).Value = targetValue
      
      Workbooks("PickCells.xls").Worksheets(1).Activate
      counter_cell = counter_cell + 1
    Loop
    
    counter_cell = 8
    
    ' ↓↓問題箇所→Workbooks(filePath).Closeと書くとエラー
    Workbooks(2).Close
    ' ↑↑問題箇所→Workbooks(filePath).Closeと書くとエラー
    
    Workbooks("PickCells.xls").Worksheets(1).Activate
    counter_file = counter_file + 1
     ' ↑counter++ や += は無い?
  Loop
End Sub

【54781】Re:Workbooksの引数でフルパス指定が原因...
発言  n  - 08/3/29(土) 17:57 -

引用なし
パスワード
   こんにちは。
非常に直しがいがあるコードなのは確かですが、
ほとんどの方にとってはちょっと見難いコードになっているのではないでしょうか。

まずはコードモジュールの先頭に

Option Explicit

をいれて、変数に対して明示的な宣言を強制し、
コンパイルエラーのチェックを行ったほうが良いかもしれません。

エラーについては解決のようですね。
Workbooks.Openメソッドではフルパスが必要ですが、(必要ない場合もありますが)
開いた後のWorkbooksのインデックスの指定には、ファイル名のみを使います。

 Dim bookname As String

...と変数を1個追加し、

 Workbooks.Open Filename:=filePath
 bookname = ActiveWorkbook.Name
 :
 :
 MsgBox (filePath)
 MsgBox (bookname)
 Workbooks(bookname).Activate
 'Workbooks(filePath).Activate

...としてみてください。

ただ、文字列指定ではなくて、Object型の変数を用意すれば、
名前によるインデックス指定使わなくても良いです。

 Dim wb As Workbook
 :
 Set wb = Workbooks.Open(Filename:=filePath)
 :
 wb.Activate
 :
 :
 Set wb = Nothing
End Sub

【54782】Re:Workbooksの引数でフルパス指定が原因...
お礼  jax_6  - 08/3/29(土) 18:32 -

引用なし
パスワード
   n様、ご回答ありがとうございます。

>まずはコードモジュールの先頭に
>Option Explicit
>をいれて、変数に対して明示的な宣言を強制し、
>コンパイルエラーのチェックを行ったほうが良いかもしれません。

頭にハテナを浮かべながら言われた通りにしてみたところ・・
sv_ のカウンターが宣言されていませんでした!!!
ありがとうございます。


>開いた後のWorkbooksのインデックスの指定には、ファイル名のみを使います。
> Dim bookname As String
>...と変数を1個追加し、
> Workbooks.Open Filename:=filePath
> bookname = ActiveWorkbook.Name
> :
> MsgBox (filePath)
> MsgBox (bookname)
> Workbooks(bookname).Activate
> 'Workbooks(filePath).Activate
>...としてみてください。

できましたー!!!!!なるほど!開いた直後はアクティブなはずなので、そこで nameプロパティ(!)で名前をとっておくんですね。


>ただ、文字列指定ではなくて、Object型の変数を用意すれば、
>名前によるインデックス指定使わなくても良いです。

オオ・・実は代入は全部 Set を使うのかと今日の午前中くらいまで思っていたんですが、オブジェクト型変数に代入するときに Set を使うんですね!!


大変勉強になります。本当にありがとうございます。改良したら(可読性と合わせて)もういちどコードをのせます。

----------------------
> Set wb = Nothing
VBA でもメモリーリークみたいのがあるんですか(NullじゃなくてNothingなんですね・・)使わなくなった変数はどんどん Nothing を代入すると安全・・ですか・・・

【54783】Re:Workbooksの引数でフルパス指定が原因...
発言  n  - 08/3/29(土) 19:27 -

引用なし
パスワード
   補足ですが、ActivateやSelectに頼ると状況によっては不具合の元ですし、
実行速度にも影響してきます。
Objectの親からきちんと指定してあげれば、ほとんどのケースでは不要です。
://www.officetanaka.net/excel/vba/speed/s2.htm

例えば
Dim wb     As Workbook
Dim ws1     As Worksheet
Dim ws2     As Worksheet
Dim mxRow    As Long
Dim i      As Long

Set ws1 = Workbooks("PickCells.xls").Worksheets(1)
Set ws2 = Workbooks("PickCells.xls").Worksheets(2)
...とし、

cellPlace = ws1.Range(cellPlaceCol + CStr(counter_cell)).Value
cellSheet = ws1.Range(cellSheetPlaceCol + CStr(counter_cell)).Value
targetValue = wb.Worksheets(cellSheet).Range(cellPlace).Value

ws2.Cells(1, counter_cell - sv_counter_cell + 1).Value = cellPlace + "(" + CStr(cellSheet) + ")"

ws2.Cells(counter_file - sv_counter_file + 2, counter_cell - sv_counter_cell + 1).Value = targetValue

...とすればActivateは必要ないです。

またLoop処理についてはFor...Nextステートメントについて調べてみてください。

'B8セルから下へ連続データを調べ、終わりの行を取得
mxRow = ws1.Cells(counter_file, filePathCol).End(xlDown).Row
'B9セル以降データがないとシートの最終行を取得してしまうので念の為チェック
If ws1.Cells(mxRow, filePathCol).Value = "" Then mxRow = counter_file
For i = counter_file To maxrow
'カウンタとして i を利用

Next

...などとする事ができます。
さらに For Each...Next ステートメントを使うとObjectや配列のLoopの際に効率的です。
VBEのヘルプは状況依存型で、コード内の調査語句にマウスキャレットをあてて[F1]キーを押せば
目的のトピックにクイックアクセスできますので、色々調べてみてください。


>VBA でもメモリーリークみたいのがあるんですか
あるみたいですね。でも Set wb = Nothing をすれば大丈夫、というものでもないようですよ。
Set wb = Nothing
は言ってみれば『Object変数の初期化』ですね。
本来、プロシージャ終了時にリセットされるので必要ないという考えもあるようです。
ですがこのObject変数の使い方によっては、なんか残ったりするようですね。
プロではないので詳しくはわかりませんが。

補足は以上です。
でわ。がんばってください。

【54792】Re:Workbooksの引数でフルパス指定が原因...
お礼  jax_6  - 08/3/30(日) 12:12 -

引用なし
パスワード
   n様、さらなるご教授ありがとうございましたm(_ _)m

>補足ですが、ActivateやSelectに頼ると状況によっては不具合の元ですし、
>実行速度にも影響してきます。
オブジェクトにしてみたところ、スピードが全然違いました!!
これは超重要事項ですね。
Activate多用は「うおーなんか自動で動いてるー」というのが見える以外、特に使うメリットは多くないんですね。

>VBEのヘルプは状況依存型で、コード内の調査語句にマウスキャレットをあてて[F1]キーを押せば
こ、これは・・・便利すぎます。大変ありがとうございます。
あとエラーチェックや実行時のデバッグも親切で、VBAを作る人の意欲が失われにくくなってると思いました。

>またLoop処理についてはFor...Nextステートメントについて調べてみてください。
使ってみました!ちょっとスッキリしました。あと、一回一回空白かどうか判定する前回の処理よりも、一度データの最終行を取得してループする方がスピードが上がりそうですね。

その他、参照するセルは各ファイルで共通なので、毎回設定を読むのではなく、最初に配列に格納してループ中はそこを見るように変えました。
(「定数式が必要です。」のエラーが多発してくじけそうになりましたが、ReDimを学びました。)

n様には大変お世話になり、改めてお礼申し上げます。

↓以下改良コード↓
-------------------
Sub 抽出実行ボタン()

  ' ↓対象のファイルと、抜き出すセルを設定してあり、マクロの実行ボタンがあるブック
  Dim wb As Workbook
  Set wb = Workbooks("PickCells.xls")
  
  ' ↓一枚目のシートが設定&ボタン
  Dim ws1 As Worksheet
  Set ws1 = wb.Worksheets(1)
  
  ' ↓二枚目のシートに抽出結果を出力する
  Dim ws2 As Worksheet
  Set ws2 = wb.Worksheets(2)
  
  ' ↓開くブックのワーク変数
  Dim wk_wb As Workbook
  Dim wk_ws As Worksheet
  
  
  ' ↓抽出対象のファイルパスが書いてある列
  Dim filePathCol As String
  filePathCol = "B"
  
  ' ↓抽出対象のファイルが列挙してある最初の行
  Dim counter_file As Integer
  counter_file = 8
  
  ' ↓抽出対象のファイルが列挙してある最後の行
  Dim mxRow_file As Integer
  Dim i_file As Integer
  If ws1.Range(filePathCol + CStr(counter_file + 1)).Value <> "" Then
    mxRow_file = ws1.Range(filePathCol + CStr(counter_file)).End(xlDown).Row
  Else
    mxRow_file = counter_file
    ' ↑一行しかない場合End(xlDown)はシート最終行を取得してしまうので
  End If
  
  
  ' ↓抽出対象のセルが列挙してある列
  Dim cellPlaceCol As String
  cellPlaceCol = "D"
  
  ' ↓抽出対象のセルのシートが列挙してある列
  Dim cellSheetPlaceCol As String
  cellSheetPlaceCol = "E"
  
  ' ↓抽出対象のセルが列挙してある最初の行
  Dim counter_cell As Integer
  counter_cell = 8
  
  ' ↓抽出対象のセルが列挙してある最後の行
  Dim mxRow_cell As Integer
  If ws1.Range(cellPlaceCol + CStr(counter_cell + 1)).Value <> "" Then
    mxRow_cell = ws1.Range(cellPlaceCol + CStr(counter_cell)).End(xlDown).Row
  Else
    mxRow_cell = counter_cell
  End If
  
  ' ↓ファイル数の回数繰り返し見ることになるので、予め抽出対象のセルは配列に格納してスピードアップ。
  Dim cellPlaceS() As String
  Dim cellSheetS() As Integer
  ' 変数で配列定義をしようとするとエラーになってしまうのでReDimを使う
  ReDim cellPlaceS(counter_cell To mxRow_cell)
  ReDim cellSheetS(counter_cell To mxRow_cell)
  Dim i As Long
  For i = counter_cell To mxRow_cell
    cellPlaceS(i) = ws1.Range(cellPlaceCol + CStr(i)).Value
    cellSheetS(i) = ws1.Range(cellSheetPlaceCol + CStr(i)).Value
    ' ↓シート番号は空欄だったら1にしておく
    If cellSheetS(i) = 0 Then
      cellSheetS(i) = 1
    End If
  Next i
  
  ' ↓抽出処理開始
  For i_file = counter_file To mxRow_file
    Dim filePath As String
    filePath = ws1.Range(filePathCol + CStr(i_file)).Value
    
    Set wk_wb = Workbooks.Open(Filename:=filePath)

    Dim i_cell As Integer
    For i_cell = LBound(cellPlaceS) To UBound(cellPlaceS)
      Set wk_ws = wk_wb.Worksheets(cellSheetS(i_cell))
      
      Dim targetValue As String
      targetValue = wk_ws.Range(cellPlaceS(i_cell)).Value
      
      ' ↓抽出結果の見出しを作っている部分
      If i_file = counter_file Then
        ws2.Cells(1, i_cell - counter_cell + 1).Value = cellPlaceS(i_cell) + "(" + CStr(cellSheetS(i_cell)) + ")"
      End If
      
      ' ↓ws2に抽出した内容を転記
      ws2.Cells(i_file - counter_file + 2, i_cell - counter_cell + 1).Value = targetValue
    Next i_cell
    
    wk_wb.Close
  Next i_file
  
  ' ↓ReDimしたら後始末が必要らしい
  Erase cellPlaceS
  Erase cellSheetS
  
  ' ↓おまじない
  Set wb = Nothing
  Set ws1 = Nothing
  Set ws2 = Nothing
  Set wk_wb = Nothing
  Set wk_ws = Nothing
End Sub

【54793】Re:Workbooksの引数でフルパス指定が原因...
発言  jax_6  - 08/3/30(日) 12:24 -

引用なし
パスワード
   あ・・・
もうカウンターじゃなくなって、ただの設定値になってるので、counter_file や counter_cell の名前を変えようと思ってたんですが忘れてしまいました・・・

4741 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free