Workbook Access Problem

I have a problem,
i have made a program that writes data to an excel file, the process takes approx 1 hour but will increase due to more data being written, the problem is that while this program is running a user can open any excel document on thier drive and the workbook that my program is using (writing data to) opens up with that file. I do not want any access to the workbook that is being written to, i want to somehow lock access, deny access, or hide the workbook so no matter what excel file you open - it wont let the workbook my program is working on open with it. Basically i want my program to compile an excel file in the background but at the same time a user can open any excel document close it, and open another without mine opening at all until the proceedure is complete.
Any ideas how i can fix this issue? I have already tried starting new excel, saving the workbook as something else then re-opening it but not so its visible and it works as long as you dont open another excel file. if the workbook is open like this shouldnt it bring up a msgbox saying file in use and give an option to read only??? it dont though.

Thanks

I'm not sure this will work, but have you tried password protecting the file that you are making? If you set a password before you write the data, then remove it after the process has completed then it should remain "locked" from other people (probably...)

i Have tried that and it still doesnt work, its because the workbook is opend by the program already so it can write data to it, so a password wud not make any difference. i need a way of hiding the workbook or making it visible = false. Im not sure how to do that. since my program writes so much data to the excel file (about 500,000 to 1million rows of data - spans across sheets) if the user does open the workbook by mistake or access it and happens to write any data to it then the program crashes coz of an application error (prob coz its accessing an activesheet and this has changed when the workbook is visible to the user) so it needs to be hidden away somehow. this is soo frusrating.

Hmmm... There must be some even which takes place at the start of a new instance of the application - maybe you can get it to hide your workbook from the active workbooks menu (Window list).

Otherwise it might be a case of subclassing Excel and removing the menu item using winAPI... but that's not a good way to go if you can avoid it.

I'll see what I can find out, but I've just mucked up my Office installation, so it might take a while...

Hiding it from the menu may well work, i will give it a try soon, however im jsut thinking about the user - a user of a program is incredibly stupid, (thats what my teacher said) so ya have to prepare for anything to make a stable rigid program.......what if the user tries to unhide it? thats if this works of course. can i make the application invisible? if i can could it be made visible by the user???

You can make the application invisible when you kick off an instance of it, but if they open it up via the normal shortcut then, as you have found, it will run up a copy and (usually) display the workbook...

You might do well by having the program / macro display a nice big message warning telling the user NOT TO TOUCH anything while your process is running (in conjunction with hiding the workbook), and if you have any idea of the total amount of (a) time or (b) number of records/rows you have to process then you could display a progress bar - always a feature I appreciate when running long processes.

While you cannot stop a user from running Excel (if it exists), you can stop them getting into a Worksheet.
If your VB program opens the Workbook as unshared - by opening an existing workbook name (even if it is blank), then when a user tries to run Excel, they get to see the title bar only, but excel will not run for them. (It acts as though it is locked up)
This has been my experience.
Look at the task list when you attempt to open a second occurance of excel and normally you will see the 2nd occurence there - this is the only way I have found to terminate the second occurance is to terminate the task here.

Hope you find that helpful.

The best way I could resolve it is if i can write to the file without it being open at all lol. hmmm dont think its possible. I blame Microsoft entireley of course since they made it all. As to the progress bar, i do have one on my program showing how long it will take (as you can imagine 500,000 to 1million records can take a very long time to process) therefore hoggin the cpu useage and blocking access to Excel for that length of time, which is not good since the main program used at any time is Excel. Even if a user opend a new instance of Excel it still would not work since my program writes to the activesheet, and of course if a new instance is opend then it will write data to the activesheet in that wouldnt it? so either way im very stuck on this problem. Dunno if you guys can help but i also had a topic about "Cell Values" on the forum but no-body responed yet - wonder if either of you know how to help me on that (im thinking if i can resolve one problem here im half way there lol)

Thank you very much.

Have you considered writing the data out to a flat file like *.csv ?? Then you would only have to kick off Excel to import the data - probably significantly faster than continuously passing messages to Excel to handle from VB - and you then don't have to worry about users messing around at the same time...

Can you not make the file Locked (I.e read-only) when you open it?

If you are using the excel object 8.0 within VB then I don't see why you can't do this.

that way if someone opens it, they cna change whatever they like, they wont be able to save it because it opens as read only.

This example will allow you to write to BIFF format (Excel Format).
It processes about 100 times faster than using an Excel Object!