Excel VBA質問箱 IV

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

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


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

【24845】「統合」の「統合元」を変数で指定するとエラーが? つん 05/5/11(水) 16:27 質問[未読]
【24851】Re:「統合」の「統合元」を変数で指定する... Jaka 05/5/11(水) 17:28 発言[未読]
【24853】Re:「統合」の「統合元」を変数で指定する... ichinose 05/5/11(水) 17:49 発言[未読]
【24856】Re:「統合」の「統合元」を変数で指定する... ichinose 05/5/11(水) 18:08 発言[未読]
【24857】Re:「統合」の「統合元」を変数で指定する... Kein 05/5/11(水) 18:24 発言[未読]
【24860】アホなことしてたのわかりました(>_<) つん 05/5/11(水) 20:05 発言[未読]
【24914】Re:アホなことしてたのわかりました(>_... Jaka 05/5/13(金) 15:26 発言[未読]
【24918】試行錯誤中です〜 つん 05/5/13(金) 19:23 発言[未読]
【24999】再びSOSです! つん 05/5/17(火) 13:22 質問[未読]
【25000】Re:再びSOSです! ウッシ 05/5/17(火) 14:29 回答[未読]
【25021】やっぱりアホでした・・・ つん 05/5/18(水) 9:38 お礼[未読]
【25020】Re:再びSOSです! Jaka 05/5/18(水) 9:36 発言[未読]
【25023】そのやり方は思いつきませんでした! つん 05/5/18(水) 9:44 発言[未読]
【25029】出来ました(^o^)ノ つん 05/5/18(水) 12:16 お礼[未読]

【24845】「統合」の「統合元」を変数で指定すると...
質問  つん E-MAIL  - 05/5/11(水) 16:27 -

引用なし
パスワード
   いつもお世話になります^^
質問させていただきます。

うちの会社では「統合」をよくつかいます。
標準機能でも十分なんですが、もちっと簡単にしてみようと、マクロにすることにしました。
統合させる表は、全く構成の表で、例えば、「統合テスト.xls」の「4月」、「5月」、「6月」といった月別の表があり、それを同ブックの「1月〜3月累計」シートに統合させます。
マクロは、「Personal.xls」に組んでいます。

コードは以下の通りです。

ユーザーフォーム上に

ラベル lblSaki(統合先のシート名&範囲のアドレスを表示)
リスト lstSheet (統合元のシート名をリスト(範囲は先と同じ))
コマンドボタン 3つ(「追加」「削除」「実行」用)

Option Explicit
    Private pr_strAddress As String '統合範囲(R1C1型式)
    Private pr_strSaki As String  '統合先の先頭アドレス
    Private pr_ws As Worksheet   '統合先シート


Private Sub UserForm_Initialize()

  Dim r As Range
  
  If Selection.Areas.Count > 1 Then
    MsgBox "範囲指定は一ヶ所にしてください"
    Exit Sub
  End If
  
  lblSaki.Caption = ActiveSheet.Name & " " & Selection.Address
  pr_strAddress = Selection.Address(ReferenceStyle:=xlR1C1)
  pr_strSaki = ActiveCell.Address
  Set pr_ws = ActiveSheet

End Sub


Private Sub cmdOK_Click()’実行ボタン

  Dim strTougou As String
  Dim i As Long
  
  With lstSheet
    If .ListCount = -1 Then
      MsgBox "統合するシートを選んでください"
      Exit Sub
    Else
      For i = 0 To .ListCount - 1
        strTougou = strTougou & """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & ""","
      Next i
    End If
  End With
  
  strTougou = Left$(strTougou, Len(strTougou) - 1)
  
  Debug.Print strTougou
  
  pr_ws.Range(pr_strSaki).Consolidate sources:=Array(strTougou), Function:=xlSum
  
End Sub

これを実行すると、どうしても、上記(太字)の統合させる場所でエラーが出てしまいます。
どういったエラーかというと、『統合元ファイル'[統合テスト.xls]4月"を開くことができません』です。

試しに、マクロの記録で統合すると、以下のようなコードが生成されました。


  Range("G13:AA24").Select
  Selection.Consolidate Sources:=Array( _
    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27", _
    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27", _
    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"), Function:=xlSum, TopRow _
    :=False, LeftColumn:=False, CreateLinks:=False

もともと、これを参考にコードを組んだわけです。
「Array()」の中を、変数(strTougou)なわけですが、

Debug.Print strTougou

で確かめると、

中味は、

"'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27",
"'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27",
"'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"


記録で生成されたコードと同じのようなんですが・・・
何がいけないのか、さっぱりわかりません。
ちなみに、マクロの記録も、実行ブックと別ブックに記録して、そこから実行してみると、正常に動きます。

アドバイスの程よろしくお願いします。

うまくまとめられず長くなりました(^^;
すんません(>_<)

【24851】Re:「統合」の「統合元」を変数で指定す...
発言  Jaka  - 05/5/11(水) 17:28 -

引用なし
パスワード
   こんにちは。

帰る前に
この統合テスト.xlsって開いてから統合をしているんですか?
開いているといないとでは、ファイル指定が変わるので、んな事は存じていると思いますが一応。

>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27",
>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27",
>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"

帰っちゃうのでこんな事聞いても意味ないか...すみません。
統合って1度もやった事無いので、今度教えてね!

【24853】Re:「統合」の「統合元」を変数で指定す...
発言  ichinose  - 05/5/11(水) 17:49 -

引用なし
パスワード
   つん さん、Jakaさん
こんにちは。

>
>うちの会社では「統合」をよくつかいます。
>標準機能でも十分なんですが、もちっと簡単にしてみようと、マクロにすることにしました。
>統合させる表は、全く構成の表で、例えば、「統合テスト.xls」の「4月」、「5月」、「6月」といった月別の表があり、それを同ブックの「1月〜3月累計」シートに統合させます。
>マクロは、「Personal.xls」に組んでいます。
>
>コードは以下の通りです。
>
>ユーザーフォーム上に
>
>ラベル lblSaki(統合先のシート名&範囲のアドレスを表示)
>リスト lstSheet (統合元のシート名をリスト(範囲は先と同じ))
>コマンドボタン 3つ(「追加」「削除」「実行」用)
>
>Option Explicit
>    Private pr_strAddress As String '統合範囲(R1C1型式)
>    Private pr_strSaki As String  '統合先の先頭アドレス
>    Private pr_ws As Worksheet   '統合先シート
>
>
>Private Sub UserForm_Initialize()
>
>  Dim r As Range
>  
>  If Selection.Areas.Count > 1 Then
>    MsgBox "範囲指定は一ヶ所にしてください"
>    Exit Sub
>  End If
>  
>  lblSaki.Caption = ActiveSheet.Name & " " & Selection.Address
>  pr_strAddress = Selection.Address(ReferenceStyle:=xlR1C1)
>  pr_strSaki = ActiveCell.Address
>  Set pr_ws = ActiveSheet
>
>End Sub
>
>
>Private Sub cmdOK_Click()’実行ボタン
>
>  Dim strTougou As String
>  Dim i As Long
>  
>  With lstSheet
>    If .ListCount = -1 Then
>      MsgBox "統合するシートを選んでください"
>      Exit Sub
>    Else
>      For i = 0 To .ListCount - 1
>        strTougou = strTougou & """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & ""","
>      Next i
>    End If
>  End With
>  
>  strTougou = Left$(strTougou, Len(strTougou) - 1)
>  
>  Debug.Print strTougou
>  
>  pr_ws.Range(pr_strSaki).Consolidate sources:=Array(strTougou), Function:=xlSum
'Array(strTougou)が問題なんでは・・と思います。
'これで配列Array("a","b","c")とはなりませんよ!!
'Spilt(StrTougou,",")にしてみてはいかがてしょうか?


>End Sub
>
>これを実行すると、どうしても、上記(太字)の統合させる場所でエラーが出てしまいます。
>どういったエラーかというと、『統合元ファイル'[統合テスト.xls]4月"を開くことができません』です。
>
>試しに、マクロの記録で統合すると、以下のようなコードが生成されました。
>
>
>  Range("G13:AA24").Select
>  Selection.Consolidate Sources:=Array( _
>    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27", _
>    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27", _
>    "'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"), Function:=xlSum, TopRow _
>    :=False, LeftColumn:=False, CreateLinks:=False
>
>もともと、これを参考にコードを組んだわけです。
>「Array()」の中を、変数(strTougou)なわけですが、
>
>Debug.Print strTougou
>
>で確かめると、
>
>中味は、
>
>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27",
>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27",
>"'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"
>
>
>記録で生成されたコードと同じのようなんですが・・・
>何がいけないのか、さっぱりわかりません。
>ちなみに、マクロの記録も、実行ブックと別ブックに記録して、そこから実行してみると、正常に動きます。
>
>アドバイスの程よろしくお願いします。
>
>うまくまとめられず長くなりました(^^;
>すんません(>_<)

【24856】Re:「統合」の「統合元」を変数で指定す...
発言  ichinose  - 05/5/11(水) 18:08 -

引用なし
パスワード
   >>      For i = 0 To .ListCount - 1
>>        strTougou = strTougou & """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & ""","
>>      Next i

よくみると↑この文字列の連結も気になりますねえ・・・。
""で文字列内にダブルコーテションが入っていますが、確認してみて下さい。

【24857】Re:「統合」の「統合元」を変数で指定す...
発言  Kein  - 05/5/11(水) 18:24 -

引用なし
パスワード
   配列を渡すなら動的配列として ReDim Preserve しながら一つずつ
格納していくとええよ♪

【24860】アホなことしてたのわかりました(>_&...
発言  つん E-MAIL  - 05/5/11(水) 20:05 -

引用なし
パスワード
   Jakaさん、ichinoseさん、keinさん。
回答ありがとうございました(^o^)ノ

えと・・・・自分がいかにアホなことしてたのか、わかりました^^;
皆さんのレスを参考に、やりなおします。
ファイルを持って帰ってこなかったので、明日以降になりますが、
また、出来たら結果報告、つまづいたら再質問させていただきます。
ありがとうございました。

えと、Jakaさん。
「統合」ってのはね。メニューの「データ」−「統合」にあるんですけど、
統合したい範囲を範囲指定して、「統合」のダイアログを出すと、
統合元の範囲が設定できます。それを必要なだけ設定して、OKすると、
統合したい範囲に、統合出来るの・・・って、なんちゅー説明や(>_<)
こんなのではわからないね(T_T)

【24914】Re:アホなことしてたのわかりました(>...
発言  Jaka  - 05/5/13(金) 15:26 -

引用なし
パスワード
   >「統合」ってのはね。メニューの「データ」−「統合」にあるんですけど、
>統合したい範囲を範囲指定して、「統合」のダイアログを出すと、
>統合元の範囲が設定できます。それを必要なだけ設定して、OKすると、
>統合したい範囲に、統合出来るの・・・って、なんちゅー説明や(>_<)
>こんなのではわからないね(T_T)

御回答ありがとうございました。

>統合したい範囲に、統合出来るの・・・って、
何がどう統合できるのか解りませんでした。[チョキ]
うちで入門書を見てなんとなく合計の所だけ解った様な気がします。

Sheet1!B5 + Sheet3!B5 + Sheet4!B5 見たいな表を自動で作ってくれるんやね!
他の機能についてはわかりませんでした。

それと、統合するファイルが開いていようがフルパスで入るんですね。
ごめんなさ〜い。

【24918】試行錯誤中です〜
発言  つん E-MAIL  - 05/5/13(金) 19:23 -

引用なし
パスワード
   こんばんは^^
仕事の合間をぬって試行錯誤してます。
なんか上手くいきません・・・・もちっと頑張ってみます。

Jakaさん

>>統合したい範囲に、統合出来るの・・・って、
>何がどう統合できるのか解りませんでした。[チョキ]
>うちで入門書を見てなんとなく合計の所だけ解った様な気がします。

下手な説明で失礼しました^^;

>Sheet1!B5 + Sheet3!B5 + Sheet4!B5 見たいな表を自動で作ってくれるんやね!
>他の機能についてはわかりませんでした。

そうそう。結果的には串刺し計算(というのだったか)と同じ結果になります。
そのやり方の方がメジャーなのかも・・・
「合計」だけじゃなくて、他の演算もあるみたいですが、触ったこと無いです^^;

>それと、統合するファイルが開いていようがフルパスで入るんですね。

マクロの記録じゃそうなりました。
対象ブック内に記録すると、シート名から指定されました。

【24999】再びSOSです!
質問  つん E-MAIL  - 05/5/17(火) 13:22 -

引用なし
パスワード
   こんにちは^^
お世話になります〜

最近仕事がばたばたして、なかなか時間がとれないでいました。
合間を縫って、試行錯誤してるんですが、どうもわかりません。
もう一度、SOS出せていただきます。

えと、皆様のアドバイスを元に、

Private Sub cmdOK_Click()

  Dim strTougou() As String
  Dim i As Long
  
  
  With lstSheet
    If .ListCount = -1 Then
      MsgBox "統合するシートを選んでください"
      Exit Sub
    Else
      ReDim strTougou(.ListCount - 1)
      For i = 0 To .ListCount - 1
        strTougou(i) = """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & """"
        
      Next i
    End If
  End With
 
pr_ws.Range(pr_strSaki).Consolidate sources:=strTougou, Function:=xlSum

End Sub

とやり直してみました。・・・が、やっぱり上手くいきません。

途中に

  For i = LBound(strTougou) To UBound(strTougou)
    Debug.Print strTougou(i)
  Next i

と入れて、配列の中味を確かめたところ、

"'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27"
"'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27"
"'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"

と、なりました。
そして、
統合のメイン処理のところに、これを直接入れてみたら、

pr_ws.Range(pr_strSaki).Consolidate sources:=Array("'C:\WINDOWS\デスクトップ\[統合テスト.xls]4月'!R13C7:R24C27", "'C:\WINDOWS\デスクトップ\[統合テスト.xls]5月'!R13C7:R24C27", "'C:\WINDOWS\デスクトップ\[統合テスト.xls]6月'!R13C7:R24C27"), Function:=xlSum

正常に動きました。
なので、引数の文字列は正しいと思うんですが・・・何故、配列変数だとダメなんでしょう?
前回と同じく、「ファイルが開けません」のメッセージが、3回(4月、5月、6月分)出ます。

ichinoseさんに

'==============================================
>>      For i = 0 To .ListCount - 1
>>        strTougou = strTougou & """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & ""","
>>      Next i

よくみると↑この文字列の連結も気になりますねえ・・・。
""で文字列内にダブルコーテションが入っていますが、確認してみて下さい。
'==============================================
とのアドバイスを頂いたのも、恥ずかしながらよくわかんないです。
だって、マクロの記録で生成したコードにもダブルコーテーション入ってるんですもの〜


またもや、アホなことをしてるんかも・・・
どうぞ、再びアドバイスお願いいたしますm(__)m  

【25000】Re:再びSOSです!
回答  ウッシ  - 05/5/17(火) 14:29 -

引用なし
パスワード
   こんにちは

  strTougou(i) = "'" & ActiveWorkbook.Path & _
   "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress

としてみてはどうでしょうか?

【25020】Re:再びSOSです!
発言  Jaka  - 05/5/18(水) 9:36 -

引用なし
パスワード
   おはようございます。
昨日さんまとロンドンブーツを見ながら、つんさんのコードを手本に作っていたら、何となく原因がわかりました。

>      ReDim strTougou(.ListCount - 1)
           ↑ここで先に配列を作っていますが
      ↓配列に値を入れる時に配列のインデックスをループカウンタを使用
       してますね!
       多分リストボックスがマルチ選択にしている仮定すると、飛び飛び
       選択した場合、配列に何も入ってない個所が出来るわけでぇ〜。
       要するに配列の中身が不完全の性じゃないかと....。
       全部選択した時は、まともに動きませんでしたか!
>      For i = 0 To .ListCount - 1
>        strTougou(i) = """'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & .List(i) & "'!" & pr_strAddress & """"
>        
>      Next i

で、テレビ見ながら作ったものです。
マクロブック等にコードを書くとして、アクティブシートに書き込むようにしてあります。
変更の余地はいっぱいあると思います。
尚、範囲等は直してください。

標準モジュール

Public ACWB As Workbook, TWB As Workbook
Public TWBpt As String, TWBNm As String

Sub tougou()
  Dim Fname As Variant
  Dim LstTB() As String
  Set ACWB = ActiveWorkbook
  Fname = Application.GetOpenFilename("統合ファイル(*.xls),*.xls", , "Open Files (XLS)")
  If Fname = False Then Exit Sub
  
  Set TWB = Workbooks.Open(Fname)
  ReDim LstTB(1 To TWB.Worksheets.Count)
  For i = 1 To TWB.Worksheets.Count
    LstTB(i) = TWB.Worksheets(i).Name
  Next
  
  TWBpt = TWB.Path
  TWBNm = TWB.Name
  With UserForm1
     'リストボックスをマルチ選択にする。
    .ListBox1.MultiSelect = fmMultiSelectExtended
    .ListBox1.List = LstTB
    Erase LstTB
    .Show
  End With
End Sub

フォームモジュール
(リストボックス、ボタンが1個づつあるとして)

Private Sub CommandButton1_Click()
  Dim ShTb() As String, RCAd As String, CNT As Integer
  RCAd = Range("A2:E11").Address(, , xlR1C1)
  For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
      CNT = CNT + 1
      ReDim Preserve ShTb(1 To CNT)
      ShTb(CNT) = "'" & TWBpt & "\[" & TWBNm & "]" & ListBox1.List(i) & "'!" & RCAd
    End If
  Next
  ACWB.Sheets(1).Range("C3").Consolidate Sources:=ShTb, Function:=xlSum, _
          TopRow:=False, LeftColumn:=False, CreateLinks:=False
  TWB.Close (False)
  Set ACWB = Nothing
  Set TWB = Nothing
  DoEvents
  Unload Me
End Sub

【25021】やっぱりアホでした・・・
お礼  つん E-MAIL  - 05/5/18(水) 9:38 -

引用なし
パスワード
   おはようございます。

ウッシさん、お返事が遅くなり、すみません。
書き込み後にまた急な仕事が舞い込み、検証も出来ませんでした。
回答有り難うございました!
出来ました(^o^)ノ

全体を「””」で囲む必要なかったんですね。
最初、なんでや?と思ったけど、考えたら、そらそーやんって感じで・・・
ichinoseさんがおっしゃってたのもそういうことだったんですね。
何で自分で思い至らなかったのか・・・自分のアホさ加減に、とほほ(T_T)です。

修行して、出直してきます。

ウッシさん、皆さん、ありがとうございました(^o^)ノ

【25023】そのやり方は思いつきませんでした!
発言  つん E-MAIL  - 05/5/18(水) 9:44 -

引用なし
パスワード
   Jaka さん〜

書き込みしてる間に、Jakaさんが・・・

えと、Jakaさんのは、予め、リストボックスに全てのシートをリストしてるんですね。
私は、統合するシートをシートタブをクリックしてリストさせるようにしてたんです。
なので、マルチ選択ではないです。
Jakaさんのやり方には思い至りませんでした。
でも、その方がシンプルかもしれませんね!

Jakaさんのコードを一度じっくり見させてもらいます〜
どうもありがとう^^

なんだか仕事がバタバタしがちで、遅くなるかもしれませんが、
また、レスしますね〜

【25029】出来ました(^o^)ノ
お礼  つん E-MAIL  - 05/5/18(水) 12:16 -

引用なし
パスワード
   Jakaさん〜〜〜

Jakaさんのコードを元に改造して、なかなか使い勝手の良いマクロが仕上がりました。
どうもありがとー♪

ところで、途中、vbOKCancelなメッセージボックスを使ったんですが、
一瞬使い方がわからんで困りました・・・・
そういや、最近、OKボタンオンリーのメッセージボックスしか使ってなかったっけ。
それにしても、ひどすぎですね(>_<)

常々「優秀な頭脳が欲しいぞ!」と思ってるのですが、
「優秀」どころやありませんわ・・・
「まともな記憶力のある頭脳」が必要です。とほほ(T_T)

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