Need to Designate a Workbook as Shared using VBA

nigelh

New Member
Joined
Nov 28, 2007
Messages
29
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Basically:
Code:
Workbook.saveas FileName:="whatever", accessmode:=xlshared

Just be aware that Shared workbooks are notoriously unreliable.
 

nigelh

New Member
Joined
Nov 28, 2007
Messages
29
Rory,

Many thanks - I had used Close with the SaveAs parameter. I hadn't thought to do the SaveAs separately. DOH! And yes, I've had experience with shared books before, but in this case, the files are likely to be quite small, so hopefully I will get away with it.
 

nigelh

New Member
Joined
Nov 28, 2007
Messages
29
Rory,

Many thanks - I had used Close with the SaveAs parameter. I hadn't thought to do the SaveAs separately. DOH! And yes, I've had experience with shared books before, but in this case, the files are likely to be quite small, so hopefully I will get away with it.

Nigelh
 

Watch MrExcel Video

Forum statistics

Threads
1,122,685
Messages
5,597,531
Members
414,152
Latest member
ReservoirDodds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top