★得意先管理.xlsmを開いた状態でマクロを実行したい
得意先管理.xlsmにマクロを入れて実行します。
★csvデータは閉じた状態で
閉じた状態ではデータのチェックは出来ません。開いて閉じます。
データの削除はしません。
Sub test()
Const fpath As String = \u0026quot;C:\\Users\\user\\Downloads\\\u0026quot;
Dim tbl As Variant
Dim fname As String
Dim no As Variant
Dim sname As String
Dim wb As Workbook
Dim otbl() As Variant
Dim i As Long, j As Long
Dim r As Long
Application.ScreenUpdating = False
'数字部分が最大のファイルを検索
fname = Dir(fpath \u0026amp; \u0026quot;*.csv\u0026quot;, vbNormal)
Do Until fname = \u0026quot;\u0026quot;
If left(fname, 3) = \u0026quot;abc\u0026quot; Then
If Mid(fname, 4, Len(fname) - 7) \u0026gt; no Then
no = Mid(fname, 4, Len(fname) - 7)
sname = fname
End If
End If
fname = Dir()
Loop
'そのCSVデータを配列へ
Set wb = Workbooks.Open(fpath \u0026amp; sname)
With wb.Worksheets(1)
tbl = .Range(\u0026quot;M2:M\u0026quot; \u0026amp; .Cells(Rows.Count, \u0026quot;M\u0026quot;).End(xlUp).Row)
End With
'チェックして残すものを配列へ
i = -1
With ThisWorkbook.Worksheets(\u0026quot;銀行データ\u0026quot;)
For r = 3 To .Cells(Rows.Count, \u0026quot;V\u0026quot;).End(xlUp).Row
For j = 1 To UBound(tbl)
If .Cells(r, \u0026quot;V\u0026quot;).Value \u0026lt;\u0026gt; \u0026quot;\u0026quot; And .Cells(r, \u0026quot;V\u0026quot;).Value = tbl(j, 1) Then
i = i + 1
ReDim Preserve otbl(1, i)
otbl(0, i) = .Cells(r, \u0026quot;A\u0026quot;).Value
otbl(1, i) = .Cells(r, \u0026quot;V\u0026quot;).Value
Exit For
End If
Next j
Next r
End With
wb.Close
With Worksheets(\u0026quot;貼付場所\u0026quot;)
For i = 0 To UBound(otbl, 2)
.Cells(i + 2, 12).Value = otbl(0, i)
.Cells(i + 2, 13).Value = otbl(1, i)
Next i
End With
Application.ScreenUpdating = True
End Sub
