Using VBA script within Excel for MS Office Professional Plus 2010.
Attempting to access a CSV (comma delimited file) within a document library in a SharePoint Site (SharePoint 2007), using VBA script.
The VBA script successfully opens the CSV file when running under Excel for MS Office 2003. I am in the process of migrating to Office 2010 and trying to evaluate & update VBA scripts written for Office 2003.
VBA line of code that is intended to open the CSV file is:
Workbooks.open Filename:="https://www.SPSite.Company.com/RawDataFolder/" & "RawData.csv", Format:=2
Symptom of Issue:
I'm getting the Run-time error '1004': Microsoft Excel cannot access the file 'https://www.SPSite.Company.com/RawDataFolder/RawData.csv'. There are several possible reasons:
- The file name or path does not exist
- The file is being used by another program.
I have spend considerable time reviewing web sites looking for a solution. I have not found a solution, yet.
Can anyone help me with this issue? Any ideas that I should try? I appreciate any help.
My experience of Sharepoint is limited.
If you can't open the file via file open, or presumably by putting the URL in a cell as a Hyperlink and following the link, there may be some problem in accessing the sharepoint site from this machine??
Rather than using the above URL to open the file, can you do File > Open > My Network Places >... and get to the file on Sharepoint that way?
Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
In Office 2010
I unsuccessfully tried opening the file:
- Through Excel 2010 menu: File > Open > select file in the file dialog (Bill Manville's suggestion).
- Double clicking on the file from the SharePoint site (using Windows IE 8).
- Opening the SharePoint Document Library using Windows Explorer and double clicking on file from Windows Explorer.
However, if I download the file to the local or network drive, I can open the document with either the Windows Explorer or the File > Open method.
From this experience, appears that the interaction of Office 2010 with SharePoint is an issue. Any possible solutions to this issue?
In Office 2003
I successfully tried opening the file using all of the above methods. I tried Office 2003 in both a:
- PC with Win XP OS and
- PC with Win 7 (using Win XP Virtual PC Applications).
For this error, here is a KB article introducing how to fix it in Office 2007 and SharePoint 2007. Although the versions of Office are different, it might give you some useful information. Here is the link: http://support.microsoft.com/kb/949405.
Also here is a thread in TechNet which might interest you: http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/8f3e9969-65d1-446c-a475-f9d3f99bd010.
Hope this helps you,
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thanks, Bessie, for your reply.
I tried "Hotfix for Office (KB949405)". After unzipping and executing the office-kb949405-fullfile-x86-glb application and accepting terms for use, the message I receive is "There are no products affected by this package installed on this system."
I reviewed the TechNet thread you referenced in your note. The thread refers also to KP840386, which applies to users of IE6. I am using IE8.
Do you have any other ideas to try? I appreciate your help with this issue.
To recap, I'm using Office 2010, Win 7, and IE8.