open a file if a condition is fulfilled

hi guys,

I want to open a file (Sam) if a a condition is fulfilled.
So, my file Sam.xls has a sheet named "Control" (veryhidden), the cell A1 has value.
When I open Sam file, the code checks another file DOT.xls (the user should not see this process) and if it finds the same value in Sheet2 (veryhidden), cell A1, the file Sam to be opened.
If the code does not match the values the Sam file to be closed, Sheet "CHIO" to be deleted and the application closed.

Sam.xls is located here
C:\Documents and Settings\wer\Desktop\Tim

DOT.xls is located here
C:\Documents and Settings\wer\Desktop\Tim\Tim1

I really appreciate your help!
Thank you in advance!

regards,Tim

Hi Tim

I think this is the essence of what you want. The macro needs to be put as the Workbook Open Event for Sam.

Please Login or Register  to view this content.

hi Dominic,

Thank you for your solution. unfortunalely it's not working, the entire code is highlighted in yellow.

regards, Tim

Hi Tim

Have you put the macro in the WorkBook Open Event of Sam?
You need to start with Sam and Dot open.
Not sure if you are OK with macros in Events but you need to be in the Project Explorer usually on the left hand side of the VB widnow. Click on ThisWorkbook under Sam. Then in the two drop downs at the top of the code window you should have workbook on the left hand one and Open on the right hand one. Paste the code in. Put the same value in A1 of Sam and Dot. Save and close Sam. Open Sam and it should open. Close Sam again. Change A1 in Dot and Sam should not appear. Change A1 in Dot back to the original value and try opening Sam again.
Worked fine for me.

Dominic

hi Dominic,

Thank you very much for instructions. I've followed them! I had done the same thing...I attached a picture with the error received.

regards, Tim

Hi Tim
The error message is saying that it cannot find one of the sheets. I can see on the Explorer that you have Control in Sam. Have you Sheet2 in Dot? Alternatively, sometimes with 2007, VBA likes you to add the extension to the file name so in my code replace "Sam" with "Sam.xls" and "Dot" with "Dot.xls"
Keep trying!
Dominic

hi Dominic,
it's working ...if both files are opened. thank you!
but I want to open file Sam without to have the file Dot opened. Dot file should remain closed. is it possible ?

Tim

Hi Tim
Using VBA with closed files is not something I have experience of. If you search this site for GetValue you may find a solution.
Sorry I cannot help more.
Dominic

Hi Dominic,

Thank you for your help!
Tim

any idea, please?

Thank you FireFly for the solution!

Please Login or Register  to view this content.