Creating an editable connection file for Excel worksheet

[Dogchow89]

Hello everyone, and apologies if this question does not belong here...

I have an Excel 2007 worksheet that multiple people use to create certificates. It pulls data from the company's SQL data source, as well as from a data table that is hand-entered onto a separate worksheet in that workbook.

A problem I am having is that because multiple people are using this file at the same time, we continually have the situation where my primary data entry users get the "locked for editing by another user" window. Also, management is getting fed up with having to close and reload the file every time data entry is saved in the workbook.

One solution I tried was to completely offload the manual data entry portion onto its own file, and have my certificate worksheet point to it as a data connection, so the data entry user can work within the data entry file while the other users can simply refresh the data. However, this does not work because whenever anyone has the certificate worksheet open, the data entry worksheet cannot be saved, as it gives the "*FILE* is currently in use. Try again later." error window.

Are there solutions or alternatives to this? The goal is to have the data entry user be able to input and save data, while the end users can simultaneously use their worksheet and refresh data as well. I am currently limited to Excel (no Access!) so I would like to make sure I exhaust my options before going to management to justify a software purchase.

 

[Fazza]

hi,

you describe one solution with the manual data entry portion in its own file. can you set that up to be just a data file. then when data entry is made to it, it is only a brief query. UPDATE or INSERT INTO or similar. So, not opening the file. just having it there for users to query.

If you need to have DELETE queries these can't be done to Excel files. but maybe you can work around that.

Maybe you don't know, you can have an mdb file. even without MS Access installed. (VBA can create it, btw. Suggest you google. There will be an old post ~10 years ago where I gave some late bound code to do that - as I wanted to be able to make mdb files from other users computers that did not have the VBA reference set for ADOX. I don't have that handy. Still, google should find plenty of early bound examples which will be fine.)

regards