[Solved] [EXCEL] - Sharing Sheet with Macros & Tables on FTP Network: Overcoming Save Errors & Message

Hello

I have a problem, namely on network FTP I have excel sheets enriched with macros that copy and paste data from a table into a table in another tgz sheet. Main. Because saving the macro saves and closes both sheets, but it happens that another user can view the main sheet and have it running at that time, when you try to save, we get the message:



Then the sheet is closed and there is a situation that the data has not been overwritten in the main sheet because another user had it fired.

I wanted to share a workbook, but it contains tables and gets the following message:



The question on my part is whether it is able to bite somehow differently to be able to share such a sheet?

Yes

Use a database

can it be a little brighter?

Darekstw wrote:

I have a problem, namely on network FTP I have excel sheets enriched with macros that copy and paste data from a table into a table in another tgz sheet. Main.

Before you explain to me - what do you mean by the term on network FTP I propose to do a little experiment :
open two instances of Excel on your computer in each of them open the same test file. xlsx try to change anything on this sheet in one instance and keep these changes :cry:

Databases that your colleague suggests to you JacekCz have built-in mechanisms to prevent uncontrolled multiple access - but I get the impression that you must definitely change your original concept of network data exchange (what will the server do and what will the clients do?)

When I use the term network FTP, I mean a mapped network drive with read and write access to various workstations. On it there is a catalog with excel sheets in which users fill in the data and, after running, the macros save at the same time in the file in which they worked and fall into the sheet that collects the data collectively. However, the problem occurs when one of the users (client) is viewing the sheet and another wants to run a macro at this point to save new data in both his own and collective sheets. Sharing is not an option because I'm using tables in the worksheet. The question is how, then, from this sheet to create a database about which I am writing JacekCz?

I would like it otherwise, if it is possible? Just do it in such a way that although one or more users are viewing the main sheet they are chopping the data into, another can use a macro on one of the other sheets and save the data in their own and the main sheet.

Unfortunately, I have no experience with network versions of office, but I have the impression that there it is necessary to install the appropriate group version of the application on the server (with a database or Excel files) and only to this server you set access to all terminals with appropriate permissions to individual files and actions on the server.

You can also try changing the settings of these files on the network drive by changing the privacy protection options, and setting sharing a workbook or co-creation - however, I have no way of checking the operation of these options

Prot wrote:

Unfortunately, I have no experience with network versions of office, but I have the impression that there it is necessary to install the appropriate group version of the application on the server (with a database or Excel files) and only to this server you set access to all terminals with appropriate permissions to individual files and actions on the server.

You can also try changing the settings of these files on the network drive by changing the privacy protection options, and setting sharing a workbook or co-creation - however, I have no way of checking the operation of these options

As I wrote when I share the workbook, I have the error mentioned in the first post, but I was interested in option 1, which you suggested, can you clarify it a bit more?

Darekstw wrote:

I was interested in option 1 you suggested

My suggestion is related to the test analysis:

Prot wrote:

I propose to do a little experiment:
open two instances of Excel on your computer in each of them open the same test file xlsx try to change anything in this sheet in one instance and keep these changes

In a normal system this is impossible however, even theoretically, it is possible with the "workbook sharing" setting on the network server
2018-02...png Download (82.2 kB)

That's right, but when I try to share the spreadsheet in the above-mentioned way, she gets the following message:



I use tables and pivot tables in the worksheet.

Darekstw wrote:

I use tables and pivot tables in the worksheet.

In my opinion - either convert the table to traditional Excel ranges (then sharing should do the problem - although this is also not certain), or transfer the data to the Access database.


andie

Andie wrote:

Darekstw wrote:

I use tables and pivot tables in the worksheet.

In my opinion - either convert the table to traditional Excel ranges (then sharing should do the problem - although this is also not certain), or transfer the data to the Access database.


andie

The vast majority of excell's legacy does not fit the relational database in any way, starting from the very concept *). In practice, plow and make a good tool from scratch. Moreover, is Access just the best? It's still a file database, and on shared drives, data protection is so-so. It is 2018 and many tools, whether free or open source.

There is not much in the Excel range for data division?

*) I omit that 2/3 of the macros bravely solve - like socialism - problems unknown in other systems

As the authors of Offica indicate feature-information-shared-workbook Unfortunately, this feature has serious limitations, including pivot tables
I see you have a newer Offic - you can verify the new sharing mechanism there

Andie wrote:

Darekstw wrote:

I use tables and pivot tables in the worksheet.

In my opinion - either convert the table to traditional Excel ranges (then sharing should do the problem - although this is also not certain), or transfer the data to the Access database.


andie

Unfortunately, I also use pivot tables in the worksheet, I cannot convert them to traditional ranges.

So it looks like the project would be best done as a relational database? Excel, unfortunately, cannot cope with this type of topic

Excel is not able to cope with this topic, please use a relational database for this type of situation.