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]

Can someone make this work???

 

[pedie]

One last time....H E L P !

 

[meldoc]

Hi Pedie

The code works if you take out the line.

Workbooks(x).AcceptAllChanges

I don't really understand it except, that it has something to do with shared workbooks, and if that is what you want, then this is no help at all.

but here is a link.

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.acceptallchanges.aspx

 

[pedie]

I was expecting the macro macro might haved saved hello! in cell A1, but when i open Book2 it is not.

Am I doing somthing wrong????

 

[pedie]

I have book2 closed which is why it was creating prob. How can i make the macro open book2 by itself?
When I open book2 & run the macro it is perfectly okay but does not work when it is closed

 

[meldoc]

It works for me, both workbooks must be open.
Also the code assumes that the second workbook is "Book2", this is dangerous as it loops through all open workbooks.
And if you are not vigilant,(careful), you could overwrite data.

EDIT: This reply is for post #5

 

[AlphaFrog]

This will open Book2.xls. It assumes that Book2.xls is saved in the same location as the current workbook. If not, you'll have to edit the path and file name in Red.

Sub Test2()

    Dim wb As Workbook, MyFile As String
    
    MyFile = [COLOR="Red"]ThisWorkbook.Path & "\Book2.xls"[/COLOR]
    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
    
End Sub

 

[pedie]

Sure...thanks alot for inputting.
I was like waiting for you guys!!!!

 

[pedie]

Okay that worked perfectly...
Is there a way i can assigned a password for book2 in book1 macro so that even if book2 is locked with password it opens it??