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

[µo°Ý] ¨Ì¾ÚInputBox¶ñ¤Jªº¤é´ÁÅã¥Ü¸ê®Æ

[µo°Ý] ¨Ì¾ÚInputBox¶ñ¤Jªº¤é´ÁÅã¥Ü¸ê®Æ

´ú¸ÕÀÉ¡J ¨Ì¾ÚInputBox¶ñ¤Jªº¤é´ÁÅã¥Ü¸ê®Æ.rar (23.32 KB)

½Ð°Ý¡J¥H¤Uµ{¦¡ªº»Ý¨D»yªk(2003ª©)À³¸Ó¦p¦ó½s¼g¡H
ÁÂÁ¡I

»Ý¨D¡J
1_¥ý³]¥ß1­Ó¥i¿é¤J¤é´Á(yyyymmdd)ªºInputBox

2_¦bInputBox¿é¤JX¤é´Á«á¡A§Y¥i±N¸Ó¤é´Á¶ñ¤J"¤C¬F","¤K¨ö","¤­¦æ","¤»¨R","¦X¼Æ","¥Í¨v","§¡­È","§À¼Æ"ªº¦U¤u§@ªí(¥H¤U²ºÙ"Arr¡¨)ªºA1¡A
¨Ã±NDATAªºAÄæ=X¤é´Áªº¤U1¦C¤§B¡JH¼Æ­È¶ñ¤JArr ¡A¦pªG¸ÓB¡JH="¡¨®É¡A«hArrªºA1¤]="¡¨¡C

3_±N§¹¦¨²Ä2¶µªº"Arr"¿é¥X¬°1­Ó¿W¥ßÀɮסAÀɮצWºÙ="¤j¼Ö³z_¿òº|ªÅÁ`²Î­pªí_¿é¤JInputBoxªº¤é´Á¡¨¡C
½Ð¸Ô¨£InputBoxªº¤é´Á=202010©M20200717©M20200721ªº¤T­Ó®ÄªGÀɽd¨Ò¡C

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-7-26 22:58 ½s¿è

¦^´_ 1# papaya


DATAªºAÄæ ¬O"¦r¦ê®æ¦¡" (2020/7/7  (¤G))¡AInputBox¿é¤Jªº¤é´Á¤]¬O"¦r¦ê®æ¦¡" (2020707)

¨â­Ó®æ¦¡³£¬O"¦r¦ê" ¤Sªøªº¤£¤Ó¤@¼Ë¡A­n«ç»ò¤ñ?

¥Î©î¸Ñ¦r¦ê¤è¦¡?  ·|«Ü³Â·Ðªü!

«Øij¤é´Á´N¦^Âk¨ì¤é´Á®æ¦¡¡A¥i¥H¥Î¦Û­q®æ¦¡ "yyyy/mm/dd (aaa)"

¤é´Á®æ¦¡¥i¥H°µ¹Bºâ¡A¦r¦ê®æ¦¡¤£¯à
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# n7822123
¤£¦n·N«ä¡A¦]¬°­è±µÄ²VBA¡AÁÙ¤£«Ü²M·¡½s¼gªº³W¯x¡A©|½Ð¨£½Ì
¦]¬°DATAªº¸ê®Æ¬O¤U¸ü¦ÛAPP³nÅé¡A©Ò¥H½Ð±NDATA¤é´Á®æ¦¡§ï¬°¦p2020/1/1,2020/7/17,2020/10/7§Y¥i¡A
InputBox¿é¤Jªº¤é´Á®æ¦¡¡A´N°t¦X§ó§ï«áªºDATA¤é´Á®æ¦¡¡C
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-7-27 00:58 ½s¿è

¦^´_ 3# papaya


¥ýÀ°§A§âAÄ檺®æ¦¡§ï¦¨¤é´Á®æ¦¡¡A§Ú¦Û»{µù¸Ñ¼g¤£¤Ö....¥B¨¾§b¼g¤£¤Ö

©Ò¥Hµ{¦¡¼gªº¤ñ¸ûªø¤@ÂI¡A§Æ±æ¬ÝªºÀ´§a~ µ{¦¡¦p¤U


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Arr, ¦¹ªí As Worksheet, ¤é´Á As Date, tim!, rg As Range, Rn&, xlsNm$, sh
Arr = Array("¤C¬F", "¤K¨ö", "¤­¦æ", "¤»¨R", "¥Í¨v", "¦X¼Æ", "§¡­È", "§À¼Æ")
Set ¦¹ªí = ActiveSheet
'1_¥ý³]¥ß1­Ó¥i¿é¤J¤é´Á(yyyymmdd)ªºInputBox
¤é´Á = InputBox("½Ð¿é¤J¤é´Á¡A®æ¦¡Ex:2020/07/07", "¿é¤J¤é´Á")
If IsEmpty(¤é´Á) Then Exit Sub
'ÀɮצWºÙ="¤j¼Ö³z_¿òº|ªÅÁ`²Î­pªí_¿é¤JInputBoxªº¤é´Á¡¨
xlsNm = "¤j¼Ö³z_¿òº|ªÅÁ`²Î­pªí_" & Format(¤é´Á, "mmdd")
'Àˬd¬O§_¤w¦³ÀÉ®×(ÁקK­«Âаõ¦æ)
Àˬd$ = Dir(ThisWorkbook.Path & "\" & xlsNm & ".xls*")
If Àˬd <> "" Then
  re% = MsgBox("¦¹Àɮפw¦s¦b¡A½Ð½T»{¬O§_Âл\?", vbYesNo)
  If re = vbNo Then Exit Sub Else Kill ThisWorkbook.Path & "\" & Àˬd
End If
tim = Timer  '¶}©l­p®É
'¤ñ¹ï¤é´Á
For Each rg In Range([A1], [A1].End(4))
  If rg = ¤é´Á Then Rn = rg.Row: Exit For
Next
'¦pªG¸ÓB¡JH="¡¨®É¡A«hArrªºA1¤]="¡¨
If Rn = 0 Then MsgBox "§ä¤£¨ì¤é´Á": Exit Sub
For Each sh In Arr
  With Sheets(sh): ¦¹ªí.Activate
    '2_¸Ó¤é´Á¶ñ¤J"¤C¬F","¤K¨ö","¤­¦æ","¤»¨R","¦X¼Æ","¥Í¨v","§¡­È","§À¼Æ"ªº¦U¤u§@ªí
    .[A1] = Format(¤é´Á, "yyyy/mm/dd")
    '¨Ã±NDATAªºAÄæ=X¤é´Áªº¤U1¦C¤§B¡JH¼Æ­È¶ñ¤J
    .[A3].Resize(7) = Application.Transpose(Cells(Rn + 1, 2).Resize(, 7))
  End With
Next
'3_±N§¹¦¨²Ä2¶µªº"Arr"¿é¥X¬°1­Ó¿W¥ßÀÉ®×
With Workbooks.Add
  For i = 0 To UBound(Arr)
    ThisWorkbook.Sheets(Arr(i)).Copy After:=.Sheets(.Sheets.Count)
  Next
  .Sheets(Array(1, 2, 3)).Delete
  .SaveAs ThisWorkbook.Path & "\" & xlsNm    '¨Sµ¹°ÆÀɦW¡AªO·sª©Excel³£¾A¥Î
  .Close True
End With
[Q2] = Round(Timer - tim, 2)
End Sub


Àɮצp¤U

49¤j¼Ö³z(¥DÀÉ).rar (22.26 KB)

§A¥t¤@©«»Ý¨D·Pı§ó¦h¡A¥ú¬Ý°_¨Ó´N·Pı¦³ÂI³Â·Ð.....

¬Ý¦³¨S¦³¤HÄ@·NÀ°§A¡A¤µ¤Ñ¥ý³o¼Ë
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2020-7-27 02:45 ½s¿è

¦^´_ 4# n7822123
¤£¦n·N«ä¡A©]²`¤F¡CÁÙ³Ò¾r±z¶O¯«¸ÑÃD~¤£³Ó·P¿E
´ú¸Õ¦¨¥\
©ú¤Ñ¦A¥J²Ó«ôŪ¶Qµ{¦¡»yªk~§Æ±æ¦Û¤v¦³¯à¤O®ø¤Æ~¦A¦¸­PÁÂ
=====================================
¥t¤@ÃD~±N¥þ³¡ªº»Ý¨D¤@¦¸´£¥X~­ì¥»¬O§Æ±æ¯àÅý¸ÑÃDªº»yªk¦³¨ä³s³e©Ê©M¤@ ­P©Ê¡A
¨S·Q¨ì¤Ï¦ÓÅã±o»Ý¨D½ÆÂø¦Ó¥O¤H«o¨B~«¢~«¢~

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2020-7-27 05:30 ½s¿è

¦^´_ 4# n7822123
¤£¦n·N«ä¡A½Ð°Ý:
¦C38    .Sheets(Array(1, 2, 3)).Delete
Array(1, 2, 3)¬O«ü¤°»ò½d³òªº¤u§@ªí³Q§R°£¤F?
ÁÂÁ±z^^

TOP

¦^´_ 6# papaya


Array(1, 2, 3)¬O«ü¤°»ò½d³òªº¤u§@ªí³Q§R°£¤F?

·s¼W¬¡­¶Ã¯·|¹w³]¦³3­Ó¤u§@ªí¡A©Ò¥H§â«e3­Ó¤u§@ªí§R°£

§A§â¨º¤@¦æµù¸Ñ±¼¡A¦A°õ¦æ´Nª¾¹D¬O¤°»ò¤F  

¦pªG¤£¤@¦¸¼g¡A­n¤À¤T¦æ¼g¡A·|Åܦ¨¦p¤U(³sÄò§R°£²Ä¤@­¶¤u§@ªí¡A3¦¸)


sheets(1).delete
sheets(1).delete
sheets(1).delete
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 7# n7822123
ÁÂÁ±zªº­@¤ß»¡©ú^^

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2020-7-28 01:32 ½s¿è

¦^´_ 4# n7822123
©Ó4#ªº¸Ñµª(¸U¤À·PÁ±z¸Ô²Óªº¤å¦rµù¸Ñ)

Äò¼W»Ý¨D¡J
¥H¥DÀɪºArr¦U¤u§@ªí¦WºÙ(¥H¤U²ºÙS)¤ÎSªºBÄæ©MCÄæ¡A·j´M.CSVÂà´«¬°.XLS«áªºÀÉ®×(¥H¤U²ºÙDÀÉ)¦WºÙ¤¤¤§²Ä3,4,5¤p¬q¦³¬Û¦P"ÃöÁä¦r"ªºÀɮסA
¨Ã½Æ»sDÀÉAZÄæ=SªºA1(©Î=InputBox¿é¤Jªº¤é´Á)ªº¤é´Á¤§¤W1¦CªºBA¡JBS¤º®e¡A¶K¤WS¤§¬Û¹ïÀ³EÄæÀx¦s®æ¡C
Ex1¡J¥HInputBox=" 2020/5/15¡¨¬°¨Ò~
±N¿òº|¤j¼Æ¾Ú-¤j¼Ö³z-¤C¬F±Æ§Ç-ªÅ¼ÆÁ`Äý-¤g-²Ä¤G²Ä¤T²Ä¥|²Ä¤­³Ì¥½-(2020-07-21)ªºAZ21(2020/5/15)¤§¤W1¦CªºBA20¡JBS20¤º®e¡A
¶K¤WS=¤C¬F¡ABÄæ=¤g¡ACÄæ=²Ä¤G²Ä¤T²Ä¥|²Ä¤­³Ì¥½¡AEÄæ=E7(=1,6,12,16,17,18,26,28,36,37,38,46,48,49)¡C

Ex2¡J¥HInputBox="2020/7/21¡¨¬°¨Ò~
±N¿òº|¤j¼Æ¾Ú-¤j¼Ö³z-¥Í¨v±Æ§Ç-ªÅ¼ÆÁ`Äý-ª¯-²Ä¤G²Ä¤T³Ì¥½-(2020-07-21)ªºAZ2 (2020/7/21)¤§¤W1¦CªºBA1¡JBS1¤º®e¡A
¶K¤WS=¥Í¨v¡ABÄæ=ª¯¡ACÄæ=²Ä¤G²Ä¤T³Ì¥½¡AEÄæ=E20(=14,16,17,18,22,25,27,31,32,33,35,48,49)¡C

¨ä¾l¡K¡K.¥H¦¹Ãþ±À¡C

´ú¸ÕÀÉ¡J TSET.rar (171.88 KB)

¥H¤W   ½Ð±zÄ~Äò½ç±Ð~ÁÂÁ±z^^

³Æµù¡J
1_§Ú¹ïVBAÁÙ¬O³B©óºVªù(©|ºÙ¤£¤W¤Jªù)¶¥¬q¡A©Ò¥H¹ï¬yµ{¶¶§Ç©Î¬ÛÃöªº¾Þ§@¤è¦¡¡AÁÙ¤£²M·¡¡A
¦]¦¹¬ÛÃöªº¬yµ{¶¶§Ç©M¤ñ¹ïªº¥À¤lÅé©Î¤è¦¡³£³Ò¾r¥H±zªººc·Q¬°©y¡CÁÂÁ±z¡I


2_¬°¦]À³¨C°õ¦æ¤@¦¸ªºCSV(¥ÑAPP²£¥Í)ªºAÄæ¤é´Á®æ¦¡¡A©Ò¥H§Ú¦Û¦æ±N¥DÀɾާ@ªº¤é´Á®æ¦¡§ï¬°¡¨yyyy/m/d¡¨¡C

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2020-7-28 06:48 ½s¿è

§Ñ¤F»¡©ú¤@ÂI¡J
¦]¬°¥u¦³²Ä1¦¸¦bInputBox¿é¤J¤é´Á®É¡A¦³»Ý­n¥Î¨ì.CSVÀɮרӲ£¥Í.XLSÀɮסA
¨ä¾l«áÄò¦A¿é¤Jªº¤é´Á¡A´Nª½±µ¤ñ¹ï¥Ñ²Ä1¦¸©Ò²£¥Íªº.XLSÀɮקY¥i¡A
©Ò¥H±z¥u­n¨ú´ú¸ÕÀɤ¤ªº¤l¸ê®Æ§¨(Âà´««áªºXLS)¤ºªº4­ÓXLSÀɮרӴú¸Õ´N¥i¥H¤F
ÁÂÁ±z

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD