[µo°Ý] ¦p¦ó³z¹L VBA»yªk ¶}±Ò±M®×ªº±K½X

jsleee

½Ð±Ð¦U¦ì¥ý¶i
    ¦pªG§Ú·Q³z¹L VBA »yªk ¦b¶}±Ò ¨ä¥LExcelÀɮ׮ɡA¦Û°Ê±N¨ä ±M®×ªº±K½X¥´¶}
    ´«¥y¸Ü»¡¡A³o¨Ç Excel Àɮתº ±M®×±K½X §Ú³£²M·¡¡A¥u¬OÃi±o¤@­Ó¤@­Ó¤â°Ê¿é¤J....
    ¤£ª¾¹D ¥ý¶i­Ì ¯à§_¨ó§U´£¨Ñ¸Ó»yªk....
    §Ú¦bºô¸ô¤W¬Ý¨ìªº³£¬O­n¯}¸Ñªº°Ê§@¡A¦ý§Ú¤£»Ý­n¯}¸Ñ¡A¦]¬°±K½X¥»¨Ó´Nª¾¹D¡A
    ¥u¬O·Q¦Û°Ê¥´¶}½}¤F....

    ÁÂÁÂ

JS

kimbal

³o­Ó¬O¼ÒÀÀÁä½Lªº¤èªk:

1. Sub UnprotectVBProj(ByVal Pwd As String, wb As Workbook)
   
2.     Dim vbProj As Object
   
3.     Set vbProj = wb.VBProject
   
4.     If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
   
5.     Set Application.VBE.ActiveVBProject = vbProj
   
6.     SendKeys Pwd & "~~"
   
7.     Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
   
8. End Sub

½Æ»s¥N½X

¥Îªk "ABC" ¬°±K½X, wb¬°¹ï¤è¬¡­¶Ã¯
CALL UnprotectVBProj("ABC", wb)

jsleee

¦^´_ 2# kimbal


    ÁÂÁ kimbal ¥ý¶iªº¦^ÂСA«Ü©êºp§Ú¹ê¦b ¸Õ¤£¥X¨Ó¡A
    ©Ò¥H§Ú±NÀɮתþ¤W¨Ó¡A·í§Ú·Q­n§Q¥Îªþ¥óÀÉ®× ¶}±Ò¬Y­Ó¥Ø¿ý¤¤ªº¥þ³¡ÀÉ®×
    ¦P®É±N¨C­ÓÀɮפ¤ªº ±M®×±K½X(°²³]¬O ABC) ¶}±Ò¡A
    ¤£ª¾¹D µ{¦¡½X ¸Ó¦p¦óµ²¦X¦b¤@°_¡H
    ·Ð½Ð ©âªÅ¦A¨ó§U¤@¤U¡A·P¿E¤£ºÉ

JS ¥[¯Z³æñ®Ö¦Û°Ê¶}Àɵ{¦¡test.rar (12.21 KB)

¤U¸ü¦¸¼Æ: 21

2012-7-4 09:07

oobird

1. Sub yy()
   
2. p = [b1] & "\"
   
3.     f = Dir(p & [b2])
   
4.    Do While f <> ""
   
5.     Workbooks.Open p & f, Password:="abc"
   
6.         f = Dir
   
7.     Loop
   
8. End Sub

½Æ»s¥N½X

jsleee

¦^´_ 4# oobird


    ½Ð±Ð oobird ¥ý¶i¡A±z´£¨Ñªºµ{¦¡½X ¬O§_¬O¶}±Ò ¬¡­¶³¡ªº±K½X¡H
    §Ú¥Ø«e­n¶}±Òªº¬O VBA±M®×¤¤ªºµ{¦¡½X ªº±K½X....
    ¬O§_¦³¤@¼Ë¡H·Ð½Ð¸Ñ´b....
    ÁÂÁÂ

¨ÎªY

jsleee

½Ð±Ð¥ý¶i
     §Ú¦b #3 ©Òªþ¤Wªº¨Ò¤l¬O§_¦³¸Ñ¡H
     §Úªº¥Øªº¬O­n¥Î¤@­Ó ExcelÀɮסA³v¤@¶}±Ò«ü©w¥Ø¿ý¤¤ªº©Ò¦³ExcelÀɮסA
     ¦P®É±N³o¨ÇÀɮתº VBA±M®×±K½X¸Ñ¶}....
     §Æ±æ¦³ ¥ý¶i ¥i¥H«üÂI¤@¤U¡A·P®¦
JS

jsleee

«e­±©Ò´£ªº±K½X ¬O¤w¸gª¾¹D ±K½X­È¡A
¥u¬O§Æ±æ³z¹L ¥t¤@­Óµ{¦¡ ¨Ó±N­ì¥»Âê¦íªº VBA±M®×±K½X ¥´¶}
¤£ª¾¹Dµ{¦¡½X¸Ó¦p¦ó§e²{¡H¡H¡H

JS

Hsieh

¦^´_ 7# jsleee


    #3ªºµ{§Ç´N¯à¸Ñ°£Âê©w

1. Sub ¶}±ÒÀÉ®×()
   
2.     Dim CurrentPath As String   'Àx¦s¥Ø«eÀɮץؿý
   
3.     Dim OpenFN As String   'Ū¨ú¨ìªºÀɮצWºÙ
   
4.     Dim FNExt As String    'ÀÉ®×°ÆÀɦW
   
5.     Dim MyBook As Workbook
   
6.     FN = ActiveWorkbook.Name
   
7. 
   
8.     CurrentPath = Range("B1")   '¦pªG¦³³]©w¥H³]©w¬°¥D
   
9.     FNExt = Range("b2")   '¬d¸ßÀÉ®×Ãþ«¬
   
10.     If Trim(CurrentPath) = "" Then
    
11.        CurrentPath = Excel.ActiveWorkbook.Path
    
12.     End If
    
13.    
    
14.     n = 0
    
15.     Sheets("trans").Cells.Delete  '±N¤§«eªºµ²ªG²M°£
    
16.     If Right(CurrentPath, 1) = "\" Then
    
17.         OpenFN = Dir(CurrentPath & FNExt, vbDirectory)
    
18.         OpenFNTime = CurrentPath
    
19.     Else
    
20.         OpenFN = Dir(CurrentPath & "\" & FNExt, vbDirectory)
    
21.         OpenFNTime = CurrentPath & "\"
    
22.     End If
    
23.    
    
24.     While OpenFN <> ""
    
25.         If OpenFN <> ActiveWorkbook.Name Then '³o­ÓÀɮפ£­nÅã¥Ü
    
26.             If OpenFN <> "." And OpenFN <> ".." Then
    
27.                 n = n + 1
    
28.                 fs = OpenFNTime & OpenFN
    
29.                 Sheets("trans").Cells(n, 7).Value = fs
    
30.                 Workbooks.Open(Filename:=OpenFNTime & OpenFN _
    
31.        , Password:="msign").RunAutoMacros Which:=xlAutoOpen
    
32.        Set MyBook = ActiveWorkbook
    
33.        UnprotectVBProj "password", MyBook '½Ð¦Û¦æ­×§ï±K½X
    
34.             End If
    
35.         End If
    
36.         OpenFN = Dir() 'Ū¨ú¤U¤@­ÓÀÉ®×
    
37.     Wend
    
38.    
    
39.     Workbooks(FN).Close savechanges:=False
    
40. End Sub
    
41. Sub UnprotectVBProj(ByVal Pwd As String, wb As Workbook)
    
42.     Dim vbProj As Object
    
43.     Set vbProj = wb.VBProject
    
44.     If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
    
45.     Set Application.VBE.ActiveVBProject = vbProj
    
46.     SendKeys Pwd & "~~"
    
47.     Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
    
48. End Sub

½Æ»s¥N½X

jsleee

¦^´_ 8# Hsieh

   ÁÂÁ Hsieh ¶W¯Åª©¥Dªº¨ó§U¡A§Úªº°ÝÃD¤w¸g¥þ³¡¸Ñ¨M¤F
   ·P®¦

JS

jsleee

¦^´_ 8# Hsieh

¦A½Ð±Ð¶W¯Åª©¥D¨â­Ó©µ¦ùªº°ÝÃD¡G
1. µ{¦¡°õ¦æ§¹«á¡A·|¯d¤Uªþ¥óªº¹Ï¤ùµe­±¡A¯à§_Åý¥L¦Û°ÊÃö³¬¡H
2. ¦pªG¤@¦¸¶}±Ò¦h­ÓÀɮסA¦n¹³¥u¦³¤@­ÓÀÉ®×¥i¥H¸Ñ¶}±M®×±K½X¡A
     ¨ä¥LÀÉ®×ÁÙ¬O¨S¦³¸Ñ¶}¡A¤£ª¾¹D­þ¸Ì¥X°ÝÃD¡H¡H
¦A³Â·Ð±z¨ó§U¸Ñ´b¡AÁÂÁÂ

JS