Create Spreadsheet from Access

LJShepherd
LJShepherd used Ask the Experts™
on
Hi,

I have a number of spreadsheets that I have created and are populated by Access.

However I want to set these as Read only when they are opened by users, but retain Write properties when exporting data from Access

This is the command I am using to export the data to my spreadsheets

    stQryName = "All Internal Ncr by Customer Qry"
    DoCmd.TransferSpreadsheet (acExport), acSpreadsheetTypeExcel9, stQryName, "\\server\Databases\Internal NCR\Spreadsheets\All by Customer.xls"

Is there a simple command that I can add to make the spreadsheet read only, but still allow me to export access data to it.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you may need to use automation;

see my posted question from last week

the excel object SaveAs has a read only flag.
Are you appending the data to the spreadsheet each time or replacing the spreadsheet?  If you're creating a new spreadsheet each time you could write in your code that after the spreadsheet was created it was set to read-only.

Alternatively, it might be easier if the routine to bring the code into the spreadsheet was run from Excel rather than Access.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Hi PSi,

I am creating it each time, so what code woyuld I use in Acees to make the spreadsheet read only after creating the data?
You can not create a read only file using TransferSpreadsheet, you have to use automation.
LJShepherd,

I am afraid that you can't get a simple command to make the Exported Excel file to be read only. AFAIK, setting read only can be done within Excel Application only.

There might be possible solution on this but it needs more coding if you want to do it in Access.

Sincerely,
Ed
After you've created the spreadsheet use this:

SetAtrr "\\server\Databases\Internal NCR\Spreadsheets\All by Customer.xls", vbReadOnly

Open in new window

Oops, type:


SetAtrr "\\server\Databases\Internal NCR\Spreadsheets\All by Customer.xls", vbReadOnly
Dammit!  I mean:


SetAttr "\\server\Databases\Internal NCR\Spreadsheets\All by Customer.xls", vbReadOnly

Author

Commented:
Hi PSi,

Excellent, I used that code to set the spreadsheet to read only, but then I found that I couldn't export to it, but I used your code at the start of the transfer process and changed vbReadOnly to vbNormal, and it works perfectly for my requirements.

Points are on their way to you.

Many thanks, and thanks to all others who responded.

Les Shepherd
em, forgotten all about this little gem! I was partially right, "dammit" ;-))

Not for points, they should rightly go to PSi, however, you may have to add a little timer delay between the DoCmd and SetAttr commands

dim sStartTime as Date

sStartTime = now()
Do until now()>DateAdd("s",2,sStartTime)
Loop

Basically adds a 2 second delay before trynig to set the R.O. attribute

Author

Commented:
Thanks Runrigger, your comments are noted and taken on board.

Many thanks for your input

Les
happy to help where I can, on this occassion, I have learnt something valuable, so glad I participated.

The delay is only really relevant if you are exporting larger volumns of data, plus, if you have slower networks, it might help.

good luck anyway

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial