ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ starbox520 ©ó 2017-1-4 10:28 ½s¿è



¦¹µ{¦¡¥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. clean_rawdata
  3.    Sheets("Act_UTZ").Select
  4.     Range("D4").Value = "¤â°Ê"

  5. Dim Src As String
  6.    
  7.    
  8.     Application.DisplayAlerts = False
  9.    
  10.    
  11.     Src = ThisWorkbook.Name
  12.    
  13.     start_year = Left(Worksheets("Main").Cells(2, 2), 4)
  14.     end_year = Left(Worksheets("Main").Cells(2, 4), 4)

  15.     start_month = Mid(Worksheets("Main").Cells(2, 2), 5, 2)
  16.     end_month = Mid(Worksheets("Main").Cells(2, 4), 5, 2)
  17.    
  18.     start_day = Right(Worksheets("Main").Cells(2, 2), 2)
  19.     end_day = Right(Worksheets("Main").Cells(2, 4), 2)
  20.    
  21.     start_date = Right(Worksheets("Main").Cells(2, 2), 4)
  22.     end_date = Right(Worksheets("Main").Cells(2, 4), 4)
  23.    
  24.    
  25.     ''''plan_period¬°©Ò­n¤U¸ü¤§PC Planª©¥»¤é´Á
  26.    
  27.    
  28.     If Val(end_month) > Val(start_month) Or Val(end_year) > Val(start_year) Then
  29.       
  30.        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
  31.           m1 = 31
  32.        End If
  33.        If start_month = "02" Then
  34.           m1 = 29
  35.        End If
  36.        If start_month = "04" Or start_month = "06" Or start_month = "09" Or start_month = "11" Then
  37.           m1 = 30
  38.        End If
  39.         
  40.        plan_period = m1 - Val(start_day) + Val(end_day)
  41.       
  42.     End If
  43.       
  44.     If Val(end_month) = Val(start_month) Then
  45.    
  46.        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
  47.           m1 = 31
  48.        End If
  49.        If start_month = "02" Then
  50.           m1 = 29
  51.        End If
  52.        If start_month = "04" Or start_month = "06" Or start_month = "09" Or start_month = "11" Then
  53.           m1 = 30
  54.        End If
  55.    
  56.        plan_period = Val(end_day) - Val(start_day)
  57.       
  58.     End If
  59.    
  60.             
  61.    ' Worksheets("Raw_Data_1").Select
  62.    
  63.     'Cells.Select
  64.    ' Selection.EntireColumn.Hidden = False
  65.    ' Worksheets("Raw_Data_1").AutoFilterMode = False
  66.    

  67.    
  68.     For I = 0 To plan_period
  69.    
  70.         If Val(start_day) + I > m1 Then
  71.         
  72.            If Val(start_month) <= 8 Then
  73.       
  74.               month_var = "0" & Val(start_month) + 1
  75.               
  76.            ElseIf Val(start_month) >= 12 Then
  77.       
  78.                   month_var = "0" & Val(start_month) + 1 - 12
  79.               
  80.            ElseIf Val(start_month) >= 9 Then
  81.       
  82.                   month_var = Val(start_month) + 1
  83.               
  84.            End If
  85.       
  86.            If Val(start_day) + I - m1 <= 9 Then
  87.          
  88.               day_var = "0" & Val(start_day) + I - m1
  89.               
  90.            ElseIf Val(start_day) + I - m1 >= 10 Then
  91.       
  92.                   day_var = Val(start_day) + I - m1
  93.          
  94.            End If
  95.    
  96.            plan_date = end_year & month_var & day_var
  97.            
  98.         End If
  99.         
  100. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  101.    
  102.         If Val(start_day) + I <= m1 Then
  103.                
  104.            If Val(start_day) + I <= 9 Then
  105.    
  106.               day_var = "0" & Val(start_day) + I
  107.               
  108.            ElseIf Val(start_day) + I >= 10 Then
  109.       
  110.                  day_var = Val(start_day) + I
  111.               
  112.            End If
  113.            
  114.            plan_date = start_year & start_month & day_var
  115.            
  116.         End If
  117.             
  118.       
  119.         '¦b""¥i¿é¤Jºô§}
  120.         Importfilepath = ""
  121.    
  122.         Set oldbook = Workbooks.Open(Importfilepath)
  123.    
  124.         'Application.AutomationSecurity = secAutomation
  125.         
  126.         
  127.         
  128. '-------------------------------------------------------------------------------------------------------------------

  129. Columns("A:X").Select
  130.     Selection.Copy
  131.     Windows(Src).Activate
  132.     Sheets("output").Select

  133.     Range("A1").Select
  134.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  135.         :=False, Transpose:=False

  136.     Range("X2").Select
  137.     Application.CutCopyMode = False
  138.     ActiveCell.FormulaR1C1 = "=RC[-15]&RC[-20]"
  139.     Range("X2").Select
  140.     With Selection.Interior
  141.         .Pattern = xlSolid
  142.         .PatternColorIndex = xlAutomatic
  143.         .ThemeColor = xlThemeColorLight2
  144.         .TintAndShade = 0.799981688894314
  145.         .PatternTintAndShade = 0
  146.     End With
  147.     Range("X1").Select
  148.     ActiveCell.FormulaR1C1 = "$"


  149. '¤U©Ô

  150.     Dim r1 As Range

  151.     Range("A1").Select
  152.     Selection.End(xlDown).Select
  153.    
  154.     rf = ActiveCell.Offset(0, 23).Address
  155.     rtxt = "x2:" & rf
  156.     Set r1 = Range(rtxt)
  157.     r1.Select
  158.     Selection.FillDown
  159.    
  160.     '­È¶K¤W
  161.      Selection.Copy
  162.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  163.         :=False, Transpose:=False



  164. ¤½¦¡_UTZ
  165. ¶K¤W
  166. '¨ê¤l
  167.                
  168.    
  169.         Windows("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Activate
  170.         Workbooks("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Close False
  171.         
  172.         
  173.         
  174.         Next
  175.         
  176.         MsgBox ("¤â°Ê©Ô¤é´Á")
  177.         
  178.                
  179. End Sub
½Æ»s¥N½X
¥i§ì¨úºô¬q¤W¸ê®Æ.rar (227.96 KB)

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2017-1-5 13:46 ½s¿è

¦^´_ 1# starbox520
¤é´Á³B²z½Ð°Ñ¦Ò
DateSerial
DateDiff
DateAdd
³o´X­Ó¨ç¼Æ¡A¤£¥Î¦Û¤v³B²z±o¨º»ò³Â·Ð
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 2# stillfish00

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

TOP

¦^´_ 3# starbox520


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


  2. clean_rawdata
  3.    Sheets("Act_UTZ").Select
  4.     Range("D4").Value = "¤â°Ê"

  5. Dim Src As String
  6.    
  7.    
  8.     Application.DisplayAlerts = False
  9.    
  10.    
  11.     Src = ThisWorkbook.Name
  12.    
  13.      Dim start_d As Date, end_d As Date
  14.     start_d = DateValue(Mid(Sheets("Main").[B2], 1, 4) & "/" & Mid(Sheets("Main").[B2], 5, 2) & "/" & Mid(Sheets("Main").[B2], 7, 2))
  15.     end_d = DateValue(Mid(Sheets("Main").[D2], 1, 4) & "/" & Mid(Sheets("Main").[D2], 5, 2) & "/" & Mid(Sheets("Main").[D2], 7, 2))

  16.    
  17.     For i = start_d To end_d
  18.    
  19.         
  20.            
  21.            
  22.         plan_date = Format(i, "yyyymmdd")
  23.             
  24.       
  25.         '¦b""¥i¿é¤Jºô§}
  26.         Importfilepath = " "
  27.    
  28.         Set oldbook = Workbooks.Open(Importfilepath)
  29.    
  30.         'Application.AutomationSecurity = secAutomation
  31.         
  32.         
  33. '-------------------------------------------------------------------------------------------------------------------

  34. Columns("A:X").Select
  35.     Selection.Copy
  36.     Windows(Src).Activate
  37.     Sheets("output").Select

  38.     Range("A1").Select
  39.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  40.         :=False, Transpose:=False

  41.     Range("X2").Select
  42.     Application.CutCopyMode = False
  43.     ActiveCell.FormulaR1C1 = "=RC[-15]&RC[-20]"
  44.     Range("X2").Select
  45.     With Selection.Interior
  46.         .Pattern = xlSolid
  47.         .PatternColorIndex = xlAutomatic
  48.         .ThemeColor = xlThemeColorLight2
  49.         .TintAndShade = 0.799981688894314
  50.         .PatternTintAndShade = 0
  51.     End With
  52.     Range("X1").Select
  53.     ActiveCell.FormulaR1C1 = "$$"


  54. '¤U©Ô

  55.     Dim r1 As Range

  56.     Range("A1").Select
  57.     Selection.End(xlDown).Select
  58.    
  59.     rf = ActiveCell.Offset(0, 23).Address
  60.     rtxt = "x2:" & rf
  61.     Set r1 = Range(rtxt)
  62.     r1.Select
  63.     Selection.FillDown
  64.    
  65.     '­È¶K¤W
  66.      Selection.Copy
  67.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  68.         :=False, Transpose:=False



  69. ¤½¦¡_UTZ
  70. ¶K¤W
  71. '¨ê¤l
  72.                
  73.    
  74.         Windows("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Activate
  75.         Workbooks("Tester_COEE&UTZ_" & plan_date & "_DailyRawData.xls").Close False
  76.         
  77.         
  78.         
  79.         Next
  80.         
  81.         MsgBox ("¤â°Ê©Ô¤é´Á")
  82.         
  83.                
  84. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2017-1-5 19:17 ½s¿è

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 5# stillfish00


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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2017-1-6 15:49 ½s¿è

¦^´_ 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.         ar = oldbook.ActiveSheet.Columns("A:X")
  5.         ThisWorkbook.Sheets("output").Range("A1").Resize(UBound(ar), UBound(ar, 2)) = ar
½Æ»s¥N½X
¬Æ¦Ü¤£­nª½±µ¥Î¾ã­Ó"A:X" column , ¦Ó¬O§ä¥X¹ê»Ú¦³¸ê®Æªº°Ï°ì
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 7# stillfish00

ok

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

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD