sharing excel file on a network

[kelly mort]

Hello there,
Can I have an excel file shared on a network for multiple users?

They will not be working on same sheets. They will use userform to send data to their worksheet. Thanks in advance
Kelly

 

[dmt32]

Hi,
yes you can share excel data across your network but personally, I would avoid doing this via a shared workbook. You can read more about reasons here:http://blog.contextures.com/archives/2008/11/18/avoiding-shared-workbooks-in-excel/

A better solution maybe could be to create template copies of your workbook for each user & have these write the data to a master workbook (database) - this way, each user can leave their copy of workbook open as long as they want.

Have read here:https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

for solution I provided for another (post #10 ) who had similar requirement & see if any help

Dave

 

[kelly mort]

Okay thanks. I am looking at it. Will let you know when I get stucked
Thanks
Kelly

 

[kelly mort]

Hi,
yes you can share excel data across your network but personally, I would avoid doing this via a shared workbook. You can read more about reasons here:http://blog.contextures.com/archives/2008/11/18/avoiding-shared-workbooks-in-excel/

A better solution maybe could be to create template copies of your workbook for each user & have these write the data to a master workbook (database) - this way, each user can leave their copy of workbook open as long as they want.

Have read here:https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

for solution I provided for another (post #10 ) who had similar requirement & see if any help

Dave

Hello Dave,
I am looking at the script. And I need help with how it works.

You talked about database workbook. I am confused here:

Does it mean that I have same workbook as the macro enabled but this one is macro free?

Guide me to get it done.
Thanks

 

[dmt32]

The Master (database) workbook is a non macro workbook (xlsx) on your network drive that users have Read / Write access to.
The Database normally would have only sheet in the workbook. The template copies of users workbooks contain the macros / userforms & these would write the data to the master workbook(database)

Dave

 

[kelly mort]

The Master (database) workbook is a non macro workbook (xlsx) on your network drive that users have Read / Write access to.
The Database normally would have only sheet in the workbook. The template copies of users workbooks contain the macros / userforms & these would write the data to the master workbook(database)

Dave

Okay thanks.
So if I got it correctly then it means same workbook but the master is macro free while the templates are macro enabled?

Okay.
So do i have to name the master uniquely or any name at all will do?

What about the templates?
Thanks again
Kelly

 

[dmt32]

Point 1 - Yes that is correct
name the database workbook that is relevant for it's purpose e.g. for a database of films simply call it Film Database.xlsx.
and again name the template workbooks in same manner e.g. Film DataEntry.xlsm

one thing to note with this approach is that you cannot have the Database workbook Open in Read / Write mode whilst users are writing data to it.

Dave

 

[kelly mort]

I keep getting type mismatch anytime I run the SaveTemplateData macro

I am testing this on a single pc for now.
Could this be a reason?

I am not on a network yet. I am doing the project for a friend who is facing issues in the office.

 

[dmt32]

turn off the error handling & see where VB editor stops.

Dave

 

[kelly mort]

It stops at :

If Not Dir (DatabaseName, vbDirectory ) = vbNullString Then

DatabaseName = False
vbDirectory = 16
vbNullString = ""