• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro not running in Password file in office 2007

S

SRINATH SOMAYAJI

New Member
I have created a Macro for downloading NAV from AMFI website with password file (excel 2007), i am not able to run the macro since it is password file but if i remove the password from the file, i am able to run the macro

Question:
i want to run the macro in password file, as the data contains in the file are confidential, how to do that (since i am not able to get any help from the website and majority of the people are suggesting for removing the password)
 
Hi,

Just try this:

add below line before the beginning of actual process in the code.

sheetname.Unprotect ("Sheet Password")

And after the process just before the end of macro add below line

sheet name.Protect ("sheet password")

Just change the sheet name & sheet password in both the line.

Regards,
 
Try below code:

Code: 
Sub unprotect()
 
ThisWorkbook.unprotect ("abc") 'Workbook Password
  'Your Code goes here
ThisWorkbook.Protect ("abc")  'Workbook password
 
End Sub

Regards,
 
Somendra Misra said:
Try below code:

Code: 
Sub unprotect()

ThisWorkbook.unprotect ("abc") 'Workbook Password
  'Your Code goes here
ThisWorkbook.Protect ("abc")  'Workbook password

End Sub

Regards,[/quote
Somendra Misra said:
Try below code:

Code: 
Sub unprotect()

ThisWorkbook.unprotect ("abc") 'Workbook Password
  'Your Code goes here
ThisWorkbook.Protect ("abc")  'Workbook password

End Sub

Regards,
Click to expand...


Thanks, but still not working, i thought of attaching the excel sheet, but due to size restriction (file size 1.4 mb) i am unable to attached the file, request you to help me out how to resolve the same with a additional question of;


> How to create dynamic drop down list against respective Mutual Fund name, which is in "NAV Download Sheet" (refer "Fund House" column & "Scheme name" column) - for this you need to refer excel sheet but i am unable to attached the same
Click to expand...
 
Try saving the file in .xlsb format (binary format) to reduce file size and than upload it.

Regards,
 
Last edited:
Srinath,

If you keep the code to unprotect the workbook in the same 'protected' workbook then it will not work.

You will have to use a "cover / exe" workbook to do run the unprotect first on the workbook and then run the code.

Or you will have to use some other means if you don't want to invest in a cover workbook e.g. using a VBScript to fire the events.
 
@SRINATH SOMAYAJI

First lets tackel your initial problem.

First question: What is the purpose of keeping workbook password? Why I am asking this question is because if you are sending to others to have a look they need to know the password to open the workbook, to see the summary. So no point in keeping password protection.

Secondly, When I tried opening your workbook following things occurs:
  1. It prompted for password when I open the file. Password given "abc".
  2. Now I cannot add sheet, Unhide sheets or do anything related to sheet. The only sheet visible to me was summary sheet.
  3. If I run the macro It works perfectly (I am using Excel 2010).
Regards,
 
Copy following to a notepad and then save the file with extension as .vbs
Code: 
Option Explicit
Dim objXL
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

'\\ Provide correct information here
Dim strWB, strPath, strMacro
strWB = "Sample (Password for the file is abc).xlsm"
strPath = "C:\Temp\"
strMacro = "'" & strWB & "'!NAVUpdator"

Dim objWB
Set objWB = objXL.Workbooks.Open(strPath & strWB,True,False,,"abc")
objWB.Unprotect "abc"
objXL.Run strMacro
objWB.Protect "abc"

Msgbox "Macro run is completed!", vbExclamation

Please make adjustments to suit your path, workbook name, password etc.

This should open the workbook; unprotect it; run the macro and protect it again.

By the way, any chance that you were in Dr. B A T U Electronics Engineering? Your name sounds familiar to me.
 
Somendra Misra said:
@SRINATH SOMAYAJI

First lets tackel your initial problem.

First question: What is the purpose of keeping workbook password? Why I am asking this question is because if you are sending to others to have a look they need to know the password to open the workbook, to see the summary. So no point in keeping password protection.

Secondly, When I tried opening your workbook following things occurs:
  1. It prompted for password when I open the file. Password given "abc".
  2. Now I cannot add sheet, Unhide sheets or do anything related to sheet. The only sheet visible to me was summary sheet.
  3. If I run the macro It works perfectly (I am using Excel 2010).
Regards,
Click to expand...


Password is given to file is because, it is having confidential information and i am only the user having right to access this file
 
shrivallabha said:
Copy following to a notepad and then save the file with extension as .vbs
Code: 
Option Explicit
Dim objXL
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

'\\ Provide correct information here
Dim strWB, strPath, strMacro
strWB = "Sample (Password for the file is abc).xlsm"
strPath = "C:\Temp\"
strMacro = "'" & strWB & "'!NAVUpdator"

Dim objWB
Set objWB = objXL.Workbooks.Open(strPath & strWB,True,False,,"abc")
objWB.Unprotect "abc"
objXL.Run strMacro
objWB.Protect "abc"

Msgbox "Macro run is completed!", vbExclamation

Please make adjustments to suit your path, workbook name, password etc.

This should open the workbook; unprotect it; run the macro and protect it again.

By the way, any chance that you were in Dr. B A T U Electronics Engineering? Your name sounds familiar to me.
Click to expand...

no
 
Somendra Misra said:
@SRINATH SOMAYAJI

First lets tackel your initial problem.

First question: What is the purpose of keeping workbook password? Why I am asking this question is because if you are sending to others to have a look they need to know the password to open the workbook, to see the summary. So no point in keeping password protection.

Secondly, When I tried opening your workbook following things occurs:
  1. It prompted for password when I open the file. Password given "abc".
  2. Now I cannot add sheet, Unhide sheets or do anything related to sheet. The only sheet visible to me was summary sheet.
  3. If I run the macro It works perfectly (I am using Excel 2010).
Regards,
Click to expand...


Thanks, it is working post updating SP3, please let me know solution for my second question
 
Glad you make it working.

Now to your second question: can you elaborate more like, in which cell you will put the dropdown. On what cell it should remain dependent and any other useful information like how and when it will be updated.

Regards,
 
Thanks, please refer "Sheet 1" from attached "Sample" excel file, where i have mentioned the steps i required to dynamically capture Mutual Fund Name, Scheme Name, NAV date and NAV
 

Attachments

  • Sample (Password for the file is abc).xlsm
    134.5 KB · Views: 5
@SRINATH SOMAYAJI

One question : There are total 46 unique mutual fund names in column A of NAV Download Sheet.

Say UTI mutual fund is repeating number of times in NAV Sheet, so in summary sheet dropdown in column E against UTI sholud show all the Schemes?

Regards,
 
@SRINATH SOMAYAJI

Then I think don't put the dropdown in Column E, as scrolling through 1127 values is not a good idea. and similar for other values.

If you tell your objective than something else can be worked out.

Regards,
 
Somendra Misra said:
@SRINATH SOMAYAJI

Then I think don't put the dropdown in Column E, as scrolling through 1127 values is not a good idea. and similar for other values. = creating searchable drop down list will avoid scrolling down and selecting scheme (this is my view as i am just learing fx function / macros)

If you tell your objective than something else can be worked out. = objective is select invested scheme against respective Mutual Fund and arriving its NAV and NAV date, based on calculating return, holding period, realised gain , unrealised gain etc (refer attached excel sheet & Password the file is "abc")

Regards,
Click to expand...
 

Attachments

  • MF Investments.xlsm
    320.5 KB · Views: 2
@SRINATH SOMAYAJI

Refer attached file.

Addition in the code:

1. Remove blank rows from NAV sheet.
2. Put Unique fund names in column A of Sheet 2.
3. On sheet 2 in yellow cells filter the schemes as per selection made in dropdown of Summary sheet Column D.
4. Based on selection of column D , column E will Update on summary sheet.
5. Based on above selection NAV date & NAV price will update.
6. Commented the portion which is doing nothing in your code.
7. Created 3 Named Ranged.

Now some drawbacks:
1. As I am using array formulas to pull scheme names based on selection and your database is of the size 15000 rows everytime you will change selection it will take some time to update based on your system confuguration. :(
2. Even when you will run the code you may have to wait for some time too see the changes.
3. I am filtering scheme names based on first word of Fund name like Axis Mutual Fund so all the schemes with Axis in them will be listed but like some mutual fund start with FT where as there schemes names starts with Franklin, it will ignore them.

So in all this is not a total solution.

May be some VBA expert has to intervene into this to give you the right solution.

Please note I had deleted the data from NAV sheet to reduce file size as the file size had crossed 1 MB, so please run the code once.


Regards,
 

Attachments

  • Sample_(Password_for_the_file_is_abc) (Recovered).xlsm
    201 KB · Views: 1
Last edited:
Than
Somendra Misra said:
@SRINATH SOMAYAJI

Refer attached file.

Addition in the code:

1. Remove blank rows from NAV sheet.
2. Put Unique fund names in column A of Sheet 2.
3. On sheet 2 in yellow cells filter the schemes as per selection made in dropdown of Summary sheet Column D.
4. Based on selection of column D , column E will Update on summary sheet.
5. Based on above selection NAV date & NAV price will update.
6. Commented the portion which is doing nothing in your code.
7. Created 3 Named Ranged.

Now some drawbacks:
1. As I am using array formulas to pull scheme names based on selection and your database is of the size 15000 rows everytime you will change selection it will take some time to update based on your system confuguration. :(
2. Even when you will run the code you may have to wait for some time too see the changes.
3. I am filtering scheme names based on first word of Fund name like Axis Mutual Fund so all the schemes with Axis in them will be listed but like some mutual fund start with FT where as there schemes names starts with Franklin, it will ignore them.

So in all this is not a total solution.

May be some VBA expert has to intervene into this to give you the right solution.

Please note I had deleted the data from NAV sheet to reduce file size as the file size had crossed 1 MB, so please run the code once.


Regards,
Click to expand...

Thanks,
It is working, but as you said it is taking too much time to refresh and update the data (though my computer is having 4 GB ram with core2 duo processor)
 
You must log in or register to reply here.
Back
Top