Please...Need help running macro in another workbook.

[pedie]

my current code which does not work:

Sub Test1()
Dim wb As Workbook, x As String
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then x = wb.Name
Next wb
MsgBox "The other open workbook is named " & x & "Book2" & vbCrLf & _
"Click OK to activate it."
Workbooks(x).Activate
Cells.Activate
Range("A1").Value = "Hello!"
Range("A3").Select
Range("A3").Formula = "=A1"
Workbooks(x).AcceptAllChanges
Workbooks(x).Save
Workbooks(x).Close
End Sub

Please advice how it works!
Pedies

 

[pedie]

AlphaFrog******** type=text/javascript> vbmenu_register("postmenu_2394605", true); *********> are you arround?

 

[pedie]

Sorry for the mess...

but please help!

 

[AlphaFrog]

wb.Unprotect Password:="secret"

 

[pedie]

Sub Test2()
    Dim wb As Workbook, MyFile As String
    
    MyFile = ThisWorkbook.Path & "\Book2.xls"
    wb.Unprotect Password:="123"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile)
    On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    
    With wb
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With

This is how i entered the code, and i got an error msg,
which states

Run-time error '91':
Object variable or With block variable not set

 

[pedie]

Whenever you have time please kindly reply.

 

[pedie]

Please any one...any idea to define something so that the password will be entered automatically and that the macro might run???

 

[meldoc]

Hello Pedie

You've put the line to unprotect the sheet, that AlphaFrog supplied in the wrong place see below.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test2()<br>    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    MyFile = ThisWorkbook.Path & "\Book2.xls"<br>    <SPAN style="color:#007F00">'wb.Unprotect Password:="123"**You try to use wb before you set it**</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open(Filename:=MyFile) <SPAN style="color:#007F00">'**You set wb to equal your</SPAN><br>        <SPAN style="color:#007F00">'workbook, IE "Book2.xls" becomes the activeworkbook wb equals thisworkbook**</SPAN><br>        <SPAN style="color:#007F00">'now that you have set the variable you can use it almost anywhere**</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br>    <br>    <SPAN style="color:#00007F">If</SPAN> wb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> MsgBox "Couldn't locate " & MyFile: Exit Sub<br>   <br>    <SPAN style="color:#00007F">With</SPAN> wb<br>        .Unprotect Password:="123" <SPAN style="color:#007F00">'**this is the best place to use it**</SPAN><br>        <SPAN style="color:#007F00">'.Activate</SPAN><br>        <SPAN style="color:#007F00">'.Cells.Activate</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            .Range("A1").Value = "hello!"<br>            .Range("A3").Formula = "=A1"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Save<br>        .Close<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Norie]

You can specify the password using the Password argument of the workbooks Open method.

PS You could also take a little longer between bumps, and explain what you actually want to to right at the beginning.

I was going to post on this last night but my network went down, but I was also couldn't quite relate the thread title to the thread content.

At first I thought you wanted to run code that was in another workbook, not use code to open a workbook etc.

By the way it might be an idea to add a dot qualifier . in front of ActiveSheet just to make sure you are working with the right worksheet.

 

[pedie]

Secial thanks to Meldoc, Alplha frog, Norie...& everyone who offered ideas and help by giving solid answers....
I really appriaciate it.

Pedie.

 

[pedie]

This is in reference to MELDOC's code.

I did exactly what is intructed here to do...but it is still probing for password...when I click on run the code, the msgbox for pasword comes up so I entered the password manually & it completed the process of the code. Is that what it is suppose to do, or is there a way I can make the password be auto entered???