Temporary Workbooks

[bharatvly]

Hello dear friends,

I have created a tool for my organization and it simply works awesome. Initially, I planned all the people using this tool to own a individual excel file and use. However; there now have been some changes.

This tool is going to be read-only and only single file is going to be used by over 30 users. The problem is that the tool uses of the sheets in the file for storing some temporary data. I decided to use below code and now the temporary data is going to get stored in new workbook created by below code.

I then refer to this variable in the rest of my code to access the workbook for the data on individual temporary workbooks.

Query -
Since the variable name is going to be the same for all the users, will the code pick up data from individual temporary workbooks? or is it going to get confused?

Code -

<code>Sub AddNew()
Set NewBook = Workbooks.Add
End With
End Sub

Please help!

Regards,
Bharat.
</code>

 

[Jerry Sullivan]

Hi Bharat,

The code won't be confused because multiple users have the file open read-only and are using the same code and variable names.

You will need to be aware of the scope of the NewBook variable.

The variable will lose its pointer to the new workbook when the procedure ends unless you declare the variable to have module or global scope.

If you are going to perform an action on the Temporary workbook immediately after creating it, then it would be best to limit the scope of the variable to the Sub and either:

1. Have the rest of your code that uses the NewBook variable located in the same Sub after the new workbook is created.

2. Pass a reference to the new workbook to other procedures that act on the new workbook.

btw, there's an unneeded "End With" in your example.