How to Create Shared Spreadsheet Using Output to

[mkatz13]

I create an excel spreadsheet from access usnig the
DoCmd.Output to method. However, the spreasheet is created
in exclusive mode only. Since it is linked to other spreadsheets, I need it to be in shared mode. Presently, I need to open it and using the sharing menu save it as shared. I would like to do this from the code in access which creates the spreadsheet.

I have tried variations of the SaveAs method, but I just get errors


Thanks


Mike Katz

 

[Stretchwickster]

From Excel I have written the following routine which initiates sharing for that sheet:

Public Sub ShareWorkbook()
  
On Error GoTo ERR_ShareWorkbook
  
  Mod_Global.Target_Book.Activate
  ' sets Target to be a shared workbook which handles conflicting changes
  ' by asking the user to select a resolution method
  ActiveWorkbook.SaveAs Filename:=Mod_Global.Target_Filename, _
                        AccessMode:=xlShared, _
                        ConflictResolution:=xlUserResolution
  ' ensures that updates occur only when the workbook is saved
  ' rather than every x minutes
  ActiveWorkbook.AutoUpdateFrequency = 0
  ' ensure that a history of any changes and conflicts will be logged for
  ' one month (i.e. 30 days) 
  ActiveWorkbook.KeepChangeHistory = True
  ActiveWorkbook.ChangeHistoryDuration = 30
  
EXIT_ShareWorkbook:
  Exit Sub
  
ERR_ShareWorkbook:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume EXIT_ShareWorkbook
  
End Sub

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[mkatz13]

Thanks , just what I was looking for


Mike Katz