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

[µo°Ý] ¦h±i¤u§@ªí¥t¦s¬¡­¶Ã¯¤Î§ì¦í¹w³]±K½X

[µo°Ý] ¦h±i¤u§@ªí¥t¦s¬¡­¶Ã¯¤Î§ì¦í¹w³]±K½X

[µo°Ý] ¦h±i¤u§@ªí¥t¦s¬¡­¶Ã¯¤Î§ì¦í¹w³]±K½X
¦U¦ì±M®a
¥»¤H¥¿¾Ç²ßVBA, ½Ð°Ý¦p¦b¬¡­¶Ã¯¤º¦³3±i¤u§@ªí
²Ä1±i¤u§@ªí¦W¬°"PASSWORD",  ¤w¦C¥XA123ªºPASSWORD¦bB1, B456ªºPASSWORD¦bB2
²Ä2±i¤u§@ªí¦W¬°A123
²Ä3±i¤u§@ªí¦W¬°B456

½Ð±ÐVBA¼gªk:
²Ä2±i¤u§@ªí¦W¬°A123¥t¦s¤@­Ó¦W¬°A123ªº¬¡­¶Ã¯,. ¨Ã¯à¨ú¤u§@ªí¦W"PASSWORD"¤ºªºB1¤ºªº¦¨¬°¬¡­¶Ã¯±K½X, ¨Ã¥u¦s¤å¦rªº­È¤Î®æ¦¡
²Ä3±i¤u§@ªí¦W¬°B456¥t¦s¤@­Ó¦W¬°B456ªº¬¡­¶Ã¯,. ¨Ã¯à¨ú¤u§@ªí¦W"PASSWORD"¤ºªºB2¤ºªº¦¨¬°¬¡­¶Ã¯±K½X, ¨Ã¥u¦s¤å¦rªº­È¤Î®æ¦¡

¦³労½ç±Ð!

¦^´_ 1# missbb
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Wb As Workbook, E As Variant, xPath As String, i As Integer
  4.     Set Wb = ThisWorkbook           '¬¡­¶Ã¯ :µ{¦¡½X©Ò¦bªº
  5.     'Set Wb = Workbooks(2)          '¬¡­¶Ã¯ :²Ä2­Ó
  6.     'Set Wb = Workbooks("a.xls")    '¬¡­¶Ã¯ :«ü©w¦WºÙ
  7.     'Set Wb = ActiveWorkbook        '¬¡­¶Ã¯ :§@¥Î¤¤ªº
  8.     xPath = Wb.Path & "\"                '¦sÀɪº¸ô®|
  9.     With Wb.Sheets("password")
  10.         i = 1
  11.         For Each E In Array("a123", "b456")
  12.             Wb.Sheets(E).Copy
  13.             ActiveWorkbook.Sheets(1).UsedRange.Value = ActiveWorkbook.Sheets(1).UsedRange.Value           '¦s¤å¦rªº­È¤Î®æ¦¡
  14.             ActiveWorkbook.SaveAs Filename:=xPath & E & ".xls", Password:=.Cells(i, "b"), WriteResPassword:=""
  15.             ActiveWorkbook.Close False
  16.         Next
  17.         i = i + 1
  18.     End With
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE



±z¦n, ²Ä¤@­ÓÀÉ®×µL°ÝÃD, ¦ý¶}±Ò²Ä¤G­ÓÀÉ®×®É, §¡»¡±K½X¤£²Å. ¤w¸ÕÅç¨â¦¸¤Î¥Î¤£¦P±K½X¤]¤£¦æ.:(  ²Ä¤G­ÓÀɮתº±K½X¬O¦s¦bB2Àx¦s®æ¤º).

¦¹¥~, ²{¦b¥u¬O¦s¨â­ÓÀÉ®×, ¦ý¦pªG¦³20­ÓÀÉ®×, ¬O§_­n±N20­Óºh®×ªº¦W¦r³£¿é¤J¦bµ{¦¡¤º.
**For Each E In Array("a123", "b456"......."²Ä20­ÓÀÉ")

¦hÁÂ!

TOP

¦^´_ 3# missbb
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Wb As Workbook, E As Variant, xPath As String, I As Integer
  4.     Set Wb = ThisWorkbook          '¬¡­¶Ã¯ :µ{¦¡½X©Ò¦bªº
  5.     'Set Wb = Workbooks(2)          '¬¡­¶Ã¯ :²Ä2­Ó
  6.     'Set Wb = Workbooks("a.xls")  '¬¡­¶Ã¯ :«ü©w¦WºÙ
  7.     'Set Wb = ActiveWorkbook      '¬¡­¶Ã¯ :§@¥Î¤¤ªº
  8.     xPath = Wb.Path & "\"            '¦sÀɪº¸ô®|
  9.     With Wb.Sheets("password")
  10.         '*********³o¬¡­¶Ã¯¦³20­Ó¤u§@ªí+"password"*************************************************
  11.         For I = 1 To Wb.Sheets.Count - 1 'password ¤u§@ªí ©T©w¬¡­¶Ã¯¤¤¦ì¸m³Ì«á­±(©Ò¦³¤u§@ªíªº«á­±)
  12.         ' For i = 2 To Wb.Sheets.Count     'password ¤u§@ªí ©T©w¬¡­¶Ã¯¤¤¦ì¸m³Ì«e­±(²Ä1­Ó)
  13.         '*********³o¬¡­¶Ã¯¤£¥u¦³20­Ó¤u§@ªí+"password" -> ¬¡­¶Ã¯¤¤20­Ó¤u§@ªí¬O³sÄò¦b¦p(5-24)ªº¯Á¤Þ¦ì¸m**************
  14.         ' For i = 5 To 24
  15.         '********************************************************************************************
  16.             Wb.Sheets(I).Copy
  17.             ActiveWorkbook.Sheets(1).UsedRange.Value = ActiveWorkbook.Sheets(1).UsedRange.Value           '¦s¤å¦rªº­È¤Î®æ¦¡
  18.             ActiveWorkbook.SaveAs Filename:=xPath & Wb.Sheets(I).Name & ".xls", Password:=Trim(.Cells(I, "b")), WriteResPassword:=""
  19.                        '·í For i = 1  ->  Password:=Trim(.Cells(i, "b"))
  20.                        '·í For i = 2  ->  Password:=Trim(.Cells(i - 1, "b"))
  21.                        '·í For i = 3  ->  Password:=Trim(.Cells(i - WB.Sheets(3).Index + 1, "b"))
  22.                        '·í For i = 4  ->  Password:=Trim(.Cells(i - WB.Sheets(4).Index + 1, "b"))
  23.                         '±K½X·|¿ù»~,¥i¯à¬O«e«á¦³ªÅ¥Õ¦r¤¸ ,Trim:®ø°£«e«áªÅ¥Õ¦r¤¸ ->   Password:=Trim(.Cells(i, "b"))
  24.             ActiveWorkbook.Close False
  25.         Next
  26.     End With
  27. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# missbb

­Y¤u§@ªí±Æ§Ç¿ù»~´N®e©ö¥X¿ù
«Øij±N¤u§@ªí¹ïÀ³±K½Xªí¼g¦APASSWORD¤u§@ªí(¦p¹Ï)
ª½±µ¨ú¥Î¤u§@ªí¦WºÙ¹ïÀ³±K½XÁקK¥X¿ù
   
  1. Sub ex()
  2. Dim f$, fd$, fs$, A As Range
  3. fd = "D:\"
  4. With Sheets("PASSWORD")
  5. For Each A In .Range(.[A1], .[A1].End(xlDown))
  6.    f = CStr(A)
  7.    fs = fd & f & ".xls"
  8.    Sheets(f).Copy
  9.    With ActiveWorkbook
  10.    .ActiveSheet.UsedRange = .ActiveSheet.UsedRange.Value
  11.    .SaveAs Filename:=fs, Password:=CStr(A.Offset(, 1)), WriteResPassword:=""
  12.    .Close 0
  13.    End With
  14. Next
  15. End With
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh

Åã¥Ü°»¿ùCOPY¤èªk¥¢±Ñ ->    Sheets(f).Copy

§Ú§@¿ù¤F¬Æ»ò?:Q

TOP

¦^´_ 6# missbb
  1. Sub ex()
  2. Dim f$, fd$, fs$, A As Range, Wb As Workbook
  3.     Set Wb = ThisWorkbook          '¬¡­¶Ã¯ :µ{¦¡½X©Ò¦bªº
  4.     'Set Wb = Workbooks(2)          '¬¡­¶Ã¯ :²Ä2­Ó
  5.     'Set Wb = Workbooks("a.xls")  '¬¡­¶Ã¯ :«ü©w¦WºÙ
  6.     'Set Wb = ActiveWorkbook      '¬¡­¶Ã¯ :§@¥Î¤¤ªº
  7.     fd = "D:\"
  8.     With Wb.Sheets("PASSWORD")
  9.         For Each A In .Range(.[A1], .[A1].End(xlDown))
  10.         f = CStr(A)
  11.         fs = fd & f & ".xls"
  12.         Wb.Sheets(f).Copy
  13.         '¦p¦³¦h­Ó¬¡­¶Ã¯±Ò®É,¥BActiveWorkbook,¤£¬O­n½Æ»s¤u§@ªíªº¬¡­¶Ã¯.
  14.         '«ü©w¬O­þ¤@­Ó¬¡­¶Ã¯ªº¤u§@ªí­n½Æ»s
  15.         With ActiveWorkbook
  16.             .ActiveSheet.UsedRange = .ActiveSheet.UsedRange.Value
  17.             .SaveAs Filename:=fs, Password:=CStr(A.Offset(, 1)), WriteResPassword:=""
  18.             .Close 0
  19.         End With
  20.     Next
  21.     End With
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# missbb


§Ú´ú¸Õ¬OOK
¥i¯à¬OPASWORG¤u§@ªíAÄ椺ªº¦WºÙ»P¤u§@¦WºÙ¨S¦³¹ï¨ì
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-11-28 08:30 ½s¿è

¦^´_ 6# missbb
§Ú¥ç´ú¸Õ¹L Hsieh ª©¤jªºµ{¦¡½X¡A¤@¤Á¥¿±`µL³_¡A
¦³¥i¯à¬O§A¦b¬¡­¶Ã¯¶¡¤Á´«²¾Âà®É²£¥Íªº°ÝÃD¡C
¨ä¹ê GBKEE¡BHsieh ¨â¦ìª©¤jªº¸ÑÃD¦U¦³¨ä¤£¿ùªº¸àÄÀ¡C
§Ú±N¥¦­Ì¤©¥H¥[µù¡A¶Kªþ¦p¤U¡A¨âªÌ¶¡¦U¦³¨ä¥©§®¤§³B¡A
«Ü­È±o§@¬°­ÉÃè¡C
  1. Option Explicit

  2. Sub Ex()         '  GBKEE
  3.     Dim Wb As Workbook, E As Variant, xPath As String, xi As Integer
  4.    
  5.     Set Wb = ThisWorkbook             '  ¬¡­¶Ã¯ :µ{¦¡½X©Ò¦bªº
  6.     xPath = Wb.Path & "\"             '  ¦sÀɪº¸ô®|¡FÄ´¦p¡G xPath : "D:\TXT\" : String
  7.    
  8.     With Wb.Sheets("password")
  9.         For xi = 1 To Wb.Sheets.Count - 1   '  password ¤u§@ªí ©T©w¬¡­¶Ã¯¤¤¦ì¸m³Ì«á­±(©Ò¦³¤u§@ªíªº«á­±)
  10.             Wb.Sheets(xi + 1).Copy          '  «ü©w¬O­þ¤@­Ó¬¡­¶Ã¯ªº¤u§@ªí­n½Æ»s
  11.             '  Example:  Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
  12.             '  This example copies Sheet1, placing the copy after Sheet3.
  13.             '  Remarks:  If you don't specify either Before or After, Microsoft Excel creates a new workbook
  14.             '            that contains the copied sheet.
  15.             ActiveWorkbook.Sheets(1).UsedRange.Value = ActiveWorkbook.Sheets(1).UsedRange.Value  '  ¦s¤å¦rªº­È¤Î®æ¦¡
  16.                 '  FileFormat:=xlExcel8   Excel 2003ª©¥» 56; xlWorkbookDefault = Excel 2007, or 2010, or 2013.
  17.            ActiveWorkbook.SaveAs Filename:=xPath & Wb.Sheets(xi + 1).Name & ".xls", Password:=Trim(.Cells(xi, "B")), WriteResPassword:="", FileFormat:=xlExcel8
  18.             '   ActiveWorkbook.SaveAs Filename:=xPath & Wb.Sheets(xi + 1).Name & ".xlsx", Password:=Trim(.Cells(xi, "B")), WriteResPassword:="", FileFormat:=xlWorkbookDefault

  19.             ActiveWorkbook.Close False     '  Ãö³¬ "D:\A123.xls" ¬¡­¶Ã¯¡B"D:\B456.xls" ¬¡­¶Ã¯¡C
  20.         Next
  21.     End With
  22. End Sub
½Æ»s¥N½X
¦b Hsieh ª©¤jªºµ{¦¡½X¤¤¡AGBKEE ¼W¥[¤F Wb ªº¥[±j«Å§i¡A©ú½T¦a«ü¥X¬¡­¶Ã¯ªºÄÝ©ÊÂkÄÝ¡C
  1. Sub Ex2()            '  Hsieh & GBKEE
  2.     Dim f$, fd$, fs$, A As Range, Wb As Workbook
  3.    
  4.     Set Wb = ThisWorkbook             '  ¬¡­¶Ã¯ :µ{¦¡½X©Ò¦bªº
  5.     fd = Wb.Path & "\"                       '  ¦sÀɪº¸ô®|
  6.     With Wb.Sheets("PASSWORD")
  7.         For Each A In .Range(.[A1], .[A1].End(xlDown))
  8.             '  A                                   : "A123" : Range/Range
  9.             '  A                                   : "B456" : Range/Range
  10.             '  Sheets("PASSWORD").[A1]             : "A123" : Variant/Object/Range
  11.             '  Sheets("PASSWORD").[A1].End(xlDown) : "B456" : Variant/Object/Range
  12.             f = CStr(A)
  13.             fs = fd & f & ".xls"
  14.             Wb.Sheets(f).Copy      '  «ü©w¬O­þ¤@­Ó¬¡­¶Ã¯ªº¤u§@ªí­n½Æ»s
  15.             '  Sheets(f).Copy °õ¦æ¹L«á¡A½Æ»s¤F¤@¬¡­¶Ã¯¡A¤º¦³¤@¦W¬° "A123" ¤§¤u§@ªí³æ¡C
  16.             '  ActiveWorkbook.Name           : "¬¡­¶Ã¯1" : String
  17.             '  ActiveWorkbook.Sheets(1).Name : "A123"    : Variant/String
  18.             '  Sheets(f).Copy °õ¦æ¹L«á¡A½Æ»s¤F¤@¬¡­¶Ã¯¡A¤º¦³¤@¦W¬° "B456" ¤§¤u§@ªí³æ¡C
  19.             '  ActiveWorkbook.Name           : "¬¡­¶Ã¯2" : String
  20.             '  ActiveWorkbook.Sheets(1).Name : "B456"    : Variant/String
  21.             With ActiveWorkbook
  22.                 .ActiveSheet.UsedRange = .ActiveSheet.UsedRange.Value
  23.                 '  FileFormat:=xlExcel8   Excel 2003ª©¥» 56; xlExcel12  version 12, or 14, or 15 = Excel 2007, or 2010, or 2013.
  24.                 .SaveAs Filename:=fs, Password:=CStr(A.Offset(, 1)), WriteResPassword:="", FileFormat:=xlExcel8
  25.                 .Close 0       '  Ãö³¬ "D:\A123.xls" ¬¡­¶Ã¯¡B"D:\B456.xls" ¬¡­¶Ã¯¡C
  26.             End With           '  ¥¿¦¡µ²§ô (Ãö³¬)¡C
  27.         Next
  28.     End With
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# missbb
µ{¦¡½X¥u­n¬O©ñ¦b¦¹Àɮפº¡A¦P¤@­ÓEXCEL±M®×¨ÃµL¨ä¥L¶}±ÒªºÀÉ®×
¤£»Ý«ü©w¦¹¬¡­¶Ã¯¤]À³¸Ó¥i¥H°õ¦æ
·|¥X¿ù¥i¯àAÄ檺¤u§@ªí¦WºÙ»P­nCOPYªº¤u§@ªí¦WºÙ¤£²Å¡A¦ý¦¹Ãþ¿ù»~À³¸Ó²£¥Í¶W¥X°}¦C¯Á¤Þ¿ù»~
«Øij±N§AªºÀɮפW¶Ç¡A¤ñ¸û¯à°÷¤F¸Ñ¿ù»~ÂI
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD