I have a requirement to transfer data from one application to another that necessarily involves a number of different data mappings. To complicate matters, the target application contains various distinct workbooks (managed by different users), with each source "line" of information being transferred to one (but only one) of these workbooks, as determined by one of the source items of data. The first step of the required application validates that mappings exist for all source items - if they do not, reference data in the target application has to be updated. Only when the source file has been fully validated can the the transfer to the individual workbooks be attempted. A control requirement is that each source "line" is annotated once it has been processed, so that a post transfer audit can be carried out. At a simple level this suggests that the source file would be a "shared" workbook so that the transfer process can automatically update the transfer status information, even when different users are updating their own files. There is no data conflict in the source file from multiple users because source lines have a unique destination, and only the status information is updated. The implemented requirement will reside in a much larger VBA based application, and so will also be coded in VBA.
My initial design involves saving the source file after the validation process is complete, but with a different name (so providing a control point) and at the same time designating this new workbook as a Shared Workbook, which can be updated with the transfer status information. (I really do not want to have to create temporary "status workbooks" and then merge these back to the original to do the audit.) However, I cannot find anyway of using VBA to set the "Share" status. (Running the record function yields nothing.) Just to add to the confusion, the code is being developed under Excel 2010, but will run, initially at least, in Excel 2003.
Can anyone give me any ideas for a way forward?
Thanks
My initial design involves saving the source file after the validation process is complete, but with a different name (so providing a control point) and at the same time designating this new workbook as a Shared Workbook, which can be updated with the transfer status information. (I really do not want to have to create temporary "status workbooks" and then merge these back to the original to do the audit.) However, I cannot find anyway of using VBA to set the "Share" status. (Running the record function yields nothing.) Just to add to the confusion, the code is being developed under Excel 2010, but will run, initially at least, in Excel 2003.
Can anyone give me any ideas for a way forward?
Thanks
