VBA to check if a excel workbook is already open

[sathyaganapathi]

Hi all,
I have two excel sheet. one contains live data and another contains archive data.
after completing data entry for every row I move the row contents from excel 1 to excel 2. since both the excel workbooks are used by many people, I have protected them password.
During moving the data, the macro automatically unprotectes both the excel workbooks and moves the row content to excel 2 and protects again saves. excel 2 is opened through macro and closed with VBA code written in excel 1.
Problem: when the excel 2 is opened "read only"/ normaly by somebody, and some one else tries to move the row content to excel 2, it tries to open the read only file again and moves the data to new read only file. due to this the data is lost.
solution needed: I want to know the VBA code to check if the excel 2 is already opened by somebody as read only and if opened, quit the program with a message "excel 2 is already opened, try later after closing excel 2", and if excel 2 is not opened, continue with row content movement from excel 1 to excel 2.

could somebody help with VBA code ?

code used in excel 1:

Please Login or Register  to view this content.

Regards, sathyaganapathi

[sathyaganapathi]

Hi all,
is there any solution for this?
Thanks.

[sathyaganapathi]

Bump no response... !!!

[mike7952]

Yo can check if the wb is open and if it is use the wb or if its not open it.

Please Login or Register  to view this content.

[sathyaganapathi]

Hi Mike,
Thanks for your code. I tried with it and got error when the excel 2 is already open. please see the attachment.
I am getting error in the code where I have marked with ------>>>>> when I click on "No".

Please Login or Register  to view this content.

[mike7952]

From looking at your jpg pic and your code dosent look like your trying to open "excel 2.xlsm"

[sathyaganapathi]

Hi Mike, Actually I have given "Lab Reports Status Tracker Archive log.xlsm" as the name for my excel work book. in the aboive code I have changed it to 'excel 2.xlsm' to make it simpler. sorry for the confusion here. "Lab Reports Status Tracker Archive log.xlsm" in the jpg pic = "excel 2.xlsm" in the code above.... !!
Thanks,
sathyaganapathi.

[mike7952]

Thry this should work.

Please Login or Register  to view this content.

[sathyaganapathi]

Hi Mike, Checked your code again. if the file areay opened as read only, macro copies the cell contents to the file and askes "the file exists already and do you want to replace it? !!.
Thanks, Sathyaganapathi.

[mike7952]

Ok I misssed the read only part, try this

Please Login or Register  to view this content.

[sathyaganapathi]

Hi Mike, Thanks a lot. The code worked fine with little modification to check if the file is already opened by somebody with password. Thanks a lot again. Have a nice weekend.
Regards, sathyaganapathi.