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

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
my current code which does not work:

Code:
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
 
I think it should be

Rich (BB code):
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
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
There is another way to find out if a workbook (or any file) exists - use Dir.
Code:
If Dir(MyFile)="" Then
     Msgbox "File not found.
Else
     ' workbook exists so open it and continue
End If
 
Upvote 0
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!:stickouttounge:
 
Upvote 0
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!

Code:
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
 
Upvote 0
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>
 
Last edited:
Upvote 0
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:biggrin:

Please gimme a moment!
 
Upvote 0
I dont know where I am doing wrong but it pop up asking for password or ready only.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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;)

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top