Avatar of LJShepherd
LJShepherd

asked on 

Create Spreadsheet from Access

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
Microsoft OfficeMicrosoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Runrigger
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of LJShepherd
LJShepherd

ASKER

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?
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

You can not create a read only file using TransferSpreadsheet, you have to use automation.
Avatar of MINDSUPERB
MINDSUPERB
Flag of Kuwait image

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
ASKER CERTIFIED SOLUTION
Avatar of Phil Stratford
Phil Stratford
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of LJShepherd
LJShepherd

ASKER

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
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of LJShepherd
LJShepherd

ASKER

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

Many thanks for your input

Les
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo