I'm trying to understand arguments!

[dylanr69]

but failing!

I'm ashamed to say that I've been muddling around with VBA for a while though don't truly understand the logic behind it - usually don't use it for a while then it's all hands to the pumps at work and I start trying to understand again!

anyways,

I've glued together some code that will cycle through all workbooks of a specific type, open them, find and replace a text string within the VBA module, save and close, then move onto the next one.

It works fine for unprotected workbooks, unfortunately our archived workbooks are password protected, so I'm trying to use some SendKeys code to enter the password.

problem is, when I add the line:

Application.Run ("UnprotectVBProject")

to the Sub DirFiles along with the other two I get:

run time 449

'Argument not optional'

I realise I'm in a bit of a mess with arguments - obviously i need to enter the actual password text string somewhere but I'm a little stumped

Sub DirFiles()
    Dim FileName As String, FileSpec As String, FileFolder As String
    Dim WB As Workbook
     
    FileFolder = ThisWorkbook.Path & "\"
    FileSpec = FileFolder & "*.xlsm"
     
    FileName = Dir(FileSpec)
    If FileName = "" Then Exit Sub
     
     '   Loop until no more matching files are found
    Do While FileName <> ""
        If IsWorkbookOpen(FileName) = False Then
             Set WB = Workbooks.Open(FileFolder & FileName, 0)
             'wb.Saved = True
             DoEvents
             Application.Run ("UnprotectVBProject")
             Application.Run ("ReplaceText")
             Application.Run ("CloseBook")
             'wb.Close True
            Debug.Print FileName
        End If
        FileName = Dir()
    Loop
     
End Sub
 
 
Function IsWorkbookOpen(stName As String) As Boolean
    Dim Wkb As Workbook
    On Error Resume Next ' In Case it isn't Open
    Set Wkb = Workbooks(stName)
    If Not Wkb Is Nothing Then IsWorkbookOpen = True
     'Boolean Function assumed To be False unless Set To True
End Function
Sub ReplaceText()
     
    Dim VBP As VBIDE.VBProject
    Dim VBC As VBIDE.VBComponent
    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean
     
    On Error Resume Next
    Set VBP = ActiveWorkbook.VBProject
    On Error GoTo 0
     
    If VBP Is Nothing Then
        MsgBox "Your security settings do not allow this macro to run.", vbInformation
        Exit Sub
    End If
     
    For Each VBC In VBP.VBComponents
        'If VBC.Type = vbext_ct_Document Then
            If InStr(1, VBC.Name, "ThisWorkbook", vbTextCompare) = 0 Then
                With VBC.CodeModule
                    SL = 1
                    SC = 1
                    EL = .CountOfLines
                    EC = 999
                    Found = .Find("dandy", SL, SC, EL, EC, True, False, False)
                    If Found = True Then
                        S = .Lines(SL, 1)
                        S = Replace(S, "dandy", "found", 1, -1, vbTextCompare)
                        .ReplaceLine SL, S
                    End If
                End With
            End If
        'End If
    Next VBC
     
End Sub
Sub CloseBook()
    Application.DisplayAlerts = False
    ActiveWorkbook.Close savechanges:=True
    Application.DisplayAlerts = True
End Sub
'need reference To VBA Extensibility
'need To make sure that the target project Is the active project
Sub test()
    UnprotectVBProject Workbooks("ABook.xls"), "password"
End Sub
 
Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
    '
    ' Bill Manville, 29-Jan-2000
    '
    Dim VBP As VBProject, oWin As VBIDE.Window
    Dim wbActive As Workbook
    Dim i As Integer
     
    Set VBP = WB.VBProject
    Set wbActive = ActiveWorkbook
     
    If VBP.Protection <> vbext_pp_locked Then Exit Sub
     
    Application.ScreenUpdating = False
     
    ' Close any code windows To ensure we hit the right project
    For Each oWin In VBP.VBE.Windows
        If InStr(oWin.Caption, "(") > 0 Then oWin.Close
    Next oWin
     
    WB.Activate
    ' now use lovely SendKeys To unprotect
    Application.OnKey "%{F11}"
    SendKeys "%{F11}%TE" & Password & "~~%{F11}", True
     
    If VBP.Protection = vbext_pp_locked Then
        ' failed - maybe wrong password
        SendKeys "%{F11}%TE", True
    End If
     
    ' leave no evidence of the password
    Password = ""
    ' go back To the previously active workbook
    wbActive.Activate
     
End Sub

 

[craig.penny]

Try taking the parentheses off of those lines.

Application.Run "UnprotectVBProject"

Instead of

Application.Run ("UnprotectVBProject")

 

[essentialkiwi]

The function you are calling with Application.Run requires two arguments to be passed to it:

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)

WB which is a workbook object and Password which is a string value

However you do not provide any arguments in you statement

Application.Run ("UnprotectVBProject")

but rather are just specifying the function that you want to call


the Application.Run statement should be constructed as below. Just enter your workbook object and password in the locations I have shown and it should work

NOTE: WB is a workbook object you have already created in your code. You just need to enter your passwod as a string therefore enclosed in " "

Application.Run ("UnprotectVBProject", WB, "PasswordStringGoesHere")

Check out this web page for more information about Application.Run http://msdn.microsoft.com/en-us/library/office/ff197132.aspx

 

[dylanr69]

cheers kiwi, that's just the ticket, has the code coming along nicely

thanks for the input, much appreciated

 

[essentialkiwi]

Glad it helped