1

I am relatively new to VBA and I need a solution to a problem I have.

Currently I have two excel documents - one which is a form where the user enters data and this is then sent to a second document which acts as a database for this information. My issue is that when people are looking in the database data cannot be sent to the second document because it is open and there are overwrite errors.

It is my intention to have the databases location hidden away within a network drive so it cannot be found meaning the only way to access I is through a button on the first document.

Is there a way that when the open button is pressed to access the database that a copy of the document is opened instead of the actual document itself so data can still be sent?

braX
  • 11,506
  • 5
  • 20
  • 33
  • You can open a copy of it in code using `.Add` instead of `.Open` but you cannot change the functionality of the open button the user clicks. Your other option would be to set the file to read only so the user is forced to open a ready only copy of it. – braX Nov 18 '19 at 07:57

1 Answers1

0

So, you have a few options here:

a) Use an actual database. Not so much to solve this specific problem (that too) but also because making a "database" in excel is usually a terrible idea in the long run. But I know that is not always possible, and it's overkill in many situations so...

b) Have that "database" workbook set as "Shared Workbook". Shared Workbooks come with their own host of issues (namely, can't edit certain aspects like the VBA in them, conditional formatting, and others). But if it's strictly for reading and writing data, it can work. It also comes with its own version control, so that's good.

c) What you originally asked. It is quite simple really, as you said, you make a copy of the document, ideally in one of the user's local folders . You can do this using FSO (see VBA to copy a file from one directory to another)

The only issue with this last approach is that you need some way to track when the user has closed that temporary file, in order to delete it (if you absolutely don't want to leave any traces). Otherwise, you can just leave it, and overwrite that same file each time the user presses the "View Database" button.

Hawawa
  • 93
  • 1
  • 1
  • 6