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

[µo°Ý] ¦hµ§EXCEL·j´Mªº¥Îªk[¤w¸Ñ¨M 2011-05-11]

[µo°Ý] ¦hµ§EXCEL·j´Mªº¥Îªk[¤w¸Ñ¨M 2011-05-11]

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-5-11 15:06 ½s¿è

¦pÃD..
¦]¸ê®Æ¼Æ«Ü¦h¡A·Q»¡¥Î¤@­ÓEXCEL VBA ±N¨äÃöÁä¦r·j´M¥X¨Ó ¨Ã¥B ¥t¦s¤@¥÷·sªºEXCELÀÉ®×
¦pªþÀÉ...
¯à.rar (28.27 KB)

¤è«Kªº¸Ü¡A¯à§_¦b¯à§ó§ïªº¦a¤è¥[µù¤@¤U¡A·P®¦!!
¦]¦³ª¦¤å ¦ý¬O³£¥u¦³ ³¡¥÷¬ÛÃöªº¸ê°T ¨ÃµL²Î¾ãªº..
¦p¬Ý¤£À´ ©ÒªþªºÀÉ®× ½Ð§iª¾

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-10 11:04 ½s¿è

¦^´_ 1# mark15jill
»Ý¨D»¡©ú¡G
   1.·Q§Q¥Î ¦btext¤º ¿é¤J¬d¸ßªº¶mÂí(¦p ¥xÆW )  ¦A«ö ¬d¸ß ¬d¸ßªºµ²ªG±q ²Ä¤T¦C(A3)¶}©l±N¸Ó¸ê®Æ¦C¥X¨Ó¡C
     ¦p½d¨Ò¡G­n¬d¸ß ¥xÆW ªº¸Ü        (¸ê®Æ¶¶§Ç¨Ã«D«ö·Ó 123 ³o¼Ë¤U¥h  ¦³¥i¯à¬O¸õ¸¹ 20   25  30 27 µ¥µ¥¡C   
§AªºÀɮ׬O²²¤ªº½d¨Ò    ½Ð°Ý:
1.­n·j´MªºÀɮ׬O¦P¤@¥Ø¿ý¤UªºÀɮ׶Ü?  ,¦sÀɦb¦P¤@¥Ø¿ý¤U¶Ü?
2.¸ê®Æ¸¹½X ¬O§ä¨ì ¥xÆWªº¦C¸¹¶Ü?    ¥xÆW ¦P¤@¦C©¹¥k¦³¸ê®Æ¶Ü? ­n¤@°_§ì¶Ü?
3.½d¨Ò ¥i¥H¦A¹ê»Ú¨Ç¶Ü?

TOP

¬O­n±N¦U¦~«×¬¡­¶Ã¯¤¤¡A¨C­Ó¤u§@ªíªºIÄæ¡A²Å¦XÃöÁä¦r±ø¥óªº¸ê®Æ¥þ³¡§ä¥X¨Ó
µM«á§â³o¨Ç¸ê®Æªº¥X³B¸ê®Æ¥t¦s¬¡­¶Ã¯¶Ü?
¹ê¦b¬Ý¤£À´§Aªº½d¨Ò¡A­n¦p¦ó»P§Aªº»¡©ú°t¦X°_¨Ó
½Ð±N¦U¦~«×Àɮ׶ñ¤J¼Æ¾Ú
µM«á§â·Q­nªºµ²ªG¼Ë¦¡°µ¦¨¤u§@ªí¡A¦A°t¦X»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-5-10 11:30 ½s¿è

¦^´_ 2# GBKEE

©êºp ¬O§Úªí¹F¤£¦n
¦b³o»¡¤@¦¸..
¦A¬d¸ßªº¬¡­¶Ã¯¤ºªº TEXT ¿é¤J ©Ò­n¬d¸ßªº¦r¼Ë
µM«á «ö¤U¬d¸ßªºCOMBOªº«ö¶s
«h±N  97  98 99 ¦~«× ©Ò²Å¦Xªº ¦a§}(¨Ò¦p¥xÆW¦r²´ªº) ¦C¦b A3 ¨º¦C  (¬ÛÃöªº®×¨Ò ¦³­«ªþ¤WÀÉ®×)
¦C¥Xªº¸ê®Æ ¥]§t ¸Ó¸ê®Æªº©Ò¦³ ¡§¦C¸ê®Æ - ¦p²Ä¤@µ§ªº¸Ü´N¬O ¦ba3 b3 c3 d3 e3 ³o¤@¦C ¡¨
µM«á ¦pªG«ö¤UÀx¦sªº«ö¶s
´N·|±N ¬d¸ßªº¦r¼Ë Àx¦s¬°·sªº¬¡­¶Ã¯  (¥Ø¿ý¸ô®|»P¬d¸ßªº¥Ø¿ý¦P¤@­Ó)  EXCELÀɮפά¡­¶Ã¯ ¦WºÙ¬° ¬d¸ßªº¦r¼Ë
¦p ¬d¸ß ¥xÆW
«h ¥xÆW ³o­ÓEXCEL¦WºÙ ©M ¬¡­¶Ã¯¦WºÙ §¡¬° ¥xÆW   Àx¦s¸ô®| »P ¡§¬d¸ß EXCELÀɮס¨ ¦P¼Ë¥Ø¿ý
¤£¾å±o³o¼Ë¬O§_¤ñ¸û²M·¡..



§Ú¦b­«ªþ¤@¥÷®æ¦¡ªº¸ê®Æ(¤j·§®æ¦¡)

    ¯à.rar (29.55 KB)

TOP

¦^´_ 4# mark15jill
  1. Private Sub ¬d¸ß()
  2.     Dim Text$, File$, TheSh As Object, Sh As Worksheet, Rng As Range, RngAddress$
  3.     With ThisWorkbook            'µ{¦¡½X¸m©ó¬d¸ßÁ`ªí.xls
  4.         Set TheSh = .Sheets("¬d¸ß")
  5.         TheSh.UsedRange.Offset(2).Clear
  6.         File = Dir(.Path & "\*¦~«×*.xls")
  7.         Do While File <> ""
  8.             With Workbooks.Open(.Path & "\" & File)
  9.                 For Each Sh In .Sheets
  10.                     Set Rng = Sh.Range("e:e").Find(TheSh.TextBox1, LookAt:=xlWhole)
  11.                     If Not Rng Is Nothing Then
  12.                         RngAddress = Rng.Address
  13.                         With TheSh.Range("C" & Rows.Count).End(xlUp)
  14.                             .Offset(1, -2) = File
  15.                             .Offset(1, -1) = Sh.Name
  16.                         End With
  17.                     End If
  18.                     Do While Not Rng Is Nothing
  19.                         With TheSh.Range("C" & Rows.Count).End(xlUp)
  20.                             .Offset(1).Resize(1, 6) = Sh.Range(Sh.Cells(Rng.Row, "A"), Sh.Cells(Rng.Row, "F")).Value
  21.                         End With
  22.                         Set Rng = Sh.Range("e:e").FindNext(Rng)
  23.                         If RngAddress = Rng.Address Then Exit Do
  24.                     Loop
  25.                 Next
  26.             .Close 0
  27.             End With
  28.             File = Dir
  29.         Loop
  30.     End With
  31. End Sub
  32. Sub ¦sÀÉ()              'µ{¦¡½X¸m©ó¬d¸ßÁ`ªí.xls
  33.     Dim Sh As Object
  34.     On Error Resume Next
  35.     Set Sh = ThisWorkbook.Sheets(1)
  36.    
  37.     With Workbooks.Add(xlWBATWorksheet)
  38.         Sh.UsedRange.Offset(1).Copy .Sheets(1).[A1]
  39.         .SaveAs ThisWorkbook.Path & "\" & Sh.TextBox1
  40.         .Close 0
  41.     End With
  42. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-5-10 15:21 ½s¿è

¦^´_ 5# GBKEE

GBKEE ¤j¤j¤£¦n·N«ä ­è­è´ú¸Õ
µo²{¥H¤U´X­Ó°ÝÃD
1.±±¨î¶s Åܦ¨µL®Ä (¶È¯à¥¨¶°±±¨î)
2.¦sÀɤ´µM¦³ºÃ´b µL¦sÀɰʧ@
3.¦pªG±ø¥ó§ï¦¨ ¬ü°êªº¸Ü (¹w³]¬O¥xÆW)  ´N¨S¿ìªk¦³¦sÀɪº°Ê§@¥X²{

¶¶±a¤@°Ý..
§Ú¨º­Ó«ö¶s§Ë¹ï¶Ü= =!! ¦]¬° ¦³ACTIVE ©M ¥t¥~¤@ºØ  §Ú±`±`³£·|·d²V

ªþ¤W´ú¸Õ«áªº¸ê®Æ§¨

¯à.rar (25.92 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-10 15:42 ½s¿è

¦^´_ 6# mark15jill
1.±±¨î¶s Åܦ¨µL®Ä (¶È¯à¥¨¶°±±¨î)
«ü©w«ö¶s¥¨¶°  ½Ð¬Ý¦p¹Ï

EX1.GIF
2011-5-10 15:32

Sub ¦sÀÉ()               -> ¤½¥Î·|Åã¥Ü
Private Sub ¬d¸ß()    -> ¨p¥Î(Private)¤£·|Åã¥Ü
­n¥Î: ¨Ì¼Ëµe¸¬Äª ¥´¤W Sheet1.¬d¸ß

2.¦sÀɤ´µM¦³ºÃ´b µL¦sÀɰʧ@   
¥Ø¿ý¸Ì¨S¦¹ÀÉ®× ª½±µ´N¦sÀÉ

3.¦pªG±ø¥ó§ï¦¨ ¬ü°êªº¸Ü (¹w³]¬O¥xÆW)  ´N¨S¿ìªk¦³¦sÀɪº°Ê§@¥X²{
¦s¤G¦¸ ¬ü°ê ¬Ý¬Ý

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-5-10 16:06 ½s¿è

¦^´_ 7# GBKEE

1.¨â­Ó³£¨S¤º®e+.+
Sub «ö¶s5_Click()

End Sub
Sub «ö¶s6_Click()

End Sub

2.¤w¸g¸Ñ¨MµLªk¦sÀÉ ©M µL®Ä°ÝÃDQQ  ÁÂÁ±z
­è­è§âµ{¦¡¤À¶}¸Ë...
©î¦¨¨â³¡¥÷¥á¥h¨âÃä..

3.·s¼W«áªº¬¡­¶Ã¯¦WºÙ¥¼§ïÅÜ.. ¤£¹L­è­è¤w¸g¸Ñ¨M¤F..
­ì¥»¬Osheet1.  §Ú¦h+¤F¨â¦æ..
   

        .Close 0
          Sheets("Sheet1").Select
         Sheets("Sheet1").Name = Sh.TextBox1


4.¥t¥~½Ð°Ý¤@¤U  
¦pªG¨º¼Ëªº¸Ü ¨º¬O¥u­n¦P¤@¦Cªº¸ê®Æ ¤£ºÞ¦³¦h¤Ö ³£·|¦C¥X¨Ó¶Ü??
¦]¬°§Ú½d¨ÒÀÉ®×¥u¦³¨ìHÄæ¦ì   ¹ê»Ú¤W«o¬O¨ìZÄæ¦ì0.0..

¦A¦¸·PÁ±z  :lol

TOP

¦^´_ 8# mark15jill
1.¨â­Ó³£¨S¤º®e+.+     
¨S¦³µ{¦¡½Xªü, ·|¦³°Ê§@¶Ü?
3.·s¼W«áªº¬¡­¶Ã¯¦WºÙ¥¼§ïÅÜ.. ¤£¹L­è­è¤w¸g¸Ñ¨M¤F..
  1. Private Sub ¦sÀÉ()
  2. Dim Sh As Object
  3. On Error Resume Next
  4. Set Sh = ThisWorkbook.Sheets(1)
  5. With Workbooks.Add(xlWBATWorksheet)
  6. Sh.UsedRange.Offset(1).Copy .Sheets(1).[A1]
  7. .Sheets(1).Name = Sh.TextBox1      '¤u§@ªí¦WºÙ
  8. .SaveAs ThisWorkbook.Path & "\" & Sh.TextBox1
  9. .Close 0
  10. End With
  11. End Sub
½Æ»s¥N½X

4.¦]¬°§Ú½d¨ÒÀÉ®×¥u¦³¨ìHÄæ¦ì   ¹ê»Ú¤W«o¬O¨ìZÄæ¦ì0.0..
§ï¬õ¦r³¡¦ì
.Offset(1).Resize(1,26) = Sh.Range(Sh.Cells(Rng.Row, "A"), Sh.Cells(Rng.Row, "Z")).Value

TOP

¦^´_ 9# GBKEE

1.·íµM¨S°Ê§@XD!!    ¦]¬°¤£ª¾¹D­n«ç¼g ©Ò¥H ... ¤£¹L²{¦b¤w¸g¸Ñ¨M¤F ÁÂÁÂ
3.¨º­Ó­ì©l½X ¬O®M¥Î¤j¤j±zªº   ¥i¬O ­è¦³¸ÕÅç¹L ¤£¯à ¤£¹L«á¨Ó¦³·s¼W§Ú¦Cªº¨â¦æ ´NOK¤F...´N¬Osheet1 Åܦ¨ TEXT¦WºÙ  (¤£ª¾¹D­þ¥X¿ù-0-)
4.¤F¸Ñ¨º¦æªº·N«ä¤F... ÁÂÁÂ...

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD