[µo°Ý] ·Q¦b­ì¦³ªºµ{¦¡½X¥[¤J§ä¬Q¤éªº¥\¯à

starbox520

¤U¸ü (9.92 KB)

2017-1-4 10:19

¦¹µ{¦¡¥i¦b³o¸Ì¿é¤J¤é´Á(¬õ½u°é°_³¡¤À)
·|§äºô¬q¤W¸ê®Æ¡A¨Ì§Ú¿é¤Jªº¤é´Á¤§¶¡ªº©Ò¦³ªº¸ê®Æ
(¤¤¶¡¦³¤@¨Ç¤½¦¡°µ­pºâ)
¹³¤W¹Ï´N¬O·|¥h§ä5¤ë1¸¹-5¤ë31¸¹ªº¸ê®Æ

²{¦b·Q·s¼W¤@­Óbotten¤£»Ý¿é¤J¤é´Á
«ö¤U·|ª½±µ¶]¬Q¤éªº¸ê®Æ¡AÁٽЫü¾É¤@¤U¡AÁÙ¦³³o°¦µ{¦¡¦³®É­Ô·|¶]¨ì1¦~ªº¸ê®Æ
¬Û¹ïªº´N·|¶]¶W¤[¡K³Ì¤[¤@¦¸¶]¤F§Ö2¤Ñ= ="¡Aµ{¦¡½X³¡¤À¦³¿ìªkÀu¤Æ¶Ü

1. Sub Sumbit()
   
2. 
   
3. clean_rawdata
   
4.    Sheets("Act_UTZ").Select
   
5.     Range("D4").Value = "¤â°Ê"
   
6. 
   
7. Dim Src As String
   
8.    
   
9.    
   
10.     Application.DisplayAlerts = False
    
11.    
    
12.    
    
13.     Src = ThisWorkbook.Name
    
14.    
    
15.     start_year = Left(Worksheets("Main").Cells(2, 2), 4)
    
16.     end_year = Left(Worksheets("Main").Cells(2, 4), 4)
    
17. 
    
18.     start_month = Mid(Worksheets("Main").Cells(2, 2), 5, 2)
    
19.     end_month = Mid(Worksheets("Main").Cells(2, 4), 5, 2)
    
20.    
    
21.     start_day = Right(Worksheets("Main").Cells(2, 2), 2)
    
22.     end_day = Right(Worksheets("Main").Cells(2, 4), 2)
    
23.    
    
24.     start_date = Right(Worksheets("Main").Cells(2, 2), 4)
    
25.     end_date = Right(Worksheets("Main").Cells(2, 4), 4)
    
26.    
    
27.    
    
28.     ''''plan_period¬°©Ò­n¤U¸ü¤§PC Planª©¥»¤é´Á
    
29.    
    
30.    
    
31.     If Val(end_month) > Val(start_month) Or Val(end_year) > Val(start_year) Then
    
32.       
    
33.        If start_month = "01" Or start_month = "03" Or start_month = "05" Or start_month = "07" Or start_month = "08" Or start_month = "10" Or start_month = "12" Then
    
34.           m1 = 31
    
35.        End If
    
36.        If start_month = "02" Then
    
37.           m1 = 29
    
38.        End If
    
39.        If start_month = "04" Or start_month = "06" Or start_month = "09" Or start_month = "11" Then
    
40.           m1 = 30
    
41.        End If
    
42.         
    
43.        plan_period = m1 - Val(start_day) + Val(end_day)
    
44.       
    
45.     End If
    
46.       
    
47.     If Val(end_month) = Val(start_month) Then
    
48.    
    
49.        If start_month = "01" Or start_month = "03" Or start_month = "05" Or start_month = "07" Or start_month = "08" Or start_month = "10" Or start_month = "12" Then
    
50.           m1 = 31
    
51.        End If
    
52.        If start_month = "02" Then
    
53.           m1 = 29
    
54.        End If
    
55.        If start_month = "04" Or start_month = "06" Or start_month = "09" Or start_month = "11" Then
    
56.           m1 = 30
    
57.        End If
    
58.    
    
59.        plan_period = Val(end_day) - Val(start_day)
    
60.       
    
61.     End If
    
62.    
    
63.             
    
64.    ' Worksheets("Raw_Data_1").Select
    
65.    
    
66.     'Cells.Select
    
67.    ' Selection.EntireColumn.Hidden = False
    
68.    ' Worksheets("Raw_Data_1").AutoFilterMode = False
    
69.    
    
70. 
    
71.    
    
72.     For I = 0 To plan_period
    
73.    
    
74.         If Val(start_day) + I > m1 Then
    
75.         
    
76.            If Val(start_month) <= 8 Then
    
77.       
    
78.               month_var = "0" & Val(start_month) + 1
    
79.               
    
80.            ElseIf Val(start_month) >= 12 Then
    
81.       
    
82.                   month_var = "0" & Val(start_month) + 1 - 12
    
83.               
    
84.            ElseIf Val(start_month) >= 9 Then
    
85.       
    
86.                   month_var = Val(start_month) + 1
    
87.               
    
88.            End If
    
89.       
    
90.            If Val(start_day) + I - m1 <= 9 Then
    
91.          
    
92.               day_var = "0" & Val(start_day) + I - m1
    
93.               
    
94.            ElseIf Val(start_day) + I - m1 >= 10 Then
    
95.       
    
96.                   day_var = Val(start_day) + I - m1
    
97.          
    
98.            End If
    
99.    
    
100.            plan_date = end_year & month_var & day_var
     
101.            
     
102.         End If
     
103.         
     
104. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
105.    
     
106.         If Val(start_day) + I <= m1 Then
     
107.                
     
108.            If Val(start_day) + I <= 9 Then
     
109.    
     
110.               day_var = "0" & Val(start_day) + I
     
111.               
     
112.            ElseIf Val(start_day) + I >= 10 Then
     
113.       
     
114.                  day_var = Val(start_day) + I
     
115.               
     
116.            End If
     
117.            
     
118.            plan_date = start_year & start_month & day_var
     
119.            
     
120.         End If
     
121.             
     
122.       
     
123.         '¦b""¥i¿é¤Jºô§}
     
124.         Importfilepath = ""
     
125.    
     
126.         Set oldbook = Workbooks.Open(Importfilepath)
     
127.    
     
128.         'Application.AutomationSecurity = secAutomation
     
129.         
     
130.         
     
131.         
     
132. '-------------------------------------------------------------------------------------------------------------------
     
133. 
     
134. Columns("A:X").Select
     
135.     Selection.Copy
     
136.     Windows(Src).Activate
     
137.     Sheets("output").Select
     
138. 
     
139.     Range("A1").Select
     
140.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     
141.         :=False, Transpose:=False
     
142. 
     
143.     Range("X2").Select
     
144.     Application.CutCopyMode = False
     
145.     ActiveCell.FormulaR1C1 = "=RC[-15]&RC[-20]"
     
146.     Range("X2").Select
     
147.     With Selection.Interior
     
148.         .Pattern = xlSolid
     
149.         .PatternColorIndex = xlAutomatic
     
150.         .ThemeColor = xlThemeColorLight2
     
151.         .TintAndShade = 0.799981688894314
     
152.         .PatternTintAndShade = 0
     
153.     End With
     
154.     Range("X1").Select
     
155.     ActiveCell.FormulaR1C1 = "$"
     
156. 
     
157. 
     
158. '¤U©Ô
     
159. 
     
160.     Dim r1 As Range
     
161. 
     
162.     Range("A1").Select
     
163.     Selection.End(xlDown).Select
     
164.    
     
165.     rf = ActiveCell.Offset(0, 23).Address
     
166.     rtxt = "x2:" & rf
     
167.     Set r1 = Range(rtxt)
     
168.     r1.Select
     
169.     Selection.FillDown
     
170.    
     
171.     '­È¶K¤W
     
172.      Selection.Copy
     
173.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     
174.         :=False, Transpose:=False
     
175. 
     
176. 
     
177. 
     
178. ¤½¦¡_UTZ
     
179. ¶K¤W
     
180. '¨ê¤l
     
181.                
     
182.    
     
183.         Windows("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Activate
     
184.         Workbooks("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Close False
     
185.         
     
186.         
     
187.         
     
188.         Next
     
189.         
     
190.         MsgBox ("¤â°Ê©Ô¤é´Á")
     
191.         
     
192.                
     
193. End Sub

½Æ»s¥N½X

¥i§ì¨úºô¬q¤W¸ê®Æ.rar (227.96 KB)

¤U¸ü¦¸¼Æ: 6

2017-1-4 10:27

stillfish00

¦^´_ 1# starbox520
¤é´Á³B²z½Ð°Ñ¦Ò
DateSerial
DateDiff
DateAdd
³o´X­Ó¨ç¼Æ¡A¤£¥Î¦Û¤v³B²z±o¨º»ò³Â·Ð

starbox520

¦^´_ 2# stillfish00

¦^¤j¤j
¥i¥H¥Î§Úªºµ{¦¡½XÁ|­Ó¨Ò¤l¶Ü

starbox520

¦^´_ 3# starbox520


¹³³o¼Ë¶Ü?
¦ý§Ú¤£ª¾¹D³o¼Ë­×¹L«á·|¤£·|¦s¦b¤@¨Ç§ÚÁÙ¨Sµo²{ªºBug

1. Sub Sumbit()
   
2. 
   
3. 
   
4. clean_rawdata
   
5.    Sheets("Act_UTZ").Select
   
6.     Range("D4").Value = "¤â°Ê"
   
7. 
   
8. Dim Src As String
   
9.    
   
10.    
    
11.     Application.DisplayAlerts = False
    
12.    
    
13.    
    
14.     Src = ThisWorkbook.Name
    
15.    
    
16.      Dim start_d As Date, end_d As Date
    
17.     start_d = DateValue(Mid(Sheets("Main").[B2], 1, 4) & "/" & Mid(Sheets("Main").[B2], 5, 2) & "/" & Mid(Sheets("Main").[B2], 7, 2))
    
18.     end_d = DateValue(Mid(Sheets("Main").[D2], 1, 4) & "/" & Mid(Sheets("Main").[D2], 5, 2) & "/" & Mid(Sheets("Main").[D2], 7, 2))
    
19. 
    
20.    
    
21.     For i = start_d To end_d
    
22.    
    
23.         
    
24.            
    
25.            
    
26.         plan_date = Format(i, "yyyymmdd")
    
27.             
    
28.       
    
29.         '¦b""¥i¿é¤Jºô§}
    
30.         Importfilepath = " "
    
31.    
    
32.         Set oldbook = Workbooks.Open(Importfilepath)
    
33.    
    
34.         'Application.AutomationSecurity = secAutomation
    
35.         
    
36.         
    
37. '-------------------------------------------------------------------------------------------------------------------
    
38. 
    
39. Columns("A:X").Select
    
40.     Selection.Copy
    
41.     Windows(Src).Activate
    
42.     Sheets("output").Select
    
43. 
    
44.     Range("A1").Select
    
45.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
46.         :=False, Transpose:=False
    
47. 
    
48.     Range("X2").Select
    
49.     Application.CutCopyMode = False
    
50.     ActiveCell.FormulaR1C1 = "=RC[-15]&RC[-20]"
    
51.     Range("X2").Select
    
52.     With Selection.Interior
    
53.         .Pattern = xlSolid
    
54.         .PatternColorIndex = xlAutomatic
    
55.         .ThemeColor = xlThemeColorLight2
    
56.         .TintAndShade = 0.799981688894314
    
57.         .PatternTintAndShade = 0
    
58.     End With
    
59.     Range("X1").Select
    
60.     ActiveCell.FormulaR1C1 = "$$"
    
61. 
    
62. 
    
63. '¤U©Ô
    
64. 
    
65.     Dim r1 As Range
    
66. 
    
67.     Range("A1").Select
    
68.     Selection.End(xlDown).Select
    
69.    
    
70.     rf = ActiveCell.Offset(0, 23).Address
    
71.     rtxt = "x2:" & rf
    
72.     Set r1 = Range(rtxt)
    
73.     r1.Select
    
74.     Selection.FillDown
    
75.    
    
76.     '­È¶K¤W
    
77.      Selection.Copy
    
78.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
79.         :=False, Transpose:=False
    
80. 
    
81. 
    
82. 
    
83. ¤½¦¡_UTZ
    
84. ¶K¤W
    
85. '¨ê¤l
    
86.                
    
87.    
    
88.         Windows("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Activate
    
89.         Workbooks("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Close False
    
90.         
    
91.         
    
92.         
    
93.         Next
    
94.         
    
95.         MsgBox ("¤â°Ê©Ô¤é´Á")
    
96.         
    
97.                
    
98. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 4# starbox520
¬Oªº¡A­Ë¤£¦p»¡¤£³o¼Ë­×ªº¸Ü¸ó¦~«×¬d¸ß¥i¯à¦³bug
¦ý§ï³o­Ó¤£·|´£¤É¤Ó¦h³t«×

³t«×ªº¸Ü¡A§A¥Î¤F«Ü¦hªº select , copy ¤~¬O¥D¦]
¤@¯ë¸Ñ¨M¤èªk¦³¨âºØ
¤@ºØ¬O¼È®ÉÃö³¬Application.ScreenUpdating¡A¶]§¹¦A¥´¶}
¥t¤@ºØ¬O¤£­n¥Îcopy , §ï¥Î¦p
ar = Range("A1:C3").value    '¨ú¥N copy
Range("F1").resize(ubound(ar),ubound(ar,2)).value = ar     '¨ú¥N paste
¨Ó½Æ»s¸ê®Æ¨ì§Oªº¦a¤è

¥t¥~¹³
r1.Select
Selection.FillDown
³oºØ¥i¥Hª½±µÂ²¤Æ¦¨  r1.FillDown
¤Ï¥¿¦³ Select ªº¦a¤è¯à§K«h§K(°õ¦æ°Ê§@ªºÅ޿褣ÅܤU)¡A·|§Ö«Ü¦h

starbox520

¦^´_ 5# stillfish00


³o³¡¤À§ÚÁÙ¨S¥Îªº«Ü¼ô
¤j¤j¥i¥H§ï¤@³¡¤À·í°Ñ¦Ò¶Ü

stillfish00

¦^´_ 6# starbox520
«Øij§A¬Ý³o½g¡@VBAªº¼g§@§Þ¥©»P¼W¶i®Ä¯à
¨Ò¦p

1.            
   
2.         '¦b""¥i¿é¤Jºô§}
   
3.         Importfilepath = ""
   
4.         Set oldbook = Workbooks.Open(Importfilepath)
   
5.         
   
6.         Columns("A:X").Select
   
7.         Selection.Copy
   
8.         Windows(Src).Activate
   
9.         Sheets("output").Select
   
10.         
    
11.         Range("A1").Select
    
12.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
13.             :=False, Transpose:=False

½Æ»s¥N½X

¥i¥H§ï¦¨

1.         '¦b""¥i¿é¤Jºô§}
   
2.         Importfilepath = ""
   
3.         Set oldbook = Workbooks.Open(Importfilepath)
   
4. 
   
5.         ar = oldbook.ActiveSheet.Columns("A:X")
   
6.         ThisWorkbook.Sheets("output").Range("A1").Resize(UBound(ar), UBound(ar, 2)) = ar

½Æ»s¥N½X

¬Æ¦Ü¤£­nª½±µ¥Î¾ã­Ó"A:X" column , ¦Ó¬O§ä¥X¹ê»Ú¦³¸ê®Æªº°Ï°ì

starbox520

¦^´_ 7# stillfish00

ok

§Ú¸ÕµÛ§ï§ï¬Ý ÁÂÁÂ!!