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

missbb

[µ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ªº­È¤Î®æ¦¡

¦³労½ç±Ð!

GBKEE

¦^´_ 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

missbb

¦^´_ 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ÁÂ!

GBKEE

¦^´_ 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

Hsieh

¦^´_ 3# missbb

­Y¤u§@ªí±Æ§Ç¿ù»~´N®e©ö¥X¿ù
«Øij±N¤u§@ªí¹ïÀ³±K½Xªí¼g¦APASSWORD¤u§@ªí(¦p¹Ï)
ª½±µ¨ú¥Î¤u§@ªí¦WºÙ¹ïÀ³±K½XÁקK¥X¿ù
   

¤U¸ü (74.74 KB)

2013-11-26 13:48

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

missbb

¦^´_ 5# Hsieh

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

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

GBKEE

¦^´_ 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

ML089

¦^´_ 6# missbb


§Ú´ú¸Õ¬OOK
¥i¯à¬OPASWORG¤u§@ªíAÄ椺ªº¦WºÙ»P¤u§@¦WºÙ¨S¦³¹ï¨ì

c_c_lai

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

Hsieh

¦^´_ 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