• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating main file from externel

Hi all,

I'm in need of some scripting...

I have a main workbook that holds all the data, now I'm in need of a seperate workbook that users can enter new information that updates the main book. I have created this book and general layout but not sure about coding it to copy to main.

In file public1 there is a description of what i want to happen if anyone can assist me id be grateful.
 

Attachments

  • main1.xlsb
    548.3 KB · Views: 3
  • Public1.xlsm
    26.2 KB · Views: 2
For this type of operation, I'd strongly advise against using Excel. As there is no built-in conflict resolution mechanism and can cause unexpected result. You should use MS Access or other database software for such operation.

If Excel must be used, rather than each individual file writing to the master. Master should read from each file. Using PowerQuery, MS Query, ADO or some other method to bring in the data. Note that individual file should be kept in network drive, or some other location (OneDrive etc) where master would be able to make a connection to it.
 
So the Master file is on the works network drive, this is for the admin use in the office... Work now wants operatives to edit this file but due to the amount of people touching it I want a seperate file that just adds and edits one sheet and they can not mess with the rest of the data. We don't have MS Access at work.

I need the master file to be writable by admin as and when needed but also minor changes from jobs issued to be writable for the external file, this was my only idea i could think of, do you think there is a better way i could resolve this?
 
Hi !

It may be doable on a local drive between two smart workbooks
and on your technical analyze of the need your forgot to share here …
 
Personally, I'd still use Master to read from individual file. Individual file should have unique identifier for each row (unique across all files). With matching columns.

Then have edit sheet for admins. Admins will write to this sheet, which should have all the same fields, ensuring identifier matches to the record that should be changed/updated.

Then you'd merge queried record with admin sheet. Overwriting existing record when there is value present in admin sheet. And out put to final result sheet/workbook.

But above is still last resort, when no other options are available. I'd strongly recommend looking at other collaboration options. Such as... SharePoint, MySQL based system (using PHP, asp .Net, etc as front end). Which will support robust logging and allows access level based on role.
 
Back
Top