Excel VBA質問箱 IV

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

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


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

【72228】捺印数の照合 ピッポ 12/6/24(日) 0:07 質問[未読]
【72233】Re:捺印数の照合 UO3 12/6/24(日) 8:23 発言[未読]
【72237】Re:捺印数の照合 UO3 12/6/24(日) 9:59 発言[未読]
【72238】Re:捺印数の照合 ピッポ 12/6/24(日) 14:25 回答[未読]
【72239】Re:捺印数の照合 UO3 12/6/24(日) 14:49 発言[未読]
【72241】Re:捺印数の照合 ピッポ 12/6/25(月) 0:25 お礼[未読]
【72276】Re:捺印数の照合 ピッポ 12/7/7(土) 9:42 発言[未読]
【72279】Re:捺印数の照合 UO3 12/7/7(土) 17:34 発言[未読]
【72281】Re:捺印数の照合 ピッポ 12/7/8(日) 11:39 発言[未読]
【72282】Re:捺印数の照合 UO3 12/7/8(日) 16:18 発言[未読]
【72284】Re:捺印数の照合 ピッポ 12/7/8(日) 16:47 発言[未読]
【72286】Re:捺印数の照合 UO3 12/7/8(日) 18:44 発言[未読]
【72283】Re:捺印数の照合 UO3 12/7/8(日) 16:35 発言[未読]
【72285】Re:捺印数の照合 ピッポ 12/7/8(日) 18:15 発言[未読]
【72287】Re:捺印数の照合 UO3 12/7/8(日) 20:54 発言[未読]
【72288】Re:捺印数の照合 UO3 12/7/9(月) 10:29 発言[未読]
【72294】Re:捺印数の照合 ピッポ 12/7/9(月) 18:00 発言[未読]
【72298】Re:捺印数の照合 UO3 12/7/10(火) 12:31 発言[未読]
【72329】Re:捺印数の照合 ピッポ 12/7/12(木) 18:12 発言[未読]
【72334】Re:捺印数の照合 UO3 12/7/13(金) 9:45 発言[未読]
【72339】Re:捺印数の照合 ピッポ 12/7/14(土) 21:26 お礼[未読]
【72299】Re:捺印数の照合 UO3 12/7/10(火) 12:42 発言[未読]

【72228】捺印数の照合
質問  ピッポ  - 12/6/24(日) 0:07 -

引用なし
パスワード
   シート1のF列にハイパーリンクが設定されており、
ハイパーリンク先のフォルダにエクセルファイルが格納されています。
エクセルファイルを開くと、電子印が捺印されています。
シート1のAF列に電子印の捺印数が記載されていて数が一致していれば
I列に”問題なし”、異常なら”問題あり”と記載したい。

下記のように作成しましたが、”問題あり”と記載されてしまいます。

ご教授頂けませんでしょうか?


Option Explicit

Sub image_count()
Dim myFso As Object
Dim MyOb As Object
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim c As Range
  Dim ans As String
  Dim check As String
  Dim oFold As String
  Dim nFold As String
  Dim fName As String
  Dim i As Long
  Dim n As Long
  Dim myFile As Object
  Dim i1 As Long
  Dim i2 As Long
  Dim b As String
  Dim e As String
  Dim y As String
  Dim 捺印数 As String
  Dim cnt As Long
 
  Set myFso = CreateObject("Scripting.FileSystemObject")
  Set sh1 = Sheets("一覧表")
  Set sh2 = Sheets("元保管場所(データベース)")
    
  
  For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))
 
    i = c.Row
    fName = ""
    'F列にハイパーリンクなければスキップ
    If c.Offset(, 0).Hyperlinks.Count > 0 Then
      oFold = c.Offset(, 0).Hyperlinks(1).Address
       'リンク先フォルダが存在するものだけ
      If myFso.folderExists(oFold) Then
        For Each myFile In myFso.GetFolder(oFold).Files
          e = LCase(myFso.getextensionname(myFile.Name))
          'b = myFso.getbasename(myFile.Name)
捺印数 = Cells(i, 32)
          If e = "xls" Then
            'If b Like "*" Then
             fName = myFile.Name
        'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
             Workbooks.Open oFold & "\" & fName
             With sh1.Cells(i, "J")
 
  y = Workbooks(2).Sheets.Count
  If y >= 2 Then        
  For n = 1 To y        
  Sheets(n).Select       
  i1 = 0            
  i2 = 0            
  Range("A1:BV9").Select    'シートの検索範囲を指定
  With ActiveSheet
  For Each MyOb In .Shapes 'drop downは対象外にする
  If MyOb.Type <> 8 Then  'このifが無いとdrop存在時エラーが発生する
  If Not Intersect(MyOb.TopLeftCell, Selection) Is Nothing Then
  '左上角指定で選択されたシートのみを対象にする
  'このifが無いとシート全体が対象になる
  i1 = i1 + 1
  Select Case MyOb.Type
  Case msoPicture
  i2 = i2 + 1
  End Select
  End If
  End If
  Next
  End With
  'MsgBox ("選択範囲内のDrawingObjectの数:" & i1)
  'MsgBox ("選択範囲内のpicterの数:" & i2)
  If i2 = 捺印数 Then
  Else
  GoTo NG
  End If
  Next n
   'Application.Run "sheets_count"
   End If
  i1 = 0
  i2 = 0
  Range("A1:BV9").Select    'シートの検索範囲を指定
  With ActiveSheet
  For Each MyOb In .Shapes
  If MyOb.Type <> 8 Then  
  If Not Intersect(MyOb.TopLeftCell, Selection) Is Nothing Then
    i1 = i1 + 1
  Select Case MyOb.Type
  Case msoPicture
  i2 = i2 + 1
  End Select
  End If
  End If
  Next
  End With
  'MsgBox ("選択範囲内のDrawingObjectの数:" & i1)
  'MsgBox ("選択範囲内のpicterの数:" & i2)
  If i2 = 捺印数 Then
  ActiveWorkbook.Close savechanges:=False
                 .Value = "問題なし"
                 .Font.ColorIndex = 1
  Else
NG:
   ActiveWorkbook.Close savechanges:=False
                  .Value = "問題あり"
                  .Font.ColorIndex = 3
  
  End If
  End With
                 
          End If
         Next
      End If
    End If
  
  Next
 
  Set myFso = Nothing
  Set sh1 = Nothing
  Set sh2 = Nothing
  MsgBox cnt & " 個のファイルが捺印されています。", vbInformation

End Sub

【72233】Re:捺印数の照合
発言  UO3  - 12/6/24(日) 8:23 -

引用なし
パスワード
   ▼ピッポ さん:

おはようございます

コードはざっと眺めただけで読んでいません。

>シート1のAF列に電子印の捺印数が記載されていて数が一致していれば
>I列に”問題なし”、異常なら”問題あり”と記載したい。
>下記のように作成しましたが、”問題あり”と記載されてしまいます。

問題ありの場合は問題ありとでていいわけですから、問題がないはずなのに問題有りとなる。
そういうことなんでしょうね。

Shape は セル範囲を特定して抽出するといった『器用なこと』はできません。
そのシート上のすべてのShapeが抽出されます。ですから、抽出されたShapeが
特定領域にあるのかどうかのチェックが必要です。
そのあたりが原因ではないですか?

ところで、本当に、このこーどのイメージで書かれているのでしょうか?
最初のほうはインデントがつけられていますが、あとは、まったくついていませんね。
これだけIF分岐やFor/Nextループが入り組んだコードですから、このイメージだとすると
コードを追いかけるのも至難の業ではないですか?

それと、コード記述そのものに、改善の余地があるかもです。
c.Offset(,0). これは Offsetの意味が全くないですね。単純に c. でよろしいんですが。
あと、 With も必ずしも有効に使われていない(With の必要がない)ところもありますね。

【72237】Re:捺印数の照合
発言  UO3  - 12/6/24(日) 9:59 -

引用なし
パスワード
   ▼ピッポ さん:

前言一部撤回します。
いま、とにかくアップされたコードのインデントをつけなおして、あらためて
眺めてみました。
Shapeの領域については、ちゃんとチェックしておられましたね。

ただ、やはり、これだけ入り組んだIF分岐ですから、そのあたりでコードミスがあるのかもしれません。
説明された要件だけを考えると、もっとシンプルなコードにもなりそうですが。

いずれにしても、(インデントをつけ間違えているかもしれませんけど)
こーろの中程で、開いたシートのシート数により、シートループと1枚もののばあいの処理を
わけているところがあるようですね。
(わけなくてもできるんですけどね、ここは)
そのところなんですが、複数シートの処理のあと、必ず1枚ものの処理が実行されていませんか?

どう見ても、

If y >= 2 Then        

この対になる End If の後に、1枚ものの処理が記述されているように思えますが?

【72238】Re:捺印数の照合
回答  ピッポ  - 12/6/24(日) 14:25 -

引用なし
パスワード
   ご指摘ありがとうございます。


そのところなんですが、複数シートの処理のあと、必ず1枚ものの処理が実行されていませんか?
>
>どう見ても、


If y >= 2 Then        
>
>この対になる End If の後に、1枚ものの処理が記述されているように思えますが?


上記に関してですが、1枚もののシートというくくりの中には
純粋に1枚シートのものと複数シートが存在するものが幾つかあります。
その複数シートのシート1だけ捺印してあるものがあります。

捺印の確認の順番としてまず複数シート(全てのシートに捺印してある)の処理を確認します。
この時、一枚シートの複数シートが存在してあるものは”問題あり”と
記載されます。
次に、1枚ものの処理を行い、一枚シートの複数シートは
”問題なし”と記載される流れになっています。

ごちゃごちゃしたコードになってしまい大変申し訳ありません。
他の方法が思いあたらなくこのようなコードになってしまいました。

【72239】Re:捺印数の照合
発言  UO3  - 12/6/24(日) 14:49 -

引用なし
パスワード
   ▼ピッポ さん:

こんにちは

アップされたコードそのままで上で申し上げたようなことを加味してみました。
拡張子を限定するならDIR関数のほうがやや使いやすいとも思いますが、とりあえずFSOのまま。
で、私が書くとすれば、さらにシェープの場所のチェックのループを無くしたものにするかもしれませんが
とりあえず。cntには、オリジナルのコードのとおり、何もセットしていません。
また、問題有り、なしの条件はオリジナルコードから推測して、たぶん、こんな分岐だろうとあたりをつけていますが
勘違いあれば指摘してください。

Sub Sample()
  Dim myFso As Object
  Dim MyOb As Object
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim c As Range
  Dim oFold As String
  Dim fName As String
  Dim i As Long
  Dim myFile As Object
  Dim i2 As Long
  Dim e As String
  Dim 捺印数 As String
  Dim cnt As Long
  Dim fWb As Workbook
  Dim fSh As Worksheet
  
  Application.ScreenUpdating = False
  
  Set myFso = CreateObject("Scripting.FileSystemObject")
  Set sh1 = Sheets("一覧表")
  Set sh2 = Sheets("元保管場所(データベース)")
  
  
  For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))
    i = c.Row
    捺印数 = sh1.Range("AF" & i).Value
    With sh1.Range("J" & i)
      .ClearContents
      .Font.ColorIndex = xlAutomatic
    End With
    fName = ""
    'F列にハイパーリンクなければスキップ
    If c.Hyperlinks.Count > 0 Then
      oFold = ThisWorkbook.Path & "\" & c.Hyperlinks(1).Address
      'リンク先フォルダが存在するものだけ
      If myFso.FolderExists(oFold) Then
        For Each myFile In myFso.GetFolder(oFold).Files
          e = LCase(myFso.getextensionname(myFile.Name))
          If e = "xls" Then
            fName = myFile.Name
            'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
            Set fWb = Workbooks.Open(oFold & "\" & fName)
            For Each fSh In fWb.Worksheets
              i2 = 0
              For Each MyOb In fSh.Shapes '
                If MyOb.Type = msoPicture Then  '
                  If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                  '左上角指定で選択されたシートのみを対象にする
                  'このifが無いとシート全体が対象になる
                    i2 = i2 + 1
                  End If
                End If
              Next
              fWb.Close False
              If i2 <> 捺印数 Then Exit For
            Next
            If i2 <> 捺印数 Then Exit For
          End If
        Next
        
        With sh1.Range("J" & i)
          .Value = "問題" & IIf(i2 = 捺印数, "なし", "あり")
          .Font.ColorIndex = IIf(i2 = 捺印数, xlAutomatic, 3)
        End With

      End If
      
    End If
  Next
  
  Application.ScreenUpdating = True
  
  MsgBox cnt & " 個のファイルが捺印されています。", vbInformation

End Sub

【72241】Re:捺印数の照合
お礼  ピッポ  - 12/6/25(月) 0:25 -

引用なし
パスワード
   ご多忙の中、早急なご対応に感謝致します。


現在、検証しながら動作確認を致しております。
検証時間に時間を要すると思いますが、
ご不明な点が発生した場合にご回答頂けたら幸いです。

この度はありがとうございました。

【72276】Re:捺印数の照合
発言  ピッポ  - 12/7/7(土) 9:42 -

引用なし
パスワード
   お世話になります。

質問1:
シートが1つの場合は問題なく照合できたのですが
複数シートがある際、捺印数の照合した結果、”問題なし”としか
表示されないのですがどのうように訂正すればよろしいでしょうか?


質問2:

下記のコードの構造を教えて頂きたいのですが、図形(msoPicture)
があればi2に1が代入されるというのは理解できるのですが、なぜ
i2 <> 捺印数なのか理解できませんでした。
恐れ入りますがご指導頂ければ幸いです。


If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                                                            i2 = i2 + 1
                    End If
                  End If
                Next
              fWb.Close False
              If i2 <> 捺印数 Then Exit For


Option Explicit

Sub image_count()
 Dim myFso As Object
  Dim MyOb As Object
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim c As Range
  Dim oFold As String
  Dim fName As String
  Dim i As Long
  Dim myFile As Object
  Dim i2 As Long
  Dim e As String
  Dim 捺印数 As String
  Dim シート数 As String
  Dim y As Long
  Dim fWb As Workbook
  Dim fSh As Worksheet


  Set myFso = CreateObject("Scripting.FileSystemObject")
  Set sh1 = Sheets("一覧表")
  Set sh2 = Sheets("元保管場所(データベース)")
 
 
  For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))
    i = c.Row
    捺印数 = sh1.Range("AF" & i).Value
    With sh1.Range("J" & i)
     
      .Font.ColorIndex = xlAutomatic
    End With
    fName = ""
    'F列にハイパーリンクなければスキップ
    If c.Hyperlinks.Count > 0 Then
      oFold = c.Offset(, 0).Hyperlinks(1).Address
      'リンク先フォルダが存在するものだけ
      If myFso.FolderExists(oFold) Then
        For Each myFile In myFso.GetFolder(oFold).Files
          e = LCase(myFso.getextensionname(myFile.Name))
          If e = "xls" Then
            fName = myFile.Name
            シート数 = sh1.Range("AG" & i).Value
            'ワークシート数が1つの場合
            If シート数 = 1 Then
            
            'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
            Set fWb = Workbooks.Open(oFold & "\" & fName)
              For Each fSh In fWb.Worksheets
                i2 = 0
                For Each MyOb In fSh.Shapes '
                  If MyOb.Type = msoPicture Then  '
                    If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                    '左上角指定で選択されたシートのみを対象にする
                    'このifが無いとシート全体が対象になる
                      i2 = i2 + 1
                    End If
                  End If
                Next
              fWb.Close False
              If i2 <> 捺印数 Then Exit For
              Next
            If i2 <> 捺印数 Then Exit For
            End If


            If シート数 >= 2 Then
            
            'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
            Set fWb = Workbooks.Open(oFold & "\" & fName)
              For Each fSh In fWb.Worksheets
              y = fWb.Sheets.Count
              
              For n = 1 To y        'シート数分だけループ
              Sheets(n).Select
              i2 = 0
             
              For Each MyOb In fSh.Shapes '
              
                If MyOb.Type = msoPicture Then  '
                
                
                  If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                  '左上角指定で選択されたシートのみを対象にする
                  'このifが無いとシート全体が対象になる
                    i2 = i2 + 1
                    
                  End If
                End If
              Next
              
              If i2 <> 捺印数 Then Exit For


              Next
              
              
              fWb.Close False
                If i2 = 捺印数 Then Exit For
                
              Next
           
            End If
            

          End If
        Next
    
        With sh1.Range("J" & i)
          .Value = "問題" & IIf(i2 = 捺印数, "なし", "あり")
          .Font.ColorIndex = IIf(i2 = 捺印数, xlAutomatic, 3)
        End With

      End If
   
    End If
  Next
 
  Application.ScreenUpdating = True
 
  MsgBox cnt & " 個のファイルが捺印されています。", vbInformation

End Sub

【72279】Re:捺印数の照合
発言  UO3  - 12/7/7(土) 17:34 -

引用なし
パスワード
   ▼ピッポ さん:

こんにちは

質問を2つアップしておられますが、それを回答する前にというか、回答するためにというか
以下メモします。

1.まず、最初の質問そのもので、レイアウトの説明としてはF列にハイパーリンクがセットされている。
 
  ハイパーリンク先のブックのシート1のAF列に捺印数が記入してあると、これだけでした。
  そのハイパーリンクはフォルダへのリンクなのかブックへのリンクなのかの説明はありません。
  AF列ということは、複数行に捺印数が記入されているのでしょうけど、その複数の捺印数って
  何を意味するんだろうという説明がありません。
  また、その捺印というのが、どこに、どういうようにあるのか、捺印数というのはどの単位の
  (ブックの?シートの?)捺印数七日の説明もありません。

  ★なので、すべてコードから推測しています。

2.で、いくつか疑問におもうことをコメントしました。
  たとえばシートが1枚でも複数枚でも、処理には関係ないのでは?
  このように申し上げ、その推測というか前提でコードをアップしました。

3.それに対して、いやいや、それは違うんです。こういう構成なのでシート数によって処理を変更
  するんですというレスをいただけるならまだしも、それがないまま、今回、そちらでアップされたコードでは
  やはりシート数を何かの処理の材料にしておられる。しかも、コードを読むと、AG列にあるようですね。
  AG列については、今まで説明に出てきましたっけ?
  その他にも、こちらでアップしたコードをかなり変更しておられる。それはいいんです。
  こちらの誤解があるのでしょうから。でも、それであれば、そこを説明して、だからこうしたんだと
  説明がいただきたいですね。推測のどこまでが正しく、どこが誤解なのかがまったくわかりません。
  (こちらがアドバイスした Offset(,0) は意味ないですよというところを無視されているのはご愛敬だとしても)

ということです。
なので、シートが1つの場合は・・・という質問にも、そもそもがわからないので
お答えできませんし、なぜi2 <> 捺印数なのか という質問にもおこたえできないですね。
なぜかといわれると、問題有り、なしの条件をそのように理解したからです。
これについても、最初に、ちらっと問題有り無しの条件はどういうものなんだろうということをつぶやいた
記憶があります。それに対して、こういう条件で判定しますという説明は全くないですね。

この条件って、本件で、きわめて重要なファクターですけど、その説明がないので
コードを読んで推測せざるを得ない。で、失礼ながら、コードが読みにくいと言うこともさることながら
コードは、おそらく正しいロジックにはなっていない(だから質問された)と思われますから
こちらで検討する前提というか材料というか、それがない状態だと言うことを理解いただけませんか?

【72281】Re:捺印数の照合
発言  ピッポ  - 12/7/8(日) 11:39 -

引用なし
パスワード
   お世話になります。

 言葉足らずの結果大変無礼な発言として
 ご迷惑をおかけしてしまい申し訳ありません。

 結果的に打ち切る形となってしまいましたが、
 当時は極力自分自身でチャレンジしたかったという思いが強かった為、
 ある程度のヒントを頂ければ自力でできると思い
 一度締めさせて頂く形になってしまいました。
  Offset(,0)は結果的には無視した形となりましたが、後ほど変更する可能性も  あるので
  自分自身の目印として形を保持したかった為です。


  もう一度やりたいことを一から説明させてください。

1.基本情報
 ワークシートについて
 F列11行目以降に複数行に渡りフォルダへのリンクのハイパーリンクが設定されています。
 J列11行目以降に判定結果(問題あり、問題なし)の入力欄があります。
 AF列11行目以降に捺印してある数が入力されています。
 AG列11行目以降にワークシート数が入力されています。

2.捺印について
 ハイパーリンク先のワークブックのワークシートのA1:BV9の範囲内に
 msoPictureの形式の図形(捺印)があります。

3.コード進行
 F列11行目のハイパーリンク先にあるエクセルファイルをオープンすると、
 AF列11行目に指定されている数の捺印がしてあるかどうかを判定します。
 エクセルファイルの
 この時にAG列11行目のワークシート数が一つの場合はシート1のみを判定を行い、ワークシートが2つ以上の場合は2つ以上のワークシート
 を全て判定を行います。(各ワークシートにはAF列11行目に入力されている数の捺印があることが条件)
 これらの条件を満たした時に、J列の判定結果欄に問題なしと入力する。(満たさなければ、問題ありと入力)
 同様の操作でF列12行目に移行します。

4.追加として
項目3.の「F列11行目のハイパーリンク先にあるエクセルファイルをオープンすると、」と
ありますが、これも複数のエクセルファイルをオープンできるようにしたい。


これらの内容で目的としては伝わりましたでしょうか?
コードもなるべく、無理がない限り従来のコードをベース
使用して頂けると今後の参考になります。

【72282】Re:捺印数の照合
発言  UO3  - 12/7/8(日) 16:18 -

引用なし
パスワード
   ▼ピッポ さん:

こんにちは

> 当時は極力自分自身でチャレンジしたかったという思いが強かった為、
> ある程度のヒントを頂ければ自力でできると思い
> 一度締めさせて頂く形になってしまいました。

これは、とてもいいことだと思います。
申し上げたかったのは、こちらでは、ピッポさんのシートのレイアウト含めて実行状況がみえないわけで
一緒に問題解決するために、そのあたりの情報をクリアにしたいということです。

追加で説明いただいたことを、今から精査し、まは、同じ実行環境を作成し
また、そこで、何をどのようにやるのかという要件を理解するところからはじめます。

その第一歩といいますか、一覧シートをアクティブにして、以下のコードを実行し
メッセージされるパスがどういう文字列なのか教えていただけますか?

Sub check()
  MsgBox Range("F11").Hyperlinks(1).Address
End Sub

【72283】Re:捺印数の照合
発言  UO3  - 12/7/8(日) 16:35 -

引用なし
パスワード
   ▼ピッポ さん:

もう1つ。
前回なかったAG列のシート数ですが、こういう考え方でいいですか?

ハイパーインク先の実際のシート数がどうあれ
1.AG列が1なら、リンク先ブックの最初の(一番左の)シートのみをチェック
2.AG列に数字があれば、リンク先ブックのすべてのシートをチェックする。
3.仮にAG列に100とあれば、リンク先ブックのシートをすべててチェックするが、実際のシート数は100ではなく3かもしれない。これはかまわない?
4.リンク先フォルダに複数ブックがあった場青、そのすべてがOKなら問題なし、1つでもOKじゃないブックがあれば問題有り?
5.パイパーリンク先フォルダがない場合は問題有り?
 ハイパーリンク先フォルダはあるけど、そのなかにブックが1つもない場合は問題有り?

【72284】Re:捺印数の照合
発言  ピッポ  - 12/7/8(日) 16:47 -

引用なし
パスワード
   お世話になります。

問題解決の為に、実行実行環境の情報を公開することがこちら側の責務
だと今回あらためて痛感させられました。


>メッセージされるパスがどういう文字列なのか教えていただけますか?
>
>Sub check()
>  MsgBox Range("F11").Hyperlinks(1).Address
>End Sub

実行いたしました。
リンク先には以下のような階層になっています。
\\Svr-mhsは社内のサーバー名です。

\\Svr-mhs\仕事\data\電子保管記録一覧表\test\移動先

【72285】Re:捺印数の照合
発言  ピッポ  - 12/7/8(日) 18:15 -

引用なし
パスワード
   お世話になります。


ハイパーインク先の実際のシート数がどうあれ
>1.AG列が1なら、リンク先ブックの最初の(一番左の)シートのみをチェック

はい。その通りです。


>2.AG列に数字があれば、リンク先ブックのすべてのシートをチェックする。

はい。その通りです。

>3.仮にAG列に100とあれば、リンク先ブックのシートをすべててチェックするが、実際のシート数は100ではなく3かもしれない。これはかまわない?

はい。その通りです。
実際のシートが3なら3つのシートをチェックします。

>4.リンク先フォルダに複数ブックがあった場青、そのすべてがOKなら問題なし、1つでもOKじゃないブックがあれば問題有り?

はい。その通りです。
複数ブックがあった場合すべてがOKで問題なしと表示したいです。


>5.パイパーリンク先フォルダがない場合は問題有り?
> ハイパーリンク先フォルダはあるけど、そのなかにブックが1つもない場合は問題有り?

はい。その通りです。
ハイパーリンク先にフォルダorブックが存在しない場合は問題あり
としたいです。

【72286】Re:捺印数の照合
発言  UO3  - 12/7/8(日) 18:44 -

引用なし
パスワード
   ▼ピッポ さん:

なるほど。ネットワーク上にあったんですね。
すっきりしました。
(フォルダパスがハイパーリンクでは相対パスになると理解していて、そのわりには
なぜ、そのまま、FSOに与えて大丈夫なんだろうと不思議でした)

当方でも、ネットワーク上にテストフォルダを準備し、この後のレスでkたえてもらったことも踏まえながら
取り組み開始します。

【72287】Re:捺印数の照合
発言  UO3  - 12/7/8(日) 20:54 -

引用なし
パスワード
   ▼ピッポ さん:

>コードもなるべく、無理がない限り従来のコードをベース
>使用して頂けると今後の参考になります。

アップされたコードは、以前にももう下げたとおり、インデントが正しくついていません。
ですので、各ロジックの固まりが、どのように遷移するのか、どの単位でループするのか
はなはだ、わかりにくくなっています。

以下は、アップされたコードの中の細かなコードを削除し、大きな流れを把握する部分のみを残して
それにインデントをつけたものです。

1組の If/End If や For/Next が同じ位置(桁)から始まっています。
まずは、これを印刷し、セロハンテープで貼り合わせて1枚の紙にして、ひとかたまりのブロックを
そのレベル別に(始まっている桁別に)蛍光ペンあたりで色別に塗り分けた上で
じっくりと、【流れ】をおいかけてください。

とくに後半のシートが複数ある場合の流れで、随分奇妙なループになっていることが
わかるはずです。

まず、このあたりから整理されたらよろしいかと思います。

For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))

  捺印数 = sh1.Range("AF" & i).Value
  
  'F列にハイパーリンクなければスキップ
  If c.Hyperlinks.Count > 0 Then
  
    'リンク先フォルダが存在するものだけ
    If myFso.FolderExists(oFold) Then
    
      For Each myFile In myFso.GetFolder(oFold).Files
      
        If e = "xls" Then
        
          シート数 = sh1.Range("AG" & i).Value
          
          'ワークシート数が1つの場合
          If シート数 = 1 Then
          
            'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
            Set fWb = Workbooks.Open(oFold & "\" & fName)
            
            For Each fSh In fWb.Worksheets
              i2 = 0
              For Each MyOb In fSh.Shapes '

                If MyOb.Type = msoPicture Then  '
                  If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                    '左上角指定で選択されたシートのみを対象にする
                    'このifが無いとシート全体が対象になる
                    i2 = i2 + 1
                  End If
                End If

              Next

              fWb.Close False
              If i2 <> 捺印数 Then Exit For

            Next

            If i2 <> 捺印数 Then Exit For   
            
          End If
          
          
          If シート数 >= 2 Then
          
            'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
            Set fWb = Workbooks.Open(oFold & "\" & fName)
            
            For Each fSh In fWb.Worksheets
            
              y = fWb.Sheets.Count
              
              For n = 1 To y        'シート数分だけループ
              
                i2 = 0
                
                For Each MyOb In fSh.Shapes '
                
                  If MyOb.Type = msoPicture Then  '
                  
                  
                    If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                    '左上角指定で選択されたシートのみを対象にする
                    'このifが無いとシート全体が対象になる
                    i2 = i2 + 1
                    
                    End If
                    
                  End If
                  
                Next
                
                If i2 <> 捺印数 Then Exit For
              
              Next
              
              fWb.Close False
              If i2 = 捺印数 Then Exit For
            
            Next
          
          End If
          
          
        End If
      Next
      
      '問題有り無しをJ列に記入
    
    End If
  
  End If

Next

【72288】Re:捺印数の照合
発言  UO3  - 12/7/9(月) 10:29 -

引用なし
パスワード
   ▼ピッポ さん:

上で申し上げたように、まずは現行のコードの流れをチェックし、【正常化】されるところから
進めていかれるとよろしいかと思いますが、1つだけ。

AG列のシート数に関する質問に対して
・ここが1ならどれだけシートがあろうと最初のシートのみチェック
・ここが2以上なら、そのブックのすべてのシートをチェック

このように回答していただきましたが、それなら、たとえば、ここに 3 とか 10 とかいう数値は
入らないのですね?本当にそうなんですか?

1枚だけかすべてかという区分なら、1か、それ以外か でいいわけですし、たとえば、ここが * なら1枚だけとか
そういう要件でもよさそうに思えます。

一方、アップされたコード、1の時でも、すべてのシートをチェックしておられるのも不思議なところですが
2以上の場合、すくなくとも、その数値分のループをしておられますよね。(このところのロジックはへんですけど)
たとえば、シートが3枚しかないのに、AG列に 1000 とあると 100回のループになっています。

AG列の数値と、それによるチェック条件をもう一度説明いただけませんか?

【72294】Re:捺印数の照合
発言  ピッポ  - 12/7/9(月) 18:00 -

引用なし
パスワード
   お世話になります。


現在ご指摘頂いた通りコードの流れをチェックしている最中です。


AG列のシート数に関してですが、私の伝え方が間違っていたかもしれません。

シート数が1なら最初のシートのみをチェック。
シート数が2なら最初とシート2をチェック。
シート数が3なら最初とシート2とシート3をチェックする
というようにシート数に応じてチェックします。


>一方、アップされたコード、1の時でも、すべてのシートをチェックしておられるのも不思議なところですが
>2以上の場合、すくなくとも、その数値分のループをしておられますよね。(このところのロジックはへんですけど)
>たとえば、シートが3枚しかないのに、AG列に 1000 とあると 100回のループになっています。

上記に関してですが、私自身全く理解できておりません。
シート数が1の時には最初のシートだけをチェックしていると認識していました。
コードの修正をお願いできないでしょうか?
どこがどのように相違があるか見比べてみたいです。


質問に関する内容が以上で不足がないでしょうか?

【72298】Re:捺印数の照合
発言  UO3  - 12/7/10(火) 12:31 -

引用なし
パスワード
   ▼ピッポ さん:

こんにちは

コードをアップしますが、コメントを少々。

・まず、何度か申し上げている通り、シート指定が1枚であれ複数枚であれ、処理を分ける必要はありません。
 すべてそのブックに対して For インデックス = 1 To シート数 で処理すれば、
 1 To 1 あるいは 1 To 3 といったようにできますので。
・ただ、なぜ、ピッポさんが、1かそうじゃないかに、こだわるんだろうと、考えてみました。
 もしかして・・・アップ済みのコード(オリジナルのピッポさんのコードロジックから想像したもの)では
 たとえば 捺印数指定が 4 、シート数が 3 。この場合、これら3つのシートがすべて、それぞれ 4つの捺印が必要。
 こんなロジックになっていました。でも、この 3つのシートの捺印数をあわせて、4 ということだったのかなと。
 以下のコードは、その新しい解釈によるコードになっています。(元戻しはたやすいので、違っていれば指摘願います)
・また、ロジックをシンプルにするために、指定フォルダの全ブックの全シートをチェックするようにしました。
 従来のコードは、フォルダごとに問題あり/なしをセットするわけですから、1枚でもおかしなシートがあれば
 その時点で、ループを抜けるようになっていました。このほうが、かっこいいロジックなんですが、ループ制御の
 コードが煩雑・複雑になり、今後、ピッポさんが改造されるとこを考えて、素直というか愚直なロジックにしました。
・ちょっと気になりましたので、問題ありの場合、どういう問題だったのかの補足も表示しました。
 でも、運用面としては、それじゃ、そのフォルダのどのブックが具体的に問題があったのか という質問が利用者から
 でた場合、それはわかりませんね。そういう必要があるなら、ブック明細を別表で作成するのがいいかもしれませんね。

ともあれ、以下。
あまり十分なテストはしていませんし要件の誤解も、まだまだ多々あるかもしれませんがお試しください。

Sub Sample2()
  Dim myFso As Object
  Dim MyOb As Object
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim c As Range
  Dim oFold As String
  Dim fName As String
  Dim i As Long
  Dim myFile As Object
  Dim i2 As Long
  Dim e As String
  Dim 捺印数 As Variant    '★データ型は念のためVariant
  Dim シート数 As Variant   '★データ型は念のためVariant
  Dim y As Long
  Dim fWb As Workbook
  Dim fSh As Worksheet
  Dim cnt As Long
  Dim sid As Long
  Dim fdFound As Boolean
  Dim nosBooks As Long
  Dim okBooks As Long
  Dim okSpec As Boolean
  Dim rmks As String
  Dim mycIndex As Long
  
  Application.ScreenUpdating = False
  Set myFso = CreateObject("Scripting.FileSystemObject")
  Set sh1 = Sheets("一覧表")
  Set sh2 = Sheets("元保管場所(データベース)")


  For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))
    i = c.Row
    捺印数 = sh1.Range("AF" & i).Value
    シート数 = sh1.Range("AG" & i).Value  'データ数もここで
    With sh1.Range("J" & i)
      .Clear   ' このほうがいいね
    End With
    
    fdFound = False
    okSpec = False
    nosBooks = 0
    okBooks = 0
    
    'F列にハイパーリンクなければスキップ
    If c.Hyperlinks.Count > 0 Then
      'ラインごとの変数初期化
      oFold = c.Offset(, 0).Hyperlinks(1).Address
      'リンク先フォルダが存在するものだけ
      If myFso.FolderExists(oFold) Then
        If IsNumeric(捺印数) And IsNumeric(シート数) Then  '念のため
          If 捺印数 > 0 And シート数 > 0 Then       'これも念のため
            okSpec = True                'AG/AF列定義、OK
            
            For Each myFile In myFso.GetFolder(oFold).Files
              e = LCase(myFso.getextensionname(myFile.Name))
              If e = "xls" Then
              
                fName = myFile.Name
                'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
                Set fWb = Workbooks.Open(oFold & "\" & fName)
                nosBooks = nosBooks + 1
                
                If シート数 <= fWb.Worksheets.Count Then  '指定シート数が存在する場合のみ
                  i2 = 0             '● ブック内捺印数カウンター
                  For sid = 1 To シート数         '指定シート数だけループ
                    Set fSh = fWb.Sheets(sid)
                    For Each MyOb In fSh.Shapes '
                      If MyOb.Type = msoPicture Then  '
                        If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                        '左上角指定で選択されたシートのみを対象にする
                        'このifが無いとシート全体が対象になる
                          i2 = i2 + 1
                        End If
                      End If
                    Next
                  Next
                End If
                
                fWb.Close False
                If i2 = 捺印数 Then okBooks = okBooks + 1
                i2 = 0
                
              End If
             
            Next
            
          End If
        End If
      End If
      
      'フォルダ内検索終了
      
      cnt = cnt + okBooks
      rmks = "問題あり"
      mycIndex = 3
      
      If Not okSpec Then
        rmks = rmks & "(AF/AG列 不正)"
      ElseIf fdFound Then
        rmks = rmks & "(フォルダなし)"
      ElseIf nosBooks = 0 Then
        rmks = rmks & "(フォルダにブックなし)"
      ElseIf nosBooks <> okBooks Then
        rmks = rmks & "(捺印数不合ブック数:" & nosBooks - okBooks & " 件)"
      Else
        rmks = "問題なし"
        mycIndex = xlAutomatic
      End If
      
      With sh1.Range("J" & i)
        .Value = rmks
        .Font.ColorIndex = mycIndex
      End With
      
    End If
    
  Next

  Application.ScreenUpdating = True

  MsgBox cnt & " 個のファイルの捺印状況がOKでした。", vbInformation

End Sub

【72299】Re:捺印数の照合
発言  UO3  - 12/7/10(火) 12:42 -

引用なし
パスワード
   ▼ピッポ さん:

コードの訂正じゃないんですが、コメントの場所が適切ではないところがありました。

'ラインごとの変数初期化

これは

For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))

これの下に記述すべきでした。

【72329】Re:捺印数の照合
発言  ピッポ  - 12/7/12(木) 18:12 -

引用なし
パスワード
   お世話になります。

様々な指摘に関して感謝申し上げます。

・まず、何度か申し上げている通り、シート指定が1枚であれ複数枚であれ、処理を分ける必要はありません。
> すべてそのブックに対して For インデックス = 1 To シート数 で処理すれば、
> 1 To 1 あるいは 1 To 3 といったようにできますので。


おっしゃる通りです。
言い訳がましい言い方ですが、マクロに関して最近勉強し始めた為、発想力が乏しいもので・・・
様々なものに挑戦して応用力を身につけられるように努力します。

・ただ、なぜ、ピッポさんが、1かそうじゃないかに、こだわるんだろうと、考えてみました。
> もしかして・・・アップ済みのコード(オリジナルのピッポさんのコードロジックから想像したもの)では
> たとえば 捺印数指定が 4 、シート数が 3 。この場合、これら3つのシートがすべて、それぞれ 4つの捺印が必要。
> こんなロジックになっていました。でも、この 3つのシートの捺印数をあわせて、4 ということだったのかなと。
> 以下のコードは、その新しい解釈によるコードになっています。(元戻しはたやすいので、違っていれば指摘願います)

私の表現の仕方が曖昧だったと思います。

「捺印数指定が 4 、シート数が 3 」

シートが3つあり、それぞれのシートで4つづつ捺印してあることを意味しています。
自分なりに数ヶ所変更してみました。

動作確認した結果、問題ありませんでしたが、
UO3 さん的には何か別の方法の方が宜しいでしょうか?
ご意見頂ければ幸いです。


変更箇所を下記のコメントを明記
※訂正箇所になります
※追加箇所になります


Sub image_count()
Dim myFso As Object
  Dim MyOb As Object
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim c As Range
  Dim oFold As String
  Dim fName As String
  Dim i As Long
  Dim myFile As Object
  Dim i2 As Long
  Dim e As String
  Dim 捺印数 As Variant    '★データ型は念のためVariant
  Dim シート数 As Variant   '★データ型は念のためVariant
  Dim y As Long
  Dim fWb As Workbook
  Dim fSh As Worksheet
  Dim cnt As Long
  Dim sid As Long
  Dim fdFound As Boolean
  Dim nosBooks As Long
  Dim nosSheet As Long   ’※追加箇所になります
  Dim okBooks As Long
  Dim okSpec As Boolean
  Dim rmks As String
  Dim mycIndex As Long
 
  Application.ScreenUpdating = False
  Set myFso = CreateObject("Scripting.FileSystemObject")
  Set sh1 = Sheets("一覧表")
  Set sh2 = Sheets("元保管場所(データベース)")


  For Each c In sh1.Range("F11", sh1.Range("F" & sh1.Rows.Count).End(xlUp))
   'ラインごとの変数初期化
    i = c.Row
    捺印数 = sh1.Range("AF" & i).Value
    シート数 = sh1.Range("AG" & i).Value  'データ数もここで
    With sh1.Range("J" & i)
      .Clear   ' このほうがいいね
    End With
  
    fdFound = False
    okSpec = False
    nosBooks = 0
    okBooks = 0
  
    'F列にハイパーリンクなければスキップ
    If c.Hyperlinks.Count > 0 Then
      oFold = c.Offset(, 0).Hyperlinks(1).Address
      'リンク先フォルダが存在するものだけ
      If myFso.FolderExists(oFold) Then
        If IsNumeric(捺印数) And IsNumeric(シート数) Then  '念のため
          If 捺印数 > 0 And シート数 > 0 Then       'これも念のため
            okSpec = True                'AG/AF列定義、OK
      
            For Each myFile In myFso.GetFolder(oFold).Files
              e = LCase(myFso.getextensionname(myFile.Name))
              If e = "xls" Then
       
                fName = myFile.Name
                'ブックオープンし、リンク先フォルダのxlsファイルに図形があるか判定
                Set fWb = Workbooks.Open(oFold & "\" & fName)
                nosBooks = nosBooks + 1
        
                If シート数 <= fWb.Worksheets.Count Then 
                  i2 = 0            
                  For sid = 1 To シート数        
                  nosSheet = nosSheet + 1         ’※追加箇所になります
                    Set fSh = fWb.Sheets(sid)
                    For Each MyOb In fSh.Shapes '
                      If MyOb.Type = msoPicture Then  '
                        If Not Intersect(MyOb.TopLeftCell, fSh.Range("A1:BV9")) Is Nothing Then
                        '左上角指定で選択されたシートのみを対象にする
                        'このifが無いとシート全体が対象になる
                          i2 = i2 + 1
                        End If
                      End If
                    Next
                    If i2 = 捺印数 Then okBooks = okBooks + 1 ’※追加箇所になります
                    i2 = 0                  ’※追加箇所になります  
                  Next
                End If
        
                fWb.Close False
                'If i2 = 捺印数 Then okBooks = okBooks + 1    ’※訂正箇所になります  
                'i2 = 0
              End If                         ’※訂正箇所になります
       
            Next
      
          End If
        End If
      End If
   
      'フォルダ内検索終了
   
      cnt = cnt + okBooks
      rmks = "問題あり"
      mycIndex = 3
   
      If Not okSpec Then
        rmks = rmks & "(AF/AG列 不正)"
      ElseIf fdFound Then
        rmks = rmks & "(フォルダなし)"
      ElseIf nosBooks = 0 Then
        rmks = rmks & "(フォルダにブックなし)"
      'ElseIf nosBooks <> okBooks Then
        'rmks = rmks & "(捺印数不合ブック数:" & nosBooks - okBooks & " 件)"
      ElseIf nosSheet <> okBooks Then
        rmks = rmks & "(捺印数不合ブック数:" & nosSheet - okBooks & " 件)"  ’※追加箇所になります
      Else
        rmks = "問題なし"
        mycIndex = xlAutomatic
      End If
   
      With sh1.Range("J" & i)
        .Value = rmks
        .Font.ColorIndex = mycIndex
      End With
   
    End If
  
  Next

  Application.ScreenUpdating = True

  MsgBox cnt & " 個のファイルの捺印状況がOKでした。", vbInformation


End Sub


・また、ロジックをシンプルにするために、指定フォルダの全ブックの全シートをチェックするようにしました。
> 従来のコードは、フォルダごとに問題あり/なしをセットするわけですから、1枚でもおかしなシートがあれば
> その時点で、ループを抜けるようになっていました。このほうが、かっこいいロジックなんですが、ループ制御の
> コードが煩雑・複雑になり、今後、ピッポさんが改造されるとこを考えて、素直というか愚直なロジックにしました。


ご配慮に感謝致します。
大した改造はできませんが、まずはコードの中身を理解したいと
思います。


>・ちょっと気になりましたので、問題ありの場合、どういう問題だったのかの補
>足も表示しました。
> でも、運用面としては、それじゃ、そのフォルダのどのブックが具体的に問題
>があったのか という質問が利用者から
> でた場合、それはわかりませんね。そういう必要があるなら、ブック明細を別
>表で作成するのがいいかもしれませんね。


運用面の方も想定して作成して頂き、大変助かりました。
確かに、何が問題あるのか明確化した方が問題有りの時の確認作業の合理化
にも繋がり参考になる技法だと思いました。
ブック明細は検討したいと思います。

【72334】Re:捺印数の照合
発言  UO3  - 12/7/13(金) 9:45 -

引用なし
パスワード
   ▼ピッポ さん:

▼ピッポ さん:

おはようございます。

コード拝見。
こちらのテスト確認環境を破棄してしまっていますので稼働確認ができません。
以下はコードを読んだだけのコメントになります。その点、お含みおきください。

まず、いろいろお考えになって、処理のタイミングなど対応しておられるご努力には
敬意を表します。

致命的ではないのですが。

NosSheet は 条件に合ったブックのシートのループの中で

For sid = 1 To シート数
  nosSheet = nosSheet + 1         '※追加箇所になります

こうしておられますね。
つまり、NosSheetの値は、調査したすべてのブックの【シート数】の合計になります。

一方、okBooks も

 If i2 = 捺印数 Then okBooks = okBooks + 1 '※追加箇所になります
 i2 = 0                  '※追加箇所になります
Next

これはシートごとのループの中での処理ですから、名前は okBooks ですけど
実際には ok だったシート数ですね。

で、最後のメッセージ処理で
ElseIf nosSheet <> okBooks Then
  rmks = rmks & "(捺印数不合ブック数:" & nosSheet - okBooks & " 件)"  '※追加箇所になります

正確には、ここは、捺印数不合シート数 でしょうね。

あと、ここも、結果的には問題ありませんが、
i2 を ブックレベルの情報からシートレベルの情報にしたわけですね。
ですので、上にあるように、チェックが終わったら、Next で次のシートに行く前に
i2 = 0 でクリアしておられるわけで、これでいいのですが、もう少しスコープを広げて、
この変数だけをおいかけますと

  i2 = 0  '★ ブックレベルの処理
  For sid = 1 To シート数
    '略
    For Each MyOb In fSh.Shapes '
      '略
     Next
    '略
    i2 = 0 '★シートレベルの処理
  Next

こうなっています。
不具合ではありませんが、

  For sid = 1 To シート数
    i2 = 0 '★シートレベルの処理
    '略
    For Each MyOb In fSh.Shapes '
      '略
     Next
    '略
  Next

このような記述が、本来の書き方なのかなと思います。

【72339】Re:捺印数の照合
お礼  ピッポ  - 12/7/14(土) 21:26 -

引用なし
パスワード
   数日間にわたり、大変明確で迅速な対応をして頂きありがとうございました。

おかげさまで素晴らしいものが完成しそうです。

VBAに関しては、おそらく携わることが多くなると思うので

対応できるように、知識を深めていきたいと思います。

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