vbNormal throws error
Hi,
I am trying to automate report (excel spreadsheet) production using access vba script and windows task scheduler.
I am getting error message at beginning of the scripts itself(given below).
SetAttr "Z:\Report_New.xlsx", vbNormal
(because I apply SetAttr "Z:\Report_New.xlsx", vbReadOnly at the end of all scripts.)
"Z" is a mapped shrepoint drive. The run time error 75 tells Path/File access error. But when I run the script manually there is no issue. When I left it to run over night, vba editor throws this error message.
Please let me know if you have any suggestions
Thank you
A
I am trying to automate report (excel spreadsheet) production using access vba script and windows task scheduler.
I am getting error message at beginning of the scripts itself(given below).
SetAttr "Z:\Report_New.xlsx", vbNormal
(because I apply SetAttr "Z:\Report_New.xlsx", vbReadOnly at the end of all scripts.)
"Z" is a mapped shrepoint drive. The run time error 75 tells Path/File access error. But when I run the script manually there is no issue. When I left it to run over night, vba editor throws this error message.
Please let me know if you have any suggestions
Thank you
A
Have you tried using the UNC path rather than the mapped drive?
ASKER
Hi Norie,
If I use that I need to apply userid/password, I don't know how to set up a vba script for that.
I don't think the issue is because of not using UNC. Because some nights, it works perfectly alright.
Also, the other database access this drive(Z) and dump reports without any issues automatically. Only difference is, those database does not have vbnormal or vbReadonly codes on it.
Cheers
A
If I use that I need to apply userid/password, I don't know how to set up a vba script for that.
I don't think the issue is because of not using UNC. Because some nights, it works perfectly alright.
Also, the other database access this drive(Z) and dump reports without any issues automatically. Only difference is, those database does not have vbnormal or vbReadonly codes on it.
Cheers
A
You've got it setup in task scheduler to run as a background process.
You need a user to be logged in for it to work. Norie's suggestion of using UNC path ( \\Server\Share) may work, but Access (and all Office products) really expect a desktop (user profile) to interact with and typically won't run as a background process.
Jim.
You need a user to be logged in for it to work. Norie's suggestion of using UNC path ( \\Server\Share) may work, but Access (and all Office products) really expect a desktop (user profile) to interact with and typically won't run as a background process.
Jim.
ASKER
Hi Jim,
Yes, I have set up everything. All other reports are being generated overnight. but only this report which has got vbNormal in the beginning along with the other scripts to generate it, got stuck when scheduler tries to run the vba scripts on the access editor.
Hope you get my point :(
Cheers
A
Yes, I have set up everything. All other reports are being generated overnight. but only this report which has got vbNormal in the beginning along with the other scripts to generate it, got stuck when scheduler tries to run the vba scripts on the access editor.
Hope you get my point :(
Cheers
A
hi Asatoma,
what if after SetAttr ..., you add:
what if after SetAttr ..., you add:
DoEventsASKER
Nop, that does not work
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.