Thread: Open workbook and apply code

Originally Posted by JapanDave

I am not sure if this is what you are after.

Private Sub AnalyzeNewWorkbook_Click()
Dim b18 As Long
Dim mPeriod As Range, RHAnalyzable As Range
Dim wbPath As Workbook

  Set wbPath = Workbooks.Open("C:YourFilePathHere")
        Set RHAnalyzable = wbPath.Worksheets("Random RH").Range("AJ:AJ")
        Set mPeriod = Worksheets("Internal Analysis").Range("J2")
        
        'counting how many analyzable in a given period for Test
        b18 = Application.WorksheetFunction.CountIfs(RHAnalyzable, "Yes", RHShortPeriod, mPeriod)
        Worksheets("Internal Analysis").Range("B18") = b18
  wbPath.Close savechanges:=False

End Sub

Hi JapanDave,

Yes, that's exactly it!

If the workbook this code is trying to open is password protected and has macros embedded in it, will this code at least get the workbook to open? Users would still need to type in the password as they normally would to have that workbook fully accessible right by this countifs right?

Is there any alternative solution without having the need to open that workbook? I thouht I had seen using SUMPRODUCT, but I'm not sure if that will take care of reading data from closed workbook?

Originally Posted by JapanDave

Try this,

Private Sub AnalyzeNewWorkbook_Click()
Dim b18 As Long
Dim mPeriod As Range, RHAnalyzable As Range
Dim wbPath As Workbook

  Set wbPath = Workbooks.Open("C:\YourFilePath", Password:="YourPassword")
        Set RHAnalyzable = wbPath.Worksheets("Random RH").Range("AJ:AJ")
        Set mPeriod = wbMoto.Worksheets("Internal Analysis").Range("J2")
        
        'counting how many analyzable in a given period for Test
        b18 = Application.WorksheetFunction.CountIfs(RHAnalyzable, "Yes", RHShortPeriod, mPeriod)
        Worksheets("Internal Analysis").Range("B18") = b18
  wbPath.Close savechanges:=False

End Sub

Hi,

I got an error saying method not found. The highlighted code is:

  Set wbPath = Workbooks.Open("H:\All Projects\New CCM TIA SPH Macro test 50.xlsm", Password:="SPH")

I have made sure that the file names are matching. Does this have to do with syntax or?

Originally Posted by Lifeseeker

Hi,

I got an error saying method not found. The highlighted code is:

  Set wbPath = Workbooks.Open("H:\All Projects\New CCM TIA SPH Macro test 50.xlsm", Password:="SPH")

I have made sure that the file names are matching. Does this have to do with syntax or?

Actually I got the filename incorrect....but it's working now thanks

Hi Japan,

The workbook I'm trying to open from this code has a switchboard, and it's been designed in a way that when the workbook is opened, the switchboard will pop up, allowing users to input the password.

In the code below,

Set wbPath = Workbooks.Open("C:\YourFilePath", Password:="SPH")

When I click on the button to open this new workbook from the calling workbook, I find myself STILL having to input the password in order to make worksheets accessible in the newly opened workbook. I thought that password line automatically takes care that?

for your reference, the code below is associated with the workbook I'm trying to open

Private Sub Workbook_Open()
     'Turn off screen updates
    Application.ScreenUpdating = False
     
     'Hide confidential sheet at startup
    Call hidesheets
     
     'Activate cell A1 on the Dashboard sheet at startup
    MainPagePHC.Show
    
     'Restore screen updates
    Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Call hidesheets
End Sub

Is this helpful information?

Thanks

Hi Roy,

It didn't solve the problem. I still had to input the password in order to get into the SPH workbook. Do you have any other alternative solution?

Thanks

Hi JapanDave,

I thought I should have made myself a bit more clearer last night as I was thinking more about it, but didn't realize you were so responsive in answering my requests.

I think the reason that it still asks for password AGAIN in the newly opened workbook is because for the password protection in THAT workbook, I didn't just use the built-in password protection in Excel. Rather, I wrote some self-defined code to first pop up the password window so users would just have to input the password in order to unlock worksheets in THAT workbook.

I am guessing.....if this version of the code does not work, it probably means that I didn't use the built-in password protection in Excel. Otherwise, the code line

Set wbPath = Workbooks.Open("C:\YourFilePath", Password:="YourPassword")

would have granted me access ONCE. Right?

Let me try your code anyhow and see.

Thanks

there you go JapanDave

I have created dummy workbooks.

Attached are two workbooks.

"forjapandaveMain" is the original calling workbook. To get in there, password is test

once you are in, go to the "analysis" page. On that page, you find a button. click on that button to open another workbook.

Note: So the password to access the 2nd workbook is "SPH", capitalized.

Let me know if you cannot get in. (I think you need to go into the VBA window to change the filepath of the 2nd workbook before you can open the 2nd workbook)

The problem is...because the way the 2nd workbook has been set up, users would have to type in the password AGAIN to unlock worksheets in THAT workbook. So the "password" statement found in your code does not really apply.

Thanks