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

 

[VoG]

I think it should be

Sub Test2()
    Dim wb As Workbook, MyFile As String
    
    MyFile = ThisWorkbook.Path & "\Book2.xls"
    'wb.Unprotect Password:="123"**You try to use wb before you set it**
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, Password:="123") '**You set wb to equal your
        'workbook, IE "Book2.xls" becomes the activeworkbook wb equals thisworkbook**
        'now that you have set the variable you can use it almost anywhere**
    On Error GoTo 0

    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
  
    With wb
        .Unprotect Password:="123" '**this is the best place to use it**
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With
End Sub

 

[Norie]

There is another way to find out if a workbook (or any file) exists - use Dir.

If Dir(MyFile)="" Then
     Msgbox "File not found.
Else
     ' workbook exists so open it and continue
End If

 

[pedie]

Vog! I like you coming in late into my thread and answering which is perfect...I was think i was suppose to manually enter the password myself when the macro probs it but it did everything. Big problem solved.

Okay, once again thanks everyone! thanks Vog!
Thansk to the Forum & MrExcel!

 

[pedie]

Last time few people worked together and made this code worked but now I am facing a small problem again so please help!

Vog, if you're in here today....i think it is "xlsm" excel file type that is preventing from working....!
It opens up but ask for a password even though I have mentioned the password in the code itself!

Any help will be appreciated!
Thanks again!

Sub Test2()
    Dim wb As Workbook, MyFile As String
    
    MyFile = "[COLOR=blue]C:\Documents and Settings\pediez\UserData\My Folder\try1.xlsm"[/COLOR]
  
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, Password:="123")
    On Error GoTo 0
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
  
    With wb
        .Unprotect Password:="123"
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With
End Sub

 

[meldoc]

Pedie

It works for me, on my test location using the test password "123".

<font face=Courier New>    MyFile = "C:\Users\Desk\Desktop\MyVBA\Pass123.xlsm"</FONT>

 

[pedie]

Brian, let me try again. I have already tried like 7 times but did worked....I'm trying it again to make it 8 times

Please gimme a moment!

 

[pedie]

I dont know where I am doing wrong but it pop up asking for password or ready only.

 

[meldoc]

I can't say what you are doing wrong, I saved a blank Workbook called "Pass123" using the password "123", to the location above.
Using the Office Button, Prepare, Encrypt Document method, then I run the code from post #24, with the location changed as above, and the code works as expected.
Are you doing something different.

 

[pedie]

Brian,

This is exactly how I am coding it in book called "TRYBOOK1.xls" and i want it to run in trybook2.xls
I thought may be you can recheck where I am doing wrong

Thanks again!
Pedie

Sub Test2()
    Dim wb As Workbook, MyFile As String
    MyFile = "[COLOR=#0000ff]C:\Documents and Settings\pediez\UserData\My Folder\trybook2.xls[/COLOR]"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, Password:="123")  
              
    On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
  
    With wb
        .Unprotect Password:="123" '**this is the best place to use it**
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With
End Sub

 

[meldoc]

Tried again using post #29 code, and the method from post #28, changed the location and the extension to "xlsm", it works!
Try again using a different workbook and and password, I can't suggest anything else.