- ©«¤l
- 44
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 51
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2012-11-18
- ³Ì«áµn¿ý
- 2022-8-6
|
¦^´_ 9# gkld
¦A½Ð±Ð¤@Ó°ÝÃD¡A§Ú¨Ò¥ÎªO¤j±Ðªº¤è¦¡¡A¶]¥X²Ä¤@Ó1101¥xªd¤u§@ªí
¡Aµ{¦¡½X¦p¤U¡G- Option Explicit
- Sub Ex()
- Dim Ex_Path As String, Ex_File As String, Ex_Date As String, Ex_Wb As Workbook
- Dim Rng As Range
- Ex_Path = "C:\Documents and Settings\gkld\®à±\my kp\¸ê®Æ®w\¤W¥«\" '******ק復********
- Ex_File = Dir(Ex_Path & "A112*ALL_1.csv")
- If Ex_File = "" Then
- MsgBox "¨S¦³ A112*ALL_1.csv"
- Exit Sub
- End If
- Application.ScreenUpdating = False
- Range("a1:ag65536").Clear '®ø°£¨C¤@¦æ¸ê®Æ
- Do While Ex_File <> ""
- Ex_Date = Replace(Ex_File, "A112", "") '®ø°£ÀɦW¤¤"A112"
- Ex_Date = Replace(Ex_Date, "ALL_1.csv", "") '®ø°£ÀɦW¤¤"ALL_1.csv"
- Ex_Date = DateSerial(Mid(Ex_Date, 1, 4), Mid(Ex_Date, 5, 2), Mid(Ex_Date, 7, 2)) '±a¤J¤é´Á
- With ActiveSheet '§@¥Î¤¤ªº¤u§@ªí
- Set Ex_Wb = Workbooks.Open(Ex_Path & Ex_File) '¶}±Ò A11220070102ALL_1.csv.....
- '************************************************
- .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = Ex_Date '¤é´Á¿é¤J
- If Ex_Wb.Sheets(1).Range("B3") <> "" Then
- .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find("¥xªd", lookat:=xlWhole).Offset(, 7)
- .Cells(.Rows.Count, "e").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find("¥xªd", lookat:=xlWhole).Offset(, 4)
- .Cells(.Rows.Count, "f").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find("¥xªd", lookat:=xlWhole).Offset(, 5)
- .Cells(.Rows.Count, "g").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find("¥xªd", lookat:=xlWhole).Offset(, 6)
- .Cells(.Rows.Count, "i").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find("¥xªd", lookat:=xlWhole).Offset(, 1)
-
- Else
- .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "e").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "f").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "g").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "i").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- '**** קï§@¥Î¤¤ªº¤u§@ªí.Range("B1") ¬°¬d¸ß«ü¼ÆªºÃþ§O *********
- End If
- '************************************************
- Ex_Wb.Close False 'Ãö³¬ A11220070102ALL_1.csv.....
- End With
- Ex_File = Dir '¤U¤@Ó"A112*ALL_1.csv"
- Loop
- Application.ScreenUpdating = True
- MsgBox "OK"
- End Sub
½Æ»s¥N½X «á¨Ó¡A§Ú·Q¦b·s¼W¦n´XÓ¤u§@ªí¡A¦p1102¨Èªd¡F1103¹Åªd¡F¡Kµ¥¤@ª½¨ì1110ªFªd¡A¦@7Ó¤u§@ªí
µ{¦¡½X¦p¤U¡G- Option Explicit
- Sub Ex()
- Dim Ex_Path As String, Ex_File As String, Ex_Date As String, Ex_Wb As Workbook
- Dim Rng As Range
- Ex_Path = "C:\Documents and Settings\gkld\®à±\my kp\¸ê®Æ®w\¤W¥«\" '******ק復********
- Ex_File = Dir(Ex_Path & "A112*ALL_1.csv")
- If Ex_File = "" Then
- MsgBox "¨S¦³ A112*ALL_1.csv"
- Exit Sub
- End If
- Application.ScreenUpdating = False
- Range("a1:ag65536").Clear '®ø°£¨C¤@¦æ¸ê®Æ
- For i = 1 To 7
- If i = 1 Then Name = "¥xªd"
- End If
- If i = 2 Then Name = "¨Èªd"
- End If
- If i = 3 Then Name = "¹Åªd"
- End If
- If i = 4 Then Name = "Àôªd"
- End If
- If i = 5 Then Name = "©¯ºÖ"
- End If
- If i = 6 Then Name = "«H¤j"
- End If
- If i = 7 Then Name = "ªFªd"
- End If
- Do While Ex_File <> ""
- Ex_Date = Replace(Ex_File, "A112", "") '®ø°£ÀɦW¤¤"A112"
- Ex_Date = Replace(Ex_Date, "ALL_1.csv", "") '®ø°£ÀɦW¤¤"ALL_1.csv"
- Ex_Date = DateSerial(Mid(Ex_Date, 1, 4), Mid(Ex_Date, 5, 2), Mid(Ex_Date, 7, 2)) '±a¤J¤é´Á
- With ActiveSheet '§@¥Î¤¤ªº¤u§@ªí
- Set Ex_Wb = Workbooks.Open(Ex_Path & Ex_File) '¶}±Ò A11220070102ALL_1.csv.....
- '************************************************
- .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = Ex_Date '¤é´Á¿é¤J
- If Ex_Wb.Sheets(1).Range("B3") <> "" Then
- Sheet(i).Cells(.Rows.Count, "B").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find(Name, lookat:=xlWhole).Offset(, 7)
- Sheet(i).Cells(.Rows.Count, "e").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find(Name, lookat:=xlWhole).Offset(, 4)
- Sheet(i).Cells(.Rows.Count, "f").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find(Name, lookat:=xlWhole).Offset(, 5)
- Sheet(i).Cells(.Rows.Count, "g").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find(Name, lookat:=xlWhole).Offset(, 6)
- Sheet(i).Cells(.Rows.Count, "i").End(xlUp).Offset(1) = Ex_Wb.Sheets(1).Range("b:b").Find(Name, lookat:=xlWhole).Offset(, 1)
-
- Else
- .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "e").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "f").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "g").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- .Cells(.Rows.Count, "i").End(xlUp).Offset(1) = "---" '**¨S¦³¸ê®Æ
- '**** קï§@¥Î¤¤ªº¤u§@ªí.Range("B1") ¬°¬d¸ß«ü¼ÆªºÃþ§O *********
- End If
- '************************************************
- Ex_Wb.Close False 'Ãö³¬ A11220070102ALL_1.csv.....
- End With
- Ex_File = Dir '¤U¤@Ó"A112*ALL_1.csv"
- Loop
- Application.ScreenUpdating = True
- MsgBox "OK"
- End Sub
½Æ»s¥N½X ¥Î«Ü²Â©åªº¤è¦¡¥h¼g¡A«o¶]¤£¥Xµ²ªG¨Ó¡A¥X²{¿ù»~¡K¡A¥i¥H½ÐªO¤j¼·ªÅÀ°§Ú¬Ý¬Ý¶Ü¡H |
|