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.
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
Do more with
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.
see my posted question from last week
the excel object SaveAs has a read only flag.
sorry, forgot to paste the link;
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27034282.html
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27034282.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.
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?
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
Dammit! I mean:
SetAttr "\\server\Databases\Intern
SetAttr "\\server\Databases\Intern
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial