Excel VBA質問箱 IV

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

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


2500 / 13646 ツリー ←次へ | 前へ→

【67465】MsgBoxで指定したブックのデータをコピーしたい すず 10/12/7(火) 0:14 発言[未読]
【67467】Re:MsgBoxで指定したブックのデータをコピ... kanabun 10/12/7(火) 10:28 発言[未読]
【67469】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/7(火) 21:52 お礼[未読]
【67468】Re:MsgBoxで指定したブックのデータをコピ... kanabun 10/12/7(火) 13:16 発言[未読]
【67470】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/8(水) 0:44 質問[未読]
【67471】Re:MsgBoxで指定したブックのデータをコピ... りん 10/12/8(水) 4:11 発言[未読]
【67481】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/8(水) 21:21 お礼[未読]
【67482】Re:MsgBoxで指定したブックのデータをコピ... kanabun 10/12/8(水) 22:21 発言[未読]
【67516】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/9(木) 22:28 質問[未読]
【67517】Re:MsgBoxで指定したブックのデータをコピ... kanabun 10/12/9(木) 23:30 発言[未読]
【67529】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/10(金) 22:49 お礼[未読]
【67672】Re:MsgBoxで指定したブックのデータをコピ... すず 10/12/22(水) 21:54 質問[未読]

【67465】MsgBoxで指定したブックのデータをコピー...
発言  すず  - 10/12/7(火) 0:14 -

引用なし
パスワード
   同様の質問があったとは思いますが、今の私の力では以下の動作を行うためのコードが編み出せません。
是非お力をお借りできればと思います。

<現状>

・日付(yymmdd形式)がついた、微妙に名前の違う日報がいくつかあり、ネットワークドライブのフォルダにある。

・とりまとめ用のファイルも、毎日作成するため、yymmdd形式の日付がついたファイルとなっている。

・ばらばらと点在した同じ日付のついた日報の特定のシートから、特定の範囲をコピーし、とりまとめ用に貼り付ける。
それが大変時間がかかるし、毎日の作業なので効率化を図りたい。

<やりたいこと>
・コマンドボタンなどをクリックすると、メッセージボックスが表示され、作成する日をyymmdd形式で入力するだけで、入力したのと同じ日付の日報から該当データをとりまとめ用のファイルにコピーしたい。

・ただし、日報のデータ範囲はシートごとに異なり、最後の1行が総合計となっているが、これは外してコピーしたい。

たとえば、A101206.xls、B101206.xlsが日報だとして、C101206.xlsにとりまとめるとすると、

A101206.xlsの「東京」シートのA1〜C10の範囲で、最終行以外(つまりA1〜C9まで)を、C101206シートの「東京」シートD1を基点に貼り付ける、という感じにしたいと思います。
(もうひとつのB101206.xlsは「大阪」と「名古屋」シートのデータを、C101206.xlsのそれぞれ「大阪」と「名古屋」シートに最終行以外の範囲を貼り付けます。)

データの行数が可変し、最終行以外を貼り付ける、というところがポイントになります。

せめてヒントだけでもいただければと思います。
ほんとに困っています。
なにとぞよろしくお願いいたします。

【67467】Re:MsgBoxで指定したブックのデータをコ...
発言  kanabun  - 10/12/7(火) 10:28 -

引用なし
パスワード
   ▼すず さん:

>・日付(yymmdd形式)がついた、微妙に名前の違う日報がいくつかあり、ネットワークドライブのフォルダにある。

><やりたいこと>
>・コマンドボタンなどをクリックすると、メッセージボックスが表示され、作成する日をyymmdd形式で入力するだけで、入力したのと同じ日付の日報から該当データをとりまとめ用のファイルにコピーしたい。


>せめてヒントだけでもいただければと思います。

手始めに ネットワークから 指定のyymmddを含むxlsファイルの一覧を
取得する処理をコード化してみてはいかがでしょうか?


シート上に図形描画ツールボックスから適当なオートシェイプ
(「額縁」がおすすめ)を貼りつけて、名前を「ファイル取得ボタン」
に変更し、以下を標準モジュールにコピペして実験してみてください。

'(注) myPath はそちらの環境にあわせておいて、かつ実行する前に
'   あらかじめ接続しておいてください。

'------------------------------------ 標準モジュール
Option Explicit

Sub ファイル取得ボタン_Click()
 Dim myPath As String: myPath = "\\サーバ名\フォルダ名\" '◆要変更
 Dim FileName As String
 Dim i As Long
 Dim FoundFiles() As String

 FileName = InputBox$("yymmdd形式でファイル名を指定", "ファイルの取得")
 If StrPtr(FileName) = 0& Then Exit Sub
 If Not (FileName Like "######") Then Exit Sub
 
 FileName = "*" & FileName & "*.xls"
 
 ''検索パスとファイルパターンを指定してファイル検索
 FoundFiles = GetFile(myPath, FileName)

 For i = 0 To UBound(FoundFiles)
   Debug.Print FoundFiles(i)
 Next
 
End Sub

'サブフォルダを含むファイルの検索(ファイルリストを返す)
Private Function GetFile(myPath As String, _
            FileName As String) As String()
  Dim tmpPath As String
  Dim sCmd As String
  Dim ko As Long
 
  If Right$(myPath, 1) <> "\" Then myPath = myPath & "\"
  tmpPath = Environ$("Temp") & "\Dir.tmp"
  sCmd = "DIR """ & myPath & FileName & """ /b/s /o:N > """ _
      & tmpPath & """"
  With CreateObject("WScript.Shell")
    ko = .Run("CMD /C " & sCmd, 7, True) 'Dirコマンド実行
  End With

  Dim io As Integer
  Dim buf() As Byte
  io = FreeFile()
  Open tmpPath For Binary As io '出力ファイルリスト取得
   ReDim buf(1 To LOF(io))
   Get #io, , buf
  Close io
  Kill tmpPath
  GetFile = Split(StrConv(buf, vbUnicode), vbCrLf)
End Function

'--------------------------------------------------
上は
Dir検索結果得られたフィル名リストをイミディエイト・
ウィンドウに表示しているだけですが、

>   Debug.Print FoundFiles(i)

ここを

  Set Wb = Workbooks.Open(FoundFiles(i))

   '開いたファイルに対する転記処理 〜〜〜 〜〜〜

  Wb.Close SaveChanges:=False
  Set Wb = Nothing

のように、選択ファイルの転記処理に修正補間していくと
目的の処理ができるようになると思います。

【67468】Re:MsgBoxで指定したブックのデータをコ...
発言  kanabun  - 10/12/7(火) 13:16 -

引用なし
パスワード
   先程の

> For i = 0 To UBound(FoundFiles)
>   Debug.Print FoundFiles(i)
> Next

のなかに すこし加筆してみました。

Sub ファイル取得ボタン_Click()

 (省 略)

 ''検索パスとファイルパターンを指定してファイル検索
 FoundFiles = GetFile(myPath, FileName)

 If UBound(FoundFiles) < 0 Then
   MsgBox "該当ファイルが見つかりません"
   Exit Sub
 End If
 
 Dim WB0 As Workbook
  Set WB0 = Workbooks("コピー先Book.xls")
 Dim WB As Workbook
 Dim ws As Worksheet
 For i = 0 To UBound(FoundFiles)
   Set WB = Workbooks.Open(FoundFiles(i))
   For Each ws In WB.Worksheets
     Select Case ws.Name
      Case "東京", "大阪", "名古屋"
        このシートより転記 ws, WB0
     End Select
   Next
   WB.Close False
   Set WB = Nothing
 Next
 Set WB0 = Nothing
 MsgBox "転記終了!"
End Sub

> A101206.xlsの「東京」シートのA1〜C10の範囲で、
> 最終行以外(つまりA1〜C9まで)を、
> C101206シートの「東京」シートD1を基点に貼り付ける、
> データの行数が可変し、最終行以外を貼り付ける、という
>  ところがポイントになります。
Private Sub このシートより転記( _
            ByVal ws As Worksheet, _
            ByVal WB0 As Workbook)
 Dim ws0 As Worksheet
  Set ws0 = WB0.Worksheets(ws.Name)
 Dim r As Range
 With ws
  Set r = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
  Set r = r.Resize(r.Rows.Count - 1, 3)
 End With
 r.Copy ws0.Range("D1")
  
End Sub

【67469】Re:MsgBoxで指定したブックのデータをコ...
お礼  すず  - 10/12/7(火) 21:52 -

引用なし
パスワード
   kanabun様

お礼が遅くなりまして申し訳ありません。
ご丁寧な回答いただき、本当に感謝します。
すばらしいです!!

こちらで試させていただき、また何かありましたら質問させていただきたいと思います。
お時間を割いていただき、本当にありがとうございました!!

【67470】Re:MsgBoxで指定したブックのデータをコ...
質問  すず  - 10/12/8(水) 0:44 -

引用なし
パスワード
   kanabun様

たびたび申し訳ありません。
とりまとめ用のブックのシートにボタンを作って、そちらにご教示いただいたコードをセットしました。
そして実行すると、やりたいことはできたのですが、以下の場所で引っかかってしまいます。

おそらく、「もう検索できるファイルがない」ということで出たんだと思いますが、すべてのファイルを転記し終わったあと、このエラーを出さないようにするにはどのようにすればよろしいでしょうか?

とりまとめ用のブックはアクティブになっており、こちらにマクロをセットしたかったので、
Dim WB0 As Workbook
  Set WB0 = ActiveWorkbook

というふうにしています。

エラーの出る個所は、
   Set WB = Workbooks.Open(FoundFiles(i))
です。

申し訳ありませんが、どうぞよろしくお願いいたします。

**************************************

Sub ファイル取得ボタン_Click()
  :
(中略)
  :
 ''検索パスとファイルパターンを指定してファイル検索
 FoundFiles = GetFile(myPath, FileName)

 If UBound(FoundFiles) < 0 Then
   MsgBox "該当ファイルが見つかりません"
   Exit Sub
 End If

 Dim WB0 As Workbook
  Set WB0 = ActiveWorkbook
 Dim WB As Workbook
 Dim ws As Worksheet
 For i = 0 To UBound(FoundFiles)
   Set WB = Workbooks.Open(FoundFiles(i))
   For Each ws In WB.Worksheets
     Select Case ws.Name
      Case "東京", "大阪", "名古屋"
        このシートより転記 ws, WB0
     End Select
   Next
   WB.Close False
   Set WB = Nothing
 Next
 MsgBox "転記終了!"
End Sub

【67471】Re:MsgBoxで指定したブックのデータをコ...
発言  りん E-MAIL  - 10/12/8(水) 4:11 -

引用なし
パスワード
   すず さん、こんばんわ。
>エラーの出る個所は、
>   Set WB = Workbooks.Open(FoundFiles(i))
>です。
kanabunさんの関数を試してみたら、
 For i = 0 To UBound(FoundFiles)
   Debug.Print i, FoundFiles(i)
 Next

カウンタがUBound(FoundFiles)の時には常にFoundFiles(i)は空白文字になっていました。EOF(テキストファイルの最後を示す制御文字)のみの行があるみたいです。

なので、
  For i = 0 To UBound(FoundFiles) - 1
>   Set WB = Workbooks.Open(FoundFiles(i))
>   For Each ws In WB.Worksheets
>     Select Case ws.Name
>      Case "東京", "大阪", "名古屋"
>        このシートより転記 ws, WB0
>     End Select
>   Next
>   WB.Close False
>   Set WB = Nothing
> Next
> MsgBox "転記終了!"
>End Sub

ですかね。

【67481】Re:MsgBoxで指定したブックのデータをコ...
お礼  すず  - 10/12/8(水) 21:21 -

引用なし
パスワード
   りん 様

お忙しいところ、ありがとうございました。
完璧です!!
ほんとに尊敬します。

kanabunさんも、いろいろとありがとうございました。
早くお二人のようなコードが書けるよう、勉強いたします。

またお世話になると思いますが、どうぞよろしくおねがいします。

【67482】Re:MsgBoxで指定したブックのデータをコ...
発言  kanabun  - 10/12/8(水) 22:21 -

引用なし
パスワード
   こんにちは〜
解決後にすみません。

2日間、ネットにアクセスできないところに出かけてました。

▼りん さん
> カウンタがUBound(FoundFiles)の時には常にFoundFiles(i)は空白文字
> になっていました。EOF(テキストファイルの最後を示す制御文字)
> のみの行があるみたいです。
>
> なので、
 おっしゃるとおりです。Debug.Printでイミディエイト・ウィンドウ
に表示してたのでその不具合に気が付きませんでした。
 フォローありがとうございました m(_ _)m

▼すず さん、
> 実行すると、やりたいことはできたのですが
ぼく自身はサーバーの環境がないので、ローカルディスク内で
DIRコマンドをテストしていました。
もちろんDIRコマンドはネットワークも検索できるので、
ここでご紹介したのですが、
ht tp://support.microsoft.com/?scid=kb%3Bja%3B168519&x=15&y=13
によると、たまに
> UNC(ユニバーサルいう規則)パスを使用する場合、
> 有効な情報を返さないことがあります。
そうです。
そういう不具合が発覚したときは、そこに書いてあるように、
>dir/dir$関数の代わりに OpenFile API を使って、検索します。
とか、
ここの掲示板の過去ログ↓にあるように FileSystemObjectの利用を
検討ください。
ht tp://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=62389;id=excel

【67516】Re:MsgBoxで指定したブックのデータをコ...
質問  すず  - 10/12/9(木) 22:28 -

引用なし
パスワード
   kanabun様

このたびはありがとうございました。
ご親切に、重要なポイントをお知らせください、感謝しております。
早速確認させていただきます。

そして、新たに問題が2点出てきてしまいました。

●問題点1
こちらで質問させていただいた日報のブック名を確認
したところ、日付の部分が単純に「101206」のようなyymmdd形式になって
いるものと、「2010-12-06」のように「-」で区切られている2パターンがありました。

ご教示いただいた「######」の部分をどのように変更したらよろしいでしょうか。

●問題点2
日報が保存されているフォルダが2つありました。
たとえば、フォルダAには「101206」のつく日報があり、フォルダBには「2010-12-06」のつく日報があるという具合になります。
その場合、mypathの部分をどのように変更すればよろしいでしょうか

お時間があるときで結構です。
本などでは、なかなか同じパターンがなく、私には調べきれませんでした。
どうかよろしくお願いいたします。

【67517】Re:MsgBoxで指定したブックのデータをコ...
発言  kanabun  - 10/12/9(木) 23:30 -

引用なし
パスワード
   ▼すず さん:
こんばんは〜

>そして、新たに問題が2点出てきてしまいました。
>
>●問題点1
>こちらで質問させていただいた日報のブック名を確認
>したところ、日付の部分が単純に「101206」のようなyymmdd形式になって
>いるものと、「2010-12-06」のように「-」で区切られている2パターンがありました。

>●問題点2
>日報が保存されているフォルダが2つありました。
>たとえば、フォルダAには「101206」のつく日報があり、フォルダBには「2010-12-06」のつく日報があるという具合になります。
>その場合、mypathの部分をどのように変更すればよろしいでしょうか

まず
>●問題点2
のために、myPathに セミコロン(;)で区切って複数のパス名を
記入するように 変更してみてください。
                 ↓
 myPath = "\\サーバ名\フォルダ名\;\\サーバ名\フォルダ名2\" '◆要変更

>●問題点1
については、InputBoxでの入力は これまでどおり yymmdd 形式で
入れておいてください。たとえば 「101120」と入力しますと、
プログラムのほうで、
*101120*.xls

*2010-11-20*.xls
と2種類の検索ファイル名を作成します。

で、プログラム内で これらを組み合わせて 計4種類の検索パターンを
作成し、これをDIRコマンドのパラメータにしています。
こんな風にです↓

DIR "\\サーバ名\フォルダ名\*101120*.xls" _
   "\\サーバ名\フォルダ名\*2010-11-20*.xls" _
   "\\サーバ名\フォルダ名2\*101120*.xls" _
   "\\サーバ名\フォルダ名2\*2010-11-20*.xls" _
   /b/s > "C:\Users\kanabun\AppData\Local\Temp\Dir.tmp"


上記変更点について修正したものを以下に示しますので、
新規モジュールに 下記をコピペしてテストしてみてください。
'---------------------------------------------- 標準モジュール2
Option Explicit
Sub ファイル取得ボタン_Click()
 Dim myPath As String
  myPath = "\\サーバ名\フォルダ名\;\\サーバ名\フォルダ名2\" '◆要変更
 Dim Filename As String
 Dim i As Long
 Dim FoundFiles() As String

 Filename = InputBox$("yymmdd形式でファイル名を指定", "ファイルの取得")
 If StrPtr(Filename) = 0& Then Exit Sub
 If Not (Filename Like "######") Then Exit Sub
 
 Filename = "*" & Filename & "*.xls;*20" _
      & Format$(Filename, "##-##-##") & "*.xls"
 
 ''検索パスとファイルパターンを指定してファイル検索
 FoundFiles = GetFile(myPath, Filename)

 If UBound(FoundFiles) < 0 Then
   MsgBox "該当ファイルが見つかりません"
   Exit Sub
 End If
 
 Dim WB0 As Workbook
  Set WB0 = Workbooks("コピー先Book.xls")'あらかじめ開いておく◆要変更
 Dim WB As Workbook
 Dim ws As Worksheet
 '--- ↓確認用
 For i = 0 To UBound(FoundFiles) - 1
   Debug.Print FoundFiles(i)
 Next
 '--- Open抽出 実行
 For i = 0 To UBound(FoundFiles) - 1
   Set WB = Workbooks.Open(FoundFiles(i))
   For Each ws In WB.Worksheets
     Select Case ws.Name
      Case "東京", "大阪", "名古屋"
        このシートより転記 ws, WB0
     End Select
   Next
   WB.Close False
   Set WB = Nothing
 Next
 Set WB0 = Nothing
 MsgBox "転記終了!"
End Sub

Private Sub このシートより転記( _
            ByVal ws As Worksheet, _
            ByVal WB0 As Workbook)
 Dim ws0 As Worksheet
  Set ws0 = WB0.Worksheets(ws.Name)
 Dim r As Range
 With ws
  Set r = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
  Set r = r.Resize(r.Rows.Count - 1, 3)
 End With
 r.Copy ws0.Range("D1")
  
End Sub

'サブフォルダを含むファイルの検索(ファイルリストを返す)
Private Function GetFile(myPath As String, _
            Filename As String) As String()
  Dim myPaths, Filenames
  Dim tmpPath As String
  Dim sCmd As String
  Dim i&, j&, ko As Long
 
  tmpPath = Environ$("Temp") & "\Dir.tmp"
  
  myPaths = Split(myPath, ";")
  Filenames = Split(Filename, ";")
  For i = 0 To UBound(myPaths)
    If Right$(myPaths(i), 1) <> "\" Then
      myPaths(i) = myPaths(i) & "\"
    End If
    For j = 0 To UBound(Filenames)
     sCmd = sCmd & " """ & myPaths(i) & Filenames(j) & """ "
    Next
  Next
  sCmd = "DIR " & sCmd & "/b/s > """ & tmpPath & """"
  'Debug.Print sCmd
  With CreateObject("WScript.Shell")
    ko = .Run("CMD /C " & sCmd, 7, True) 'Dirコマンド実行
  End With

  Dim io As Integer
  Dim buf() As Byte
  io = FreeFile()
  Open tmpPath For Binary As io '出力ファイルリスト取得
   ReDim buf(1 To LOF(io))
   Get #io, , buf
  Close io
  Kill tmpPath
  GetFile = Split(StrConv(buf, vbUnicode), vbCrLf)
End Function

【67529】Re:MsgBoxで指定したブックのデータをコ...
お礼  すず  - 10/12/10(金) 22:49 -

引用なし
パスワード
   kanabunさま

このたびは、本当にありがとうございます。
そんなことができるなんて、知らないことばかりで目からウロコです;;

今度確認するのが月曜日以降になりそうですが、実際にネットワーク環境で試させていただきます。
また報告させていただきますので、よろしくお願いいたします。
取り急ぎお礼まで。

【67672】Re:MsgBoxで指定したブックのデータをコ...
質問  すず  - 10/12/22(水) 21:54 -

引用なし
パスワード
   kanabunさま

このたびはお世話になっております。
間が空いてしまいましたが、以前ご教示いただいたコードを確認をさせていただいたところ、

 Filename = InputBox$("yymmdd形式でファイル名を指定", "ファイルの取得")

の「Filename」が青くハイライトされて、「コンパイルエラー 配列がありません」がでてしまい、デバッグするとPrivate Sub ファイル取得ボタン_Click()が黄色くドラックされてしまいました。

自宅では問題なかったのですが、会社では自宅⇔会社のメール禁止で、コードを一から入力したものですから、スペルミスかもしれませんが、ネットワーク環境ということもあり、パスの指定が悪いのかとも思っております。

サーバーに入っているフォルダを指定するため、ご教示いただいた、"\\
サーバー名\フォルダ名\"の形式で指定しましたが、エラーが出てしまいました。

何が原因か、もしお分かりになりましたがお教えください。
どうぞよろしくお願いいたします。

すず

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