Controling Excel file's "Protection" from in Access 97 1

[LittleSmudge]

I have an Access97 database and I'm exporting data using TransferSpreadsheet to a pre-exising excel file.

The file needs to have protection on it when users are working on it but, of course, with protection activated I cannot export ( Can't delete the old data before adding the new ).

So I need some code in Access97 that will turn off the protection if the file and turn it back on again once I've done the export.

I'm assuming I'll need the MS Excel Object Library referenced - but what objects do I need and how ?



G LS

 

[JerryKlmns]

Microsoft Excel x.0 Object Library has to be referenced

The Open method of the Workbooks object, has the password to open the file as the 5th argument. Open it and save the file without password and close it. Do your export, open it again and set the password back. The Excel object hasn't to be closed/destroyed during this process.

 

[RivetHed]

Jerry, that only works for workbooks that require a password to open rather than those with protected cells doesn't it?

 

[LittleSmudge]

Yes - I'm not talking about password protection of the file, I'm referring to the Cell protection withing the sheet.

The top row is protected from user "fiddling" as it is an Export and then after update become a source for data import - so I need to make sure the users cannot change the column headings.



G LS

 

[RivetHed]

You probably want something roughly like this then, customise to select the correct sheet, file etc.

Dim XLApp As Excel.Application
Dim XLwb As Excel.Workbook

Set XLApp = Excel.Application
Set XLwb = XLApp.Workbooks.Open("C:\Testing.xls")

XLwb.ActiveSheet.Unprotect ("Test")
'Do stuff
XLwb.ActiveSheet.Protect ("Test")
XLwb.Close SaveChanges:=True

Set XLwb = Nothing
Set XLApp = Nothing

 

[LittleSmudge]

Thanks RivetHed - that'll give me something to work on.



G LS