Excel Error when opening file through VB script
-
23 февраля 2012 г. 15:36
I am fairly new to VB. I am using a vb script to open an excel file. It works on two of our computers. However 3 other co-workers get an error when they run the same script. Excel opens a new workbook and pops up an error windows that says Excel has encountered and error and needs to close. We are all on windows XP, Excel 2007.
Below is the script. There is a larger script, but i narrowed the error down to this set of lines. When i copied these into a .vbs all by themselves I get the error on the three computers. So I know this is culprit. I know the file and path are correct (since it works on 2 out of the 5 computers) and all have the same access to the same network drive. Do I need to declare teh objExcel, or am I missing something else? Your help/advice will be great appreciated.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "Fully Qualified Path\FileName.xlsx"
objExcel.Visible = True
W
Все ответы
-
23 февраля 2012 г. 15:54
Hi
there isn't anything wrong with the script. I'm guessing they can't access te spreadsheet for some reason.
Try copying the speadsheet locally, point the script to it and see if it works then.
BTW what's the error mesage?
Cheers
Alex
-
23 февраля 2012 г. 16:03
I define these using Public, objExcel, and objWorkbook
I open using the following snippet:
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(your fully qualied path/filename here with appropriate delimiting if the path contains spaces)Should work perfectly...
DAS
- Изменено Win7Tester 23 февраля 2012 г. 16:30 typos
- Помечено в качестве ответа Bill_StewartMicrosoft Community Contributor, Moderator 5 марта 2012 г. 16:18
-
23 февраля 2012 г. 18:16Модератор
The script works for me. If the path or filename are wrong, the error message says the file was not found. If you lack permission, the error should say access denied. If your error says Excel encountered an error, either the file is corrupt, or perhaps it is already open by another process. Make sure no one else has the file open. You can check this by copying the file to the same location with another name, then modifying the script for the alternate name to see if that works.
Richard Mueller - MVP Directory Services
-
23 февраля 2012 г. 18:22Модератор
Hi,
These are all good replies to this thread. This question and the replies are the reason for this blog posting by Raymond Chen:
When you say that something didn't work, you have to say how it didn't work
Bill
-
23 февраля 2012 г. 18:27
The script works for me. If the path or filename are wrong, the error message says the file was not found. If you lack permission, the error should say access denied. If your error says Excel encountered an error, either the file is corrupt, or perhaps it is already open by another process. Make sure no one else has the file open. You can check this by copying the file to the same location with another name, then modifying the script for the alternate name to see if that works.
Richard Mueller - MVP Directory Services
Just a note on file sharing with Excel.
If an Excel file is already open then you will be prompted to open it in ReadOnly mode.
It sounds more like a currupted disk drive or a corrupted Excel installation.
This may also be caused by Excel deadlocking with an AV program but I haven't seen that for years.
¯\_(ツ)_/¯
-
23 февраля 2012 г. 20:22
Thanks for all the replies. Win7Tester had a nice suggestion. I will try to refine my script to see if your suggestion helps. Additional info:
- I have verified that the file the script opens is not opened by another user.
- The file is on the same drive as the script.
- Other scripts (that don't open Excel files) and files work on that drive and all the users have access to.
- All users can open the excel file manually through excel or file explorer without issue.
- It also doesn't seem to matter whether Excel is already open before they run the script or not.
- The only difference is that some users get an error when they run the script and others do not.
Based on the info above my assumption is that either my code is not entirely correct or there is some sort of settings that are different between the computers. Thus causing some of them to error and others to work fine.
The error states:
"Microsoft Excel has ennountered and error and needs to close" along with an option to re-open Excel.
Thanks again.
W
-
23 февраля 2012 г. 20:38
I can force teh errro by purposely creating a corrupted file. I use powershell to edit an excel file and kill Excel before fuly saving the filke. When opening Excel it asks if I want to recover the file. If I say no and try and open the file Excel displays the error.
Delete the file and create a new one. Be careful not to break it.
¯\_(ツ)_/¯
-
23 февраля 2012 г. 21:10Модератор
If a script aborts before closing an Excel file, the file remains open in memory. You can see this in task manager. When troubleshooting scripts that open Excel files, I always check task manager and close all instances of Excel if the script raises errors. This situation is also resolved if you restart the computer. Can this account for what you experience? In my scripts I always close the spreadsheet file and quit Excel.
Richard Mueller - MVP Directory Services
-
23 февраля 2012 г. 21:31I gave it a try. I was missing the " Set objWorkbook =" on the second line. I updated my code to include this. But we still get the same error "Excel has encountered an error and needs to close. Sorry for the inconvenience..." and a check box to restart excel.
W
-
23 февраля 2012 г. 21:36
We have tried rebooting the machine in between attempts to run the script, but still get the same error "Excel has encountered an error and needs to close. Sorry for the inconvenience..." and a check box to restart excel.
"Excel has encountered an error and needs to close. Sorry for the inconvenience..." and a check box to restart excel.
I also updated the script based on Win7Tester's suggestion, but still get the smae error:
Set objExcel = CreateObject("Excel.Application")
Set objworkbook = objExcel.Workbooks.Open "\\Fully Qualified Network Path\FileName.xlsx"
objExcel.Visible = True
W
-
23 февраля 2012 г. 21:48Модератор
Hi,
Does this error occur when you open Excel manually and then access the file?
If so, then the problem is not with the script and you need to troubleshoot the Excel application.
Bill
-
23 февраля 2012 г. 22:50The error doesn't occur if the users open the excel file manually either through Excel > Open file nor by double clicking on the file in file explorer.
W
-
23 февраля 2012 г. 22:59
The error doesn't occur if the users open the excel file manually either through Excel > Open file nor by double clicking on the file in file explorer.
W
The problem is in Wxel registration and/or inte5rference from AV software.
Create a brand new Wxcel file with a minimal amount in it such as jsut a few lines. OPen this to prove that the problem does not exist with new file.
¯\_(ツ)_/¯
-
25 февраля 2012 г. 22:21Thanks for the help. I checked, the file is not open by another user and it is not corrupt. Additionally, I can run the script to successfuly open the file on two computers, but get the "Excel has encountered and error..." on three other computers. And it is consistent. The same two computers always work with the script and the other three always give the same error.
W
-
25 февраля 2012 г. 22:27Thanks jrv. The file and the script on a a network drive (the same one) and each of the computers can successfully connect to it and open the excel file manually. That was a good thought on the Excel installation possibly being corrupted... This is Excel 2007, so I ran the Excel diagnostics (formerly called detect and repair) on two of the computers that fail with the script. Excel diagnostics "fixed" several problems on each computer. We rebooted the each computer... but the script still fails with the same error.
W
-
25 февраля 2012 г. 22:30
Unfortunately, the script doesn't make it far enough to open the file let alone close excel gracefully. Hower, per your advice I did check the task manager, there is not an extra instance of excel on the task manager. I also tried rebooting the computer, but I still get the error when running the script.
W
-
25 февраля 2012 г. 22:50
Thanks jrv. The file and the script on a a network drive (the same one) and each of the computers can successfully connect to it and open the excel file manually. That was a good thought on the Excel installation possibly being corrupted... This is Excel 2007, so I ran the Excel diagnostics (formerly called detect and repair) on two of the computers that fail with the script. Excel diagnostics "fixed" several problems on each computer. We rebooted the each computer... but the script still fails with the same error.
W
You have a damaged Excel installation or possibly a corrupted Windows installation. Sometimes the only fix is to re-install office after a complete removal of Office including deleting all folders and 4registry keys.
It is now extremely clear that this is not a scripting problem. You should try posting in the Office 2007 forum as they would have more suggestions on how to fix Excel.
I generally design my implementations so I can very easily just reformat and reinstll the OS and applications whenever this kind of thing occurs. It save a lot of wasted time. COnsider that you haev spent more than two days on this and you still don't have a resolution. Since you can run the script on some machines it is pretty clear that the script is not the issue.
¯\_(ツ)_/¯
-
26 февраля 2012 г. 14:48Модератор
I think at this point we know that the script code is correct and you don't really have a scripting problem. It's apparent the problem lies elsewhere. I recommend you mark an answer to this thread and ask your question in an Excel forum.
Bill
- Помечено в качестве ответа FinanceTechie 28 февраля 2012 г. 3:01
- Снята пометка об ответе FinanceTechie 4 марта 2012 г. 23:04
-
28 февраля 2012 г. 3:03I agree with last couple of posts. We've been through all the easy stuff and even the basic script fails on some computers. So I'll try reposting on Office 2007. It may end up as an AV issue or a reinstall of Office or OS as a few have suggested. Thanks everyone for your suggestions.
W
-
4 марта 2012 г. 23:05 I found the solution:
Set objExcel =
CreateObject("Excel.Application")
set wbX = objExcel.Workbooks.Open("Fully Qualified path name\Filename.xlsx")
objExcel.Visible = True
This seems to work on all the computers with no errors.W
- Помечено в качестве ответа FinanceTechie 4 марта 2012 г. 23:06
-
4 марта 2012 г. 23:20
I found the solution:
Set objExcel =
CreateObject("Excel.Application")
set wbX = objExcel.Workbooks.Open("Fully Qualified path name\Filename.xlsx")
objExcel.Visible = True
This seems to work on all the computers with no errors.
W
And how is this any different than the code you originally posted?
You have basically changed nothing so you are saying that there was really no problem to begin with.
You stated originally that it worked on 3 out ot 5 computers. This says that the issue is local and not with the script.
Please note that Bill's answer was specific to this and that you should mark his reponse as an answer and unmark your statement because it does not provide any solution to anything.
Please understand that other people may be accessing this thread as a result of web searches. You wouldn't want to mislead them into a non-solution. Many will be as lost as you are with difficult troubleshooing issues. YOur question about declaring a varaible shows that you do not understand scripting or programming loanguages.
¯\_(ツ)_/¯
-
5 марта 2012 г. 16:22Модератор
Hi,
If that's the correct answer (parentheses missing), Win7Tester provided it back on 23 Feb 2012. (It would seem that there is some information that is not being provided for some reason.)
Bill
-
5 марта 2012 г. 16:39
Hi,
If that's the correct answer (parentheses missing), Win7Tester provided it back on 23 Feb 2012. (It would seem that there is some information that is not being provided for some reason.)
Bill
The original code does not require parens:
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "Fully Qualified Path\FileName.xlsx"
objExcel.Visible = TrueUsing 'Set' here would require parens but assigning an object on the open is not reqquired because the work book can be access as: ActiveWorkBook,
I suspect something else was wrong which the OP is not willing to share.
For anyone reading thsi the troubleshooting information from all contributors is valid. The solution marked as an answer is useless.
¯\_(ツ)_/¯
.png)
