- ©«¤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
|
¦^´_ 16# GBKEE
ü§r¡KªO¤j
§Ú·Q¬O§Úªºªí¹F¤è¦¡Åý§A»~·|¦¨¡A§Ú¥unºI¨úªñ1¶gªº¸ê®Æ¦Ó¤w
¨ä¹ê¬O¨ºÓªÑ²¼ªº¸ê®Æ®w¡A¨C¤Ñ³£·|¦³·sªº¸ê®Æ¶i¨Ó¡AµM«á§Ú°õ¦æ- 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_Row As Integer, i As Integer ', Ar() As String, Ex_Name As String
- 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
- 'Ar = Array("¥xªd", "¨Èªd", "¹Åªd", "©¯ºÖ", "«H¤j", "ªFªd")
- For i = 1 To 7
- '** Name ¬OVBA©Ò¥ÎªºÃöÁä¦r¦ê,ÁקK¨Ï¥Î¬°ÅܼƦWºÙ.
- ' If i = 1 Then Ex_Name = "¥xªd"
- ' If i = 2 Then Ex_Name = "¨Èªd"
- ' If i = 3 Then Ex_Name = "¹Åªd"
- ' If i = 4 Then Ex_Name = "Àôªd"
- ' If i = 5 Then Ex_Name = "©¯ºÖ"
- ' If i = 6 Then Ex_Name = "«H¤j"
- ' If i = 7 Then Ex_Name = "ªFªd"
-
- With Sheets(i) '¨Ì¤u§@ªí¯Á¤ÞÈ«ü©w¤u§@ªí
- '****¤u§@ªí¦WºÙ ¦b¬¡¶Ã¯µøµ¡±Æ§Ç¦p¬O¨ÌIF i=1¦p¦¹¶¶§Ç***
- '***¨º´N¤£»Ý³o¨ÇIF i=1 ...........
-
- 'With Sheets(Ex_Name) '¨ÌEx_Name «ü©w¤u§@ªí
- '****¦p¦b¬¡¶Ã¯µøµ¡¤u§@ªí¦WºÙ±Æ§Ç¤£¬O¦p¦¹¶¶§Ç***
- '***¨º´N»Ýn³o¨ÇIF i=1 ...........
-
- 'With Sheets(Ar(i - 1)) '«ü©w©w°}¦C¤¤ªº¤u§@ªí¦WºÙ
- [s][color=Red] .Range("a1:ag65536").Clear '®ø°£¨C¤@¦æ¸ê®Æ[/color][/s]
- Ex_File = Dir(Ex_Path & "A112*ALL_1.csv")
- 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¤é´Á
-
- Set Ex_Wb = Workbooks.Open(Ex_Path & Ex_File) '¶}±Ò A11220070102ALL_1.csv.....
- '************************************************
- Ex_Row = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Row '¨ú±o¸ê®Æ¿é¤Jªº¦C¸¹
- Set Rng = Ex_Wb.Sheets(1).Range("b:b").Find(.Name, lookat:=xlWhole)
- '.Cells(Ex_Row, "A") = Ex_Date '¤é´Á¿é¤J '** °O¿ý©Ò¦³¤é´Á***
- If Not Rng Is Nothing Then
- .Cells(Ex_Row, "A") = Ex_Date '¤é´Á¿é¤J ¦p²¾¨ì³o¸Ì '** ¥u°O¿ý¦³¸ê®Æªº¤é´Á
- .Cells(Ex_Row, "B") = Rng.Offset(, 7)
- .Cells(Ex_Row, "e") = Rng.Offset(, 4)
- .Cells(Ex_Row, "f") = Rng.Offset(, 5)
- .Cells(Ex_Row, "g") = Rng.Offset(, 6)
- .Cells(Ex_Row, "i") = Rng.Offset(, 1)
- End If
- '************************************************
- Ex_Wb.Close False 'Ãö³¬ A11220070102ALL_1.csv.....
- Ex_File = Dir '¤U¤@Ó"A112*ALL_1.csv"
- Loop
- End With
- Next
- Application.ScreenUpdating = True
- MsgBox "OK"
- End Sub
½Æ»s¥N½X ¨C³£³£·|³s¦Pªº¸ê®Æ¤@°_«§ì«¶]¡A®É¶¡Ïû¶O¤£¤Ö
§Ú·Qnªºµ²ªG¬O¤§«e°õ¦æ«áªºÂ¸ê®Æ·|¯dµÛ¡A¨C·í§Ú¦³¤@µ§·s¸ê®Æ¶i¨Ó®É¡A´N¥un¶]·sªº¸ê®Æ·s¼W´N¦n
¦]¬°§Úvb¤£¦n¡A¨Sªk¤l¹³ªO¤j¤@¼Ë¡A¥i¥H¼g¥X¤ñ¹ï¸ê®Æ®w¸Ìªºdata¡A¥u§â·s¶i¨Óªºdata§ì¥X¨Ó¡A
«á¨Óè¦Y±ßÀ\®É¡A¬ðµM·Q¨ì¥t¤@ӸѨM¤è¦¡
§Ú±N·sªºdata©ñ¶i¥t¤@Ó·sªº¸ê®Æ§¨¡AµM«á¤£n®ø°£¨C¤@¦æªº¸ê®Æ¡A§Y°õ¦æ¥H¤Uµ{¦¡½X
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_Row As Integer, i As Integer ', Ar() As String, Ex_Name As String
Ex_Path = "C:\Documents and Settings\gkld\®à±\my kp\¸ê®Æ®w\·s¸ê®Æ§¨\"
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
'Ar = Array("¥xªd", "¨Èªd", "¹Åªd", "©¯ºÖ", "«H¤j", "ªFªd")
For i = 1 To 7
'** Name ¬OVBA©Ò¥ÎªºÃöÁä¦r¦ê,ÁקK¨Ï¥Î¬°ÅܼƦWºÙ.
' If i = 1 Then Ex_Name = "¥xªd"
' If i = 2 Then Ex_Name = "¨Èªd"
' If i = 3 Then Ex_Name = "¹Åªd"
' If i = 4 Then Ex_Name = "Àôªd"
' If i = 5 Then Ex_Name = "©¯ºÖ"
' If i = 6 Then Ex_Name = "«H¤j"
' If i = 7 Then Ex_Name = "ªFªd"
With Sheets(i) '¨Ì¤u§@ªí¯Á¤ÞÈ«ü©w¤u§@ªí
'****¤u§@ªí¦WºÙ ¦b¬¡¶Ã¯µøµ¡±Æ§Ç¦p¬O¨ÌIF i=1¦p¦¹¶¶§Ç***
'***¨º´N¤£»Ý³o¨ÇIF i=1 ...........
'With Sheets(Ex_Name) '¨ÌEx_Name «ü©w¤u§@ªí
'****¦p¦b¬¡¶Ã¯µøµ¡¤u§@ªí¦WºÙ±Æ§Ç¤£¬O¦p¦¹¶¶§Ç***
'***¨º´N»Ýn³o¨ÇIF i=1 ...........
'With Sheets(Ar(i - 1)) '«ü©w©w°}¦C¤¤ªº¤u§@ªí¦WºÙ
.Range("a1:ag65536").Clear '®ø°£¨C¤@¦æ¸ê®Æ
Ex_File = Dir(Ex_Path & "A112*ALL_1.csv")
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¤é´Á
Set Ex_Wb = Workbooks.Open(Ex_Path & Ex_File) '¶}±Ò A11220070102ALL_1.csv.....
'************************************************
Ex_Row = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Row '¨ú±o¸ê®Æ¿é¤Jªº¦C¸¹
Set Rng = Ex_Wb.Sheets(1).Range("b:b").Find(.Name, lookat:=xlWhole)
'.Cells(Ex_Row, "A") = Ex_Date '¤é´Á¿é¤J '** °O¿ý©Ò¦³¤é´Á***
If Not Rng Is Nothing Then
.Cells(Ex_Row, "A") = Ex_Date '¤é´Á¿é¤J ¦p²¾¨ì³o¸Ì '** ¥u°O¿ý¦³¸ê®Æªº¤é´Á
.Cells(Ex_Row, "B") = Rng.Offset(, 7)
.Cells(Ex_Row, "e") = Rng.Offset(, 4)
.Cells(Ex_Row, "f") = Rng.Offset(, 5)
.Cells(Ex_Row, "g") = Rng.Offset(, 6)
.Cells(Ex_Row, "i") = Rng.Offset(, 1)
End If
'************************************************
Ex_Wb.Close False 'Ãö³¬ A11220070102ALL_1.csv.....
Ex_File = Dir '¤U¤@Ó"A112*ALL_1.csv"
Loop
End With
Next
Application.ScreenUpdating = True
MsgBox "OK"
End Sub
¦n¹³¤]¥i¥H¸Ñ¨M
ü¡K¥u¯à¥Î²Â¤èªk¤F |
|