揀選名稱中有【均值】的檔案之語法。 - Excel程式區

[發問] 揀選名稱中有【均值】的檔案之語法。

帖子: 2798
主題: 10
精華: 0
積分: 2854
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-4-20

1^# 跳轉到 »

發表於 2019-6-2 09:52 | 顯示全部帖子

Sub TEST()
Dim P$, F$, A$
P = ThisWorkbook.Path
Application.ScreenUpdating = False
Do
  If F = "" Then F = Dir(P & "\*.csv") Else F = Dir
  If F = "" Then Exit Do
  A = Replace(Replace(F, "基準日：", ""), ".csv", "")
  With Workbooks.Open(P & "\" & F)
   If InStr(A, "均值") Then
      [B1:BK1].Clear
         For j = 1 To 49: Cells(1, j + 1) = j: Next
   End If
   If InStr(A, "總表") Then [A:A].NumberFormatLocal = "yyyy/mm/dd"  '總表-日期-固定長度較好檢視
   [B1:AX1].NumberFormatLocal = "00"
   With [B1:AX1].Font: .Bold = True: .Size = 14: .ColorIndex = 5: End With
   With [A:AX]
         .Font.Name = "Arial"
         .HorizontalAlignment = xlCenter
         .EntireColumn.AutoFit
   End With
   [B2].Select
   With ActiveWindow: .FreezePanes = True: .Zoom = 75: End With
   .SaveAs Filename:=P & "\" & A & ".xls", FileFormat:=xlNormal, CreateBackup:=False
   .Close 0
  End With
  Kill P & "\" & F
Loop
End Sub

================

TOP

准提部林