Temporary Workbooks

bharatvly

Board Regular
Joined
Jun 3, 2014
Messages
61
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>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top