Advise: Opening & closing of data files multiple times in macros related to a process

[hemantparmar]

Hi,

I need your advise on the below.

I have multiple macros running which uses 4 workbooks.
1. Book1 - This has all the buttons, forms and macros
2. Book2 - 4: These are Data files - e.g. Customer Master, Product Master, Transactions Data

Book1 has various Buttons e.g. Add New Customer, Update/Delete Customer, Add New Product, Update/Delete Product, Add a Transaction, etc.

Every time a button is clicked one or more user forms are opened and macros are executed. For each button operation one or more data files are opened, the necessary changes are performed and the files are closed before the operation of that button ends.

The objective in doing so is to not allow the user to get access to the data files. However, in this process the data files get opened and closed every time a button is clicked.

Is this optimal way to write the macros?
Is there any better way to handle this process?

rgds
hemant

[PaulSP8]

If you switch off Screen Updating then the users shouldn't see any of the workbooks that open in the background.

Please Login or Register  to view this content.

At the start of your code.

Please Login or Register  to view this content.

At the end.

Other than that then if you post your code (or even better, an example workbook) then we might be able to help more.

[hemantparmar]

Thanks Paul.

I am aware of the ScreenUpdating option. I have used it.

Let me explain with the help of the attached image.
Attachment 641975

When the user opens my process file (book1) the buttons as per attached image will be seen. For every button click a user form will pop-up, the required data file(s) will be opened in the background, the user will perform the necessary operation, the data will be written in the data files and the files will get closed. This happens for every button click till the user form is open.

One option is to open the data files with the file open event of Book1 and close the data files when the Book1 is closed. However, that will leave the data files open and the user will be able to access them. Therefore I am opening and closing the data files for every button operation.

I wanted to know
Is there any way to write in the data files without opening?
OR
Is there any other way to handle this so that repeated opening and closing of data files are avoided.

regards
hemant

[PaulSP8]

I've never needed to Read / Write from a closed Workbook myself so not sure, I took a quick look on Google and it does seem that it can be done but it's not something that is straight forward - Seemed to be a few different ways of doing it so I'll let you take a look at the different methods available instead of trying to link or list them here

That said, from what I understand of what is happening...

1) Click Button
2) Second Workbook Opens with information (or however many the information is stored across, for simplicity I'll just have one open for now ;-) )
3) User Makes a Change on Userform based on details from second Workbook.
4) Second Workbook is updated, saved and closed.

But you're concerned that during step 3 that the user has access to the data in the second workbook?

If that's the case then you could...

1) Click Button
2) Second Workbook Opens with information
3) Load all relevant details in to Variables
4) Close second workbook
5) User Makes a Change on Userform based on details from Variables
6) Reopen Second Workbook
7) Make changes from the stored variables
8) Close and save second workbook.

Personally I'd be tempted to start looking at Access for something like this - Either as means of storing all the information in Linked tables and building Queries or, just handling it all through Forms from within Access.

Sorry I can't be of more help - Maybe someone else can pick this up from the bump though