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\Intern
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
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\Intern
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
Last Comment
sorry, forgot to paste the link;
https://www.experts-exchange.com/questions/27034282/TransferSpreadsheet-Alternative.html
https://www.experts-exchange.com/questions/27034282/TransferSpreadsheet-Alternative.html
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.
Alternatively, it might be easier if the routine to bring the code into the spreadsheet was run from Excel rather than Access.
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?
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
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
Oops, type:
SetAtrr "\\server\Databases\Intern
SetAtrr "\\server\Databases\Intern
ASKER CERTIFIED SOLUTION
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.
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
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,sStart
Loop
Basically adds a 2 second delay before trynig to set the R.O. attribute
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,sStart
Loop
Basically adds a 2 second delay before trynig to set the R.O. attribute
ASKER
Thanks Runrigger, your comments are noted and taken on board.
Many thanks for your input
Les
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
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 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
TRUSTED BY
see my posted question from last week
the excel object SaveAs has a read only flag.