[µo°Ý] ÀɦW¤£¦P¦p¦ó§PÂ_

cowww

½Ð¨D¦U¦ì¤j¤jªºÀ°¦£
§Ú¦³­ÓÀɮצ­¤W·|¥h¶}°_¨ä¥LÀÉ®×¼´¸ê®Æ
¦³¤@­Ó¥~³æ¦ìªºÀɮ׬ðµM§ï¤FÀɦW
½Ð°Ý­n¦p¦óÅý¨t²Î§PÂ_§ä¤£¨ì"ÀɦWA"´N¥h§ä"ÀɦWB"

³o¬O­ì¥ýªºÀɦW"¤é´Á+ªÅ®æ+¦W"

¤U¸ü (1.74 KB)

2023-4-25 10:40

¤µ¤Ñ²ö¦W§ï¤FÀɦW"¤é´Á+¦W"

¤U¸ü (1.85 KB)

2023-4-25 10:41

Dim T$, B$, i%
T = "\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\X.¦Û°Ê¤Æ¤u¨ã(¤Å§R)\¼Ò¨ã²§°Ê-¤»ºÖ(«æ¥ó1.9±M¥Î)\"
For i = 0 To 10
   B = Format(Date - i, "yyyy.mm.dd")
   If Dir(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx") <> Empty Then GoTo i01

Next
MsgBox "§ä¤£¨ì " & Date & " ~ " & Date - 10 & " ÀÉ®×": Exit Sub
i01: Workbooks.Open filename:=T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx", ReadOnly:=True

Andy2483

¦^´_ 1# cowww


   
Dim T$, B$, i%
T = "\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\X.¦Û°Ê¤Æ¤u¨ã(¤Å§R)\¼Ò¨ã²§°Ê-¤»ºÖ(«æ¥ó1.9±M¥Î)\"
For i = 0 To 10
   B = Format(Date - i, "yyyy.mm.dd")
   If Dir(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx") <> Empty Then GoTo i01
   If Dir(T & B & "¨C¤é¼Ò¨ã²§°Ê.xlsx") <> Empty Then GoTo i02
Next
MsgBox "§ä¤£¨ì " & Date & " ~ " & Date - 10 & " ÀÉ®×": Exit Sub
i01: Workbooks.Open filename:=T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx", ReadOnly:=True:Exit Sub
i02: Workbooks.Open filename:=T & B & "¨C¤é¼Ò¨ã²§°Ê.xlsx", ReadOnly:=True

cowww

¦^´_ 2# Andy2483

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

§Ú¬ðµM·Q¨ì­Ó°ÝÃD¡A¦pªG¶}±ÒªºÀɦW¸ò"½Æ»s"&"Ãö³¬"ªºÀɦW¤£¤@¼Ë
°õ¦æ®É¬O¤£¬O·|¥X²{²§±`
'½Æ»s2023.04.13 ¨C¤é¼Ò¨ã²§°Ê.xlsx¤¤LF¤u§@ªíªºAÄæ¨ìAGÄæ
Workbooks(B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx").Worksheets("LF").Columns("A:AG").Cells.Copy

'Ãö³¬¤»´T²§°Êªí
Workbooks(B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx").Close

Andy2483

¦^´_ 3# cowww


   
On Error Resume Next
Workbooks(B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx").Close
Workbooks(B & "¨C¤é¼Ò¨ã²§°Ê.xlsx").Close
On Error Goto 0

===========================
¸É¥R:
«Øij«e½ú¥ý¥i¥H¥Î¤§«á¦A¬ã¨s¦p¦ó²¤Æ,Åý§ó©öµøŪ

cowww

¦^´_ 4# Andy2483


¦¨¥\¤F
«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

­ã´£³¡ªL

Dim T$, B$, i%, xBook as workbook
T = "\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\X.¦Û°Ê¤Æ¤u¨ã(¤Å§R)\¼Ò¨ã²§°Ê-¤»ºÖ(«æ¥ó1.9±M¥Î)\"
For i = 0 To 10
   B = Format(Date - i, "yyyy.mm.dd")
   If Dir(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx") <> "" Then
      set xBook=Workbooks.Open(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx", ReadOnly:=True)
      exit for
Next
if xbook is nothing then MsgBox "§ä¤£¨ì¥Ø¼ÐÀÉ®×": Exit Sub
xbook.sheets("LF").Columns("A:AG").Cells.Copy
~~do something ~~
xbook.Close 0

Andy2483

¦^´_ 6# ­ã´£³¡ªL
¦^´_ 5# cowww


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü±Ð



Option Explicit
Sub TEST()
Dim T$, B$, i%, xBook As Workbook
'¡ô«Å§iÅܼÆ:(T,B)¬O¦r¦êÅܼÆ,i¬Oµu¾ã¼Æ,xBook¬O¬¡­¶Ã¯ÅܼÆ
T = "\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\X.¦Û°Ê¤Æ¤u¨ã(¤Å§R)\¼Ò¨ã²§°Ê-¤»ºÖ(«æ¥ó1.9±M¥Î)\"
'¡ô¥OT³o¦r¦êÅܼƬO ¸ô®|¦r¦ê
For i = 0 To 10
'¡ô³]¶¶°j°é!i±q0¨ì 10
   B = Format(Date - i, "yyyy.mm.dd")
   '¡ô¥OB³o¦r¦êÅܼƬO ¤µ¤Ñ¤é´Á-iÅܼƫá,
   'Âà´«¬°4½X¦è¤¸¦~,2½X¤ë,2½X¤é,¤¤¶¡ÂI¶¡¹jªº¦r¦ê
   If Dir(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx") <> "" Then
   '¡ô¦pªGTÅܼƳs±µBÅܼÆ,¦A³s±µ" ¨C¤é¼Ò¨ã²§°Ê.xlsx"²Õ¦¨·s¦r¦ê¬d¨ì³oÀÉ®×?
      Set xBook = Workbooks.Open(T & B & " ¨C¤é¼Ò¨ã²§°Ê.xlsx", ReadOnly:=True)
      '¡ô¥OxBook³o¬¡­¶Ã¯ÅܼƬO³Q¥´¶}ªº,
      '³Q¥´¶}ªºTÅܼƳs±µBÅܼÆ,¦A³s±µ" ¨C¤é¼Ò¨ã²§°Ê.xlsx"²Õ¦¨·s¦r¦êªºÀÉ®×
      Exit For
      '¡ôµ²§ô°j°é
   End If
Next
If xBook Is Nothing Then MsgBox "§ä¤£¨ì¥Ø¼ÐÀÉ®×": Exit Sub
'¡ô¦pªGxBookÅܼƬO¨S¦³¸ü¤Jª«¥ó!´N¸õ¥X´£¥Üµ¡,µ²§ôµ{¦¡°õ¦æ
xBook.Sheets("LF").Columns("A:AG").Cells.Copy
'¡ôÄ~Äò¥HÅܼÆxBook«ü¦V¥Ø¼Ð¥i¥HÅýµ{¦¡Â²¼ä©öµøŪ
'~~do something ~~
xBook.Close 0
'¡ô¥OxBook¤£¦sÀÉ Ãö³¬
End Sub

cowww

¦^´_ 6# ­ã´£³¡ªL

«D±`·PÁ­㴣³¡ªL¤j¤jªº¸Ñ´b
¦ý¬O
§ä¤£¨ìÀÉ®×­C

¤U¸ü (3.88 KB)

2023-4-26 08:57

¤U¸ü (1.9 KB)

2023-4-26 08:57

Andy2483

¦^´_ 8# cowww


    ¤µ¤Ñ­n§ìªºÀɦW  " ¨C¤é¼Ò¨ã²§°Ê.xlsx" «e¤è¦³ªÅ¥Õ¦r¤¸¶Ü?

cowww

¦^´_ 9# Andy2483

¨S¦³ªÅ¥Õ¦r¤¸

¤U¸ü (1.9 KB)

2023-4-26 09:45