Excel VBA質問箱 IV

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

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


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

【68499】他のフォルダにあるファイル名をひな形ファイルにつけて保存する方法 ケメ子 11/3/10(木) 21:58 質問[未読]
【68504】Re:他のフォルダにあるファイル名をひな形... Yuki 11/3/11(金) 11:41 発言[未読]
【68506】Re:他のフォルダにあるファイル名をひな形... Yuki 11/3/11(金) 12:17 発言[未読]
【68521】Re:他のフォルダにあるファイル名をひな形... ケメ子 11/3/13(日) 22:23 質問[未読]
【68522】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/14(月) 8:28 発言[未読]
【68524】Re:他のフォルダにあるファイル名をひな形... Yuki 11/3/14(月) 15:46 発言[未読]
【68527】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/16(水) 11:04 発言[未読]
【68528】Re:他のフォルダにあるファイル名をひな形... ケメ子 11/3/16(水) 21:56 お礼[未読]
【68530】Re:他のフォルダにあるファイル名をひな形... ケメ子 11/3/17(木) 20:29 質問[未読]
【68531】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/18(金) 9:03 発言[未読]
【68532】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/18(金) 10:24 発言[未読]
【68533】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/18(金) 11:11 発言[未読]
【68534】Re:他のフォルダにあるファイル名をひな形... UO3 11/3/18(金) 15:09 回答[未読]
【68535】Re:他のフォルダにあるファイル名をひな形... ケメ子 11/3/18(金) 22:23 お礼[未読]
【68824】Re:他のフォルダにあるファイル名をひな形... ケメ子 11/4/20(水) 21:30 お礼[未読]

【68499】他のフォルダにあるファイル名をひな形フ...
質問  ケメ子  - 11/3/10(木) 21:58 -

引用なし
パスワード
   先日は大変お世話になりました。

以前質問させていただいたコードを利用して、次の操作を加えることになりましたので、是非ご教示ください。

以前は、No.68149で質問させていただいた通り、A,B,Cの3つの別々のフォルダに入っている、同じファイル名のファイル同志でデータの転記を行うというものだったのですが、このたびファイルの作り方が変わってしまいました。

Aフォルダ…データのコピー元ファイルが入っている。
Bフォルダ…VLOOKUPで参照するためのデータが入っている
Cフォルダ…データの貼り付け先になる作業用ファイルが入っている

今回、Cフォルダ(作業用ファイルが保存されている)に、作業用ファイルの「ひな形」を入れ、それを、Aフォルダに入っているファイル名に【作業】と先頭に付け加えた名前で保存し、そのあとNo.68149で教えていただいたデータ転記操作をしたいと考えております。

なぜならば、毎月ヘッダーに違うコメントが入ったり、月によってファイルが増減するため、Aフォルダのファイル名=Cフォルダのファイル名として、必要な数分だけ揃えようという目的のためです。

手順は以下のようになります。

Step1. Cフォルダの作業用ファイルのひな形を開く。
    その際、ひな形は「読み取り専用を推奨する」になっているので、
    このダイアログを「いいえ」(編集可能で開く)にして開く

Step2. 開いたひな形に、Aフォルダと、Aフォルダと同名のBフォルダのファイル
    から転記作業を行う
    (No.68149のコードを利用したいと思います。)

Step3. Aフォルダと同名のファイル名を付けて保存する。
    その際、「読み取り専用を推奨する」は外しておきたい。

以上になります。
上記のステップですが、最終的にStep3の形になればよいので、先にひな形を、Aフォルダのファイル名と同じファイル名にコピーをしてしまい、あとで転記作業をする、というのでも構いません。

何度も申し訳ないのですが、どうぞよろしくお願いいたします。

【68504】Re:他のフォルダにあるファイル名をひな...
発言  Yuki  - 11/3/11(金) 11:41 -

引用なし
パスワード
   ▼ケメ子 さん:
こんにちは。
簡単に書くとこうだろうと思います。
前回の回答には目を通しておりませんので御自分で書いてください。
Sub Macro2()
  Dim wb As Workbook
  ' Step1
  SendKeys "{TAB}{ENTER}"
  Set wb = Workbooks.Open(Filename:="OpenするFileのFull Path", ReadOnly:=False)
  
  ' Step2
  ' 前回の回答を参照されること
  
  ' Step3
  wb.SaveAs Filename:="SaveするFileのFull Path", FileFormat:=xlNormal, _
       Password:="", WriteResPassword:="", _
       ReadOnlyRecommended:=True, CreateBackup:=False
End Sub

【68506】Re:他のフォルダにあるファイル名をひな...
発言  Yuki  - 11/3/11(金) 12:17 -

引用なし
パスワード
   ▼ケメ子 さん:
間違いがありました。
>  ' Step3
>  wb.SaveAs Filename:="SaveするFileのFull Path", FileFormat:=xlNormal, _
>       Password:="", WriteResPassword:="", _
>       ReadOnlyRecommended:=True, CreateBackup:=False

>       ReadOnlyRecommended:=False, CreateBackup:=False
にして下さい。

【68521】Re:他のフォルダにあるファイル名をひな...
質問  ケメ子  - 11/3/13(日) 22:23 -

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

御礼が遅くなり、大変失礼いたしました。
確認いたしましたが、1つのファイルでしたらYukiさまのコードでできました。

今回は、フォルダの中に40近くのファイルがあり、これらすべてのファイル名と同じ名前で(先頭に【作業】とつきます)ひな形ファイルをコピー、保存したいと思っています。

その場合はどのようにしたらよろしいでしょうか。
たびたび申し訳ありませんがよろしくお願いいたします。

【68522】Re:他のフォルダにあるファイル名をひな...
発言  UO3  - 11/3/14(月) 8:28 -

引用なし
パスワード
   ▼ケメ子 さん:

おはようございます。
あらかた、キーポイントについてはyukiさんからお答えが出ています。
現在のコードに当てはめるとすれば、少々効率面では難がありますが
たとえば以下。(例によって、読解力の乏しいUO3ですから勘違いあればご容赦)

現在のコードの

  cName = IsExists(cPath, aName, myPre)
  If cName <> "" Then
    Set cBook = Workbooks.Open(cPath & "\" & cName, UpdateLinks:=3) 'リンクの更新して開く
 
これに【かえて】

'雛形テンプレートを開く
'【作業】 & aName を cName として、この雛形テンプレートをcフォルダに名前をつけて保存
'このブックをcBookとしておく。

以降は現行コードのままでOKだと思います。

>Yuki さま
>
>御礼が遅くなり、大変失礼いたしました。
>確認いたしましたが、1つのファイルでしたらYukiさまのコードでできました。
>
>今回は、フォルダの中に40近くのファイルがあり、これらすべてのファイル名と同じ名前で(先頭に【作業】とつきます)ひな形ファイルをコピー、保存したいと思っています。
>
>その場合はどのようにしたらよろしいでしょうか。
>たびたび申し訳ありませんがよろしくお願いいたします。

【68524】Re:他のフォルダにあるファイル名をひな...
発言  Yuki  - 11/3/14(月) 15:46 -

引用なし
パスワード
   ▼ケメ子 さん:
>今回は、フォルダの中に40近くのファイルがあり、これらすべてのファイル名と同じ名前で(先頭に【作業】とつきます)ひな形ファイルをコピー、保存したいと思っています。
Sub Macro21()
  Dim wb   As Workbook
  Dim strDirA As String
  Dim strDirB As String
  Dim wbA   As Workbook
  Dim wbB   As Workbook
  Dim sht   As Worksheet
  Dim shtB  As Worksheet
  Dim FSO   As Object
  Dim FC   As Object
  Dim F    As Object
  
  strDirA = ThisWorkbook.Path & "\A\"
  strDirB = ThisWorkbook.Path & "\B\"

  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set FC = FSO.GetFolder(strDirA).Files
  
  AppActivate Application.Caption
  Application.ScreenUpdating = False
  For Each F In FC                  ' Aフォルダの中の
    If Right(F.name, 4) = ".xls" Then        ' エクセルファイルで
    If FSO.FileExists(strDirB & F.name) Then    ' Bフォルダに同じ名前があったら
    
      ' Step1 : Cフォルダの作業用ファイルのひな形を開く。
          ' その際、ひな形は「読み取り専用を推奨する」になっているので、
          ' このダイアログを「いいえ」(編集可能で開く)にして開く
      DoEvents
      SendKeys "{TAB}{ENTER}"
      Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\C\雛形1.xls", ReadOnly:=False)

      ' Step2 : 開いたひな形に、Aフォルダと、Aフォルダと同名のBフォルダのファイル
          ' から転記作業を行う
      Set wbB = Workbooks.Open(strDirB & F.name) ' Bフォルダのエクセルを開く
      ' シートのコピーは前回のもので書き直してください。
      For Each shtB In wbB.Worksheets       'シートをコピー
        On Error Resume Next
        Set sht = wb.Worksheets(shtB.name)
        If Not sht Is Nothing Then
          shtB.Cells.Copy
          sht.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                      SkipBlanks:=False, Transpose:=False
        End If
        On Error GoTo 0
      Next
      
    ' Step3             ' Bフォルダに「作業」を付けて保存
      Application.DisplayAlerts = False
      wb.SaveAs Filename:=strDirB & "作業" & F.name, _
           FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
      wb.Close
      wbB.Close False
      Application.DisplayAlerts = True
    End If
    End If
  Next
  Set FSO = Nothing
  Application.ScreenUpdating = True
End Sub

【68527】Re:他のフォルダにあるファイル名をひな...
発言  UO3  - 11/3/16(水) 11:04 -

引用なし
パスワード
   ▼ケメ子 さん:

こんにちは

提案です。

現在のファイル処理のループの中で雛形ブックを読み込んで、bファイル名をもとに
cファイルを作り出すのではなく、処理の最初に、まず必要なcファイルをcフォルダに
すべて生成、以降は現在のコードのまま実行という方式が、わかりやすいかもしれませんし
都度、雛形ファイルを読みこむより処理時間も短縮されるかもしれません。

Sub 作業ファイル作成()
  Dim tplName As String  '追加
  tplName = "雛形.xls"  '実際の雛形ブック名に。あるいは、これも登録セルから転記。
  '
  '
  '最初に以下のコードを
  '
  'まずcフォルダの【作業】・・ブックを削除
  For Each myFile In myFso.getfolder(cPath).Files
    cName = myFile.Name
    If LCase(myFso.GetExtensionName(cName)) = "xls" And cName <> tplName Then
      Kill cPath & "\" & cName
    End If
  Next
  
  '次に雛形ブックをaフォルダのブック名に【作業】を付加した名前でcフォルダに
  '名前を付けて保存
  Application.DisplayAlerts = False
  Workbooks.Open Filename:=cPath & "\" & tplName
  Application.DisplayAlerts = True

  For Each myFile In myFso.getfolder(aPath).Files
    cName = myPre & myFile.Name
    If LCase(myFso.GetExtensionName(cName)) = "xls" Then
      ActiveWorkbook.SaveAs Filename:=cPath & "\" & cName, ReadOnlyRecommended:=False
    End If
  Next
  
  ActiveWorkbook.Close False
 
  
  'この後は既存コードのまま
  
End Sub

【68528】Re:他のフォルダにあるファイル名をひな...
お礼  ケメ子  - 11/3/16(水) 21:56 -

引用なし
パスワード
   UO3さま Yukiさま

このたびは大変お世話になっております。
昨今の状況により、なかなか確認できず、御礼が遅れましたことをお詫びいたします。

まず、

 テンプレートを開く
    ↓
 元データと同じファイル名(先頭に【作業】をつける)で保存
    ↓
 転記作業

は、Yukiさまの最初に投稿してくださったコードと、UO3さまのヒントを、試行錯誤で自分なりに記述したところ、奇跡的にも思い通りに作業ファイルが出来上がりました!
本当にありがとうございました。

一点だけ、Yukiさまのコード

>SendKeys "{TAB}{ENTER}"
>Set wb = Workbooks.Open(Filename:="OpenするFileのFull Path", ReadOnly:=False)

のReadOnly:=False にすると、なぜか読み取り専用で開くか否かのダイアログが表示されてしまいました。

そこで、Trueにしたところ、ダイアログが表示されず、なおかつ作業ファイルも読み取り推奨ではなくなって、思い通りにすることができました。
なぜでしょうか・・・。

また、UO3さまご指摘のとおり、この手順だとちょっと処理速度に問題があります。

そこで、あらたにご考案いただいたコードをまた確認させていただきますので、処理速度を比べてまたご連絡いたします。

Yukiさまの、次に投稿してくださったコードも今後の参考とさせていただきたいと思います。ありがとうございました。

【68530】Re:他のフォルダにあるファイル名をひな...
質問  ケメ子  - 11/3/17(木) 20:29 -

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

いつもありがとうございます。
本日、2通りのコードでファイル5つだけ用意し、それぞれ時間を計ってみましたが・・・
はじめの、1つ1つひな形を作って転記という方は、5ファイルで38分もかかってしまいました。

そして、UO3さまにご考案いただいた、いったんファイルの数分だけひな形をコピーし、そのあと転記という方も、若干速くなったものの36分でした。
要するに5ファイルでやり取りした場合、1ファイル平均7分〜8分かかることになります。

本番は40ファイルになりますので、おそらく就業時間まるまるかかりそうです。

1つのファイルで試したときはどちらも2分くらいでしたので、探しに行くファイル数が多くなるとそれだけ時間がかかるようです。

そこでご相談ですが、ファイルの転記作業はさほど時間がかからないし、コードも変更のしようがないと思いますが、ファイルを探しに行く操作で、何かもう少し時間が短縮できるような方法はありますでしょうか?

もしくは、数式を貼り付ける際に、自動ではなく「手動」にする、ということはできますでしょうか?

あとで、再鑑する者がもう一度目視するのですが、その際にF9で更新をすればよいので、もし「手動」にする方法がありましたらご教示できればと思います。

いつも本当に申し訳ございません。
どうかよろしくお願いいたします。

【68531】Re:他のフォルダにあるファイル名をひな...
発言  UO3  - 11/3/18(金) 9:03 -

引用なし
パスワード
   ▼ケメ子 さん:

おはようございます。
ファイルの抽出自体は、FSOは、割合【遅い】方式です。
抽出だけならDIR関数のほうが早いですし、さらにAPIを使って、さらに
高速な方式もあります。ただし、今回の場合、少なくとも、1回は雛形を
【物理的に】開く必要がありますし、また、それを複数のcブックとして
【物理的に】保存する必要があり、ここは、いかんともしがたいところでしょうね。

代替の方式案として2つ。

1.ご提示した前処理の部分を、別プロシジャにします。
  今月の作業データ(cブック)を作成するのは、実際の作業の前に
  比較的余裕のあるときに実行しておき、実際の作業は、現行、お持ちの
  作業ファイル作成 プロシジャを動かします。
  トータルの処理時間はかわりませんが、事前準備を分離するという構え。
  なお、この時、セルに登録された種々のデータの取り込み部分が2箇所になり
  保守性に問題が出てくる恐れがあります。
  ですので、1つのモジュールに
  ・まず、セルから取り出す情報格納変数等をモジュールレベルの変数として記述。
  ・それらを取り込む共通プロシジャを準備
  という構えが考えられます。イメージ的には以下。

Option Explicit

'共通的に利用する変数をここにモジュールレベル変数として記述

Sub 事前処理()
  Call 登録データ取得
  '今回ご提案した事前処理コード部分
End Sub

Sub 作業データ作成()
  Call 登録データ取得
  '現行のコード(除く、セルからのデータ取得)
End Sub

Private Sub 登録データ取得()
  'ThisWorkbook.Sheet(1)から各変数への取り込み
End Sub

2.申し上げたようにファイル抽出自体はファイル数が40になろうと50になろうと
  あまり気にしなくていいと思いますが、課題は実際のcブックの保存。
  今回ご提案した形は、
  ・まず、雛形から全てのcブックを作成。
  ・あらためて作成済みのcブックにaブックあるいはbブックから情報を付加して
   もう一度、保存。
  ということで、cブックの保存が2回実行されます。
  少しコードは煩雑になりますが、
  ・最初に雛形ブックを開いておいて
  ・aブック、bブックから、雛形ブックに情報を反映させてcブックとして保存。
  ・これを繰り返す。
  こうすることで、処理時間としては、ほぼ現行の作業ファイル作成プロシジャと
  かわらなくなる効果が期待できます。

★まずは、1.をご検討いただけませんか?

【68532】Re:他のフォルダにあるファイル名をひな...
発言  UO3  - 11/3/18(金) 10:24 -

引用なし
パスワード
   ▼ケメ子 さん:

おはようございます。

アップ後、ふと思いついたんですが。

事前に処理するにせよ、一連の処理の最初に処理するにせよ、ご提示したコードは
cファイルを【エクセルとして事前に保存】するコードで、処理コストが大きくなる
わけです。
これを、雛形ブック含め、ブックを開かないで、cフォルダに必要なcブックを
【雛形ブックからのコピー】という方式にしてみました。処理時間は大幅に短縮される
可能性があります(希望的観測?)
で、コピーはFSOでもできますが、処理時間を考慮して、標準コードのみにしてみました。

★難点があります。読み取り専用の雛形をコピーした後、その属性をリセットしている
 つもりなんですが、コピー後、開くと、やはり読み取り専用になっています。
 なぜなのか、現在の私のレベルではわかっていないので、このあとも追求してみますが
 できれば、このあたり、yukiさんはじめ、エキスパートの方々のご助言が欲しいところです。

  Dim wkName As Variant    '追加
  Dim myPool As Collection   '追加
    ’
    ’
    ’
'事前処理開始
  Set myPool = New Collection
  
  'まずaPathにあるaファイルの名前をcファイル用に加工してmyPoolに取り込む
  wkName = Dir(aPath & "\*.xls")
  Do While wkName <> ""
    myPool.Add cPath & "\" & myPre & wkName
    wkName = Dir()
  Loop
  '雛形ブックをmyPoolに取り込んだcファイル名として【コピー】
  For Each wkName In myPool
    If Len(Dir(wkName)) > 0 Then Kill wkName
    FileCopy cPath & "\" & tplName, wkName
    SetAttr wkName, vbNormal '★ここがきいていない!
  Next
  
  Set myPool = Nothing
'事前処理終了

【68533】Re:他のフォルダにあるファイル名をひな...
発言  UO3  - 11/3/18(金) 11:11 -

引用なし
パスワード
   ▼ケメ子 さん:

こんにちは

いろいろ試行錯誤した結果、GetAttrあるいはSetAttrで相手にしている読み取り専用属性は
エクセルブックとしての属性とは異なるものだということがわかりました。

で、またもや思いつきですが、ご提示したファイルコピーロジックを以下のように。
1回だけ、エクセルとしてのI/Oが発生しますが、まぁ無視できるレベルではないかと。

★ただし、まだ課題があります。
 SendKeysを用いたyukiさんのアドバイス、ならびに、試行錯誤の結果、なぜか?
 うまくいったケメ子さんの報告のコードをこちらで試しても、うまくいきません。
 SendKeysは、きわめて不安定な機能で、インターラクティブな処理では効果があるけれど
 一連のVBAの処理の中で使った場合、それが効いたり効かなかったりすることも多いようですので
 今回のコードでは細工をしていません。ですからメッセージは最初に1回でますが、これに
 操作者がレスポンスすることが必要になります。

  Dim tempName As String    '追加
  Dim wkName As Variant    '追加
  Dim myPool As Collection   '追加
      '
      '
      '
'事前処理開始
  tempName = cPath & "\temp_" & tplName
  If Len(Dir(tempName)) > 0 Then Kill tempName
  
  'ここで雛形ブックを開いて、temp_雛形を保存
  Workbooks.Open cPath & "\" & tplName    '★ 課題
  ActiveWorkbook.SaveAs tempName
  ActiveWorkbook.Close
  
  Set myPool = New Collection
  
  'aPathにあるaファイルの名前をcファイル用に加工してmyPoolに取り込む
  wkName = Dir(aPath & "\*.xls")
  Do While wkName <> ""
    myPool.Add cPath & "\" & myPre & wkName
    wkName = Dir()
  Loop
  '雛形ブックをmyPoolに取り込んだcファイル名として【コピー】
  For Each wkName In myPool
    If Len(Dir(wkName)) > 0 Then Kill wkName
    FileCopy tempName, wkName
  Next
  
  Kill tempName
  Set myPool = Nothing
'事前処理終了

【68534】Re:他のフォルダにあるファイル名をひな...
回答  UO3  - 11/3/18(金) 15:09 -

引用なし
パスワード
   ▼ケメ子 さん:

試行錯誤継続結果、上でアップしたコードの内、2行を変更することで
開く際のメッセージを回避、またそれをもとに作成したcブックも読取り推奨なしで
できあがりました。

  Workbooks.Open Filename:=cPath & "\" & tplName, IgnoreReadOnlyRecomennded:=True
  ActiveWorkbook.SaveAs Filename:=tempName, ReadOnlyRecommended:=False

【68535】Re:他のフォルダにあるファイル名をひな...
お礼  ケメ子  - 11/3/18(金) 22:23 -

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

いつも本当に親身にお考えいただき、感謝いたします。。。
ご考案いただいたコードを試させていただきます。

今、拝見いたしましたので、連休明けに確認させていただき、またご報告させていただきます。

よい連休をお過ごしくださいませ。
取り急ぎお礼を申し上げます。

ケメ子

【68824】Re:他のフォルダにあるファイル名をひな...
お礼  ケメ子  - 11/4/20(水) 21:30 -

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

あれからひと月以上経ってしまい、お忘れになったかと思いますが、以前大変お世話になりましたケメ子です。

期末処理や、自身の引っ越しなどがあり、ご報告が遅くなりましたことをお詫びいたします。

あの後、マクロの処理スピードが異常に遅かった原因が分かりました。
数式を参照しているデータファイルがとてつもなく重く、軽量にしたところスッキリと処理されました!!

本当にいろいろご考案いただき、大変勉強になりました。
心よりお礼申し上げます。

まだ伺いたいことがたくさんありますが、またお世話になれたら幸甚です。
よろしくお願いいたします。

ケメ子

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