Getting error “Microsoft Excel cannot access the file…” on VB.Net based web application

[Hevel]

Sponsored Links

Hi guys

I need help on this issue , it really strange that I am getting this error and I have no idea what is the cause of this problem . The error that I am getting is “Microsoft Excel cannot access the file 'C:\Inetpub\wwwroot\SW\Uploads\IMPORT_file_4_2012-03-02_13.18.29.XLS'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook


I have looked everywhere but was not able to fine any answer to this issue . the thing is that I have this VB.Net based web application that has this feature that it can import excel file to server by clicking upload by the end-user and then the application will phrases the data which then stored again on the SQL Server. But every time that I am doing this its gave me the above error and I have no idea that what wrong with the application. Now I can see that excel file is present on my computer and that I am pretty sure that I am selecting the proper folder where the file has kept. I have the window 7 machine. IIS applicationpool user must have every right to the /Uploads folder. And I also tested this on the Windows Server 2003 and it just works fine there. Is there anyone here can tell me what is wrong with this, as I have been looking for the answer for the long time and I am not getting anywhere . I will be really appreciated if anyone form here can help me on this

Thanks in advance

[AabheerM]

That really might be frustrating for you but I m a really sorry that it is very hard to find out the problem just by the error that you are having , by the looks of it , its seems that there is some problem with the IIS config and Web config and this I am saying because that you have mentioned that the application is working fine with the windows server 2003 set up but I may be wrong. All I can say at the moment that you should refer the related config and after that just try IIS and ASP.Net category which can support you properly . hope this can help you on understanding . by the way is it possible for you to show here the code that is showing this issue , it would be nic e to see that and perhaps I someone might show the way to the solution on this.

[CADELARIA]

Well its looks like the problem can be solve with some changes in the registry settings , just follow this and see if this is helping you in any way
In the beginning just use the regedit (from the SysWOW64 folder) to find the registry which is for the CLSID(s) associated to the control "WINWORD.EXE /Automation" , well there are chances that you will be finding more than one but in my case it was two and that was {000209FE-0000-0000-C000-000000000046} as well as {000209FF-0000-0000-C000-000000000046})

Now under those key in the in HKEY_CLASSES_ROOT\CLSID\, just put in the string value that is AppID = {similar value that of IDs}. After that on the HKEY_CLASSES_ROOT\AppID\ just make the new key that is folder for all of the IDs and inside that folder just add the string value: RunAs = Interactive User. When this is done then the next step that you have to do is go to the Dcomcnfg (from the SysWOW64 folder) and look for those IDs, well there may be the third id that will be related top the to Winword.exe). in my case that was {00020906-0000-0000-C000-000000000046}. Take a note of all the IDs and just right click on those all IDs nad go ot the Properties > Security and after that you have to edit together the launch as well as the access permissions to insert plus allow the Network Service and Interactive total permissions.

This solution is logical and really surprise me that the Microsoft Support is not figure it out but all the article on the internet was able to find out this solution. I heop ti s will solve your problem that you are having at the moment all you have to do is follow the similar principle plus look for the correlated commands (with /Automation) in the registry. However I would like ot tell you that this might not be the solution on the all scenarios. This is because that the user might not have the MS Office installed on their system but I hope this work out for you

[Villiers]

I like to suggest you to the alternate method of reading excel file from stream. Well in my case if I need to to read an Excel spreadsheet from the SharePoint site. Its looks as fi that is just the simple request at any time I required to open any on the Excel file, I just make use of the OleDb connection with the subsequent connection string:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source={0};Persist Security Info=False;
       Extended Properties=""Excel 12.0;HDR=YES""";
connectionString = string.Format(connectionString, filePath);

well certainly that you can’t open the file on the SharePoint site this way, that why you have to understand that how to download the file by the web site. The System.Net namespace give a exceptionally simple means to do this:

WebClient client = new WebClient();
client.UseDefaultCredentials = true;
Stream stream = client.OpenRead(url);

at the moment you might have the file as a stream, therefore you must should just extract it, save it to a temporary file, after that read it in the standard technique. That will solve the Problem. Now this next bit of code supposed to possibly be optimized to some extent that why the array is not resizing in every single loop

BinaryReader brdr = new BinaryReader(stream);
byte[] result = new byte[0];
int bufferSize = 32768; // 32k
byte[] buffer = new byte[bufferSize];
long pos = 0;

while (true)
{
      buffer = brdr.ReadBytes(bufferSize);
      if (pos > 0)
      {
            // copy old data to bigger result
            byte[] temp = new byte[result.LongLength];
            Array.Copy(result, temp, result.LongLength);
            result = new byte[temp.LongLength + buffer.Length];
            Array.Copy(temp, result, temp.LongLength);
            // add new data
            for (int i = 0; i < buffer.Length; i++)
            {
                result[pos + i] = buffer[i];
            }
            pos += buffer.Length;
      }
      else
      {
            result = new byte[buffer.Length];
            Array.Copy(buffer, result, buffer.Length);
            pos = buffer.Length;
      }
      if (buffer.Length < bufferSize)
            break;
}
string tempFile = Path.Combine(Environment.GetEnvironmentVariable("TMP"),
      "CopyList.xlsx");
using (var fs = new FileStream(tempFile, FileMode.OpenOrCreate))
{
      var writer = new BinaryWriter(fs);
      writer.Write(result, 0, result.Length);
      writer.Close();
      fs.Close();
}

Well this will be worked perfectly for you in many cases as it did for me on my development machine however this is part of a larger application that is influence a SharePoint site through the SharePoint API. consequently, it should have to be running form the and that should be 64-bit. On my first attempt of this , I established the error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" . that is why I just search a little around and got that the perhaps I required to install the driver. It is obtainable on the Microsoft as a separate download, and I thought that I will able to make it work although . I installed it, but I was still getting the similar error . after that I found out that there is no OleDb driver that is required for opening Excel files on a 64-bit machine. So because of that I had to Just accumulate the project as x86, . thought that would going to work but I was so l, wrong, in fact. When the project is compile on the 32-bit, I can't access the SharePoint sites. So I have to do more , to find a technique to read an Excel file straight from a stream. And the Actually, Microsoft have actually useful guide that shows that t how to do it. click here . that said that the t Excel's native format which is called the BIFF (Binary Interchange File Format). therefore, you just have to parse every the bytes from the stream on the correct format. So on my further search I found few code Excel Data Reader

C# code

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
	//excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

VB.NET Code

Dim stream As FileStream = File.Open(filePath, FileMode.Open, FileAccess.Read)

'1. Reading from a binary Excel file ('97-2003 format; *.xls)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)
'...
'2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
'...
'3. DataSet - The result of each spreadsheet will be created in the result.Tables
Dim result As DataSet = excelReader.AsDataSet()
'...
'4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()

'5. Data Reader methods
While excelReader.Read()
    'excelReader.GetInt32(0);
End While

'6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close()

this work just for the Excel 2003 format, now this issue can be easily solved Save the spreadsheet to 2003 format and that how you will be able to read it , there will be few error for the reason that stream object returned from the web download didn't support seeking and that is what this code depends on so just have to save the stream out to a temp file and just read it back as the FileStream object, that supports seeking, that its problems solve. This is the way that you be able to open the Excel file from a SharePoint site into a spreadsheet with no use of generic OleDb connection. Hope this help

[Menominee]

I am not completely sure but this looks to me as the problem with the configuration . I am familiar with that the Microsoft at the moment is not recommend and is not supporting the Automation of Microsoft Office programs from any of the unattended, non-interactive client software or module. Although as you said that this has been woring so far on the Windows Server 2003 so I hope the following codes can fix this . I found it on the web


Imports Excel = Microsoft.Office.Interop.Excel

Result = oExcel.OpenExcel(Session("UploadFile").ToString, Session("ExcelSheet").ToString, ColCount, Cols, ColTypes, DecimalSign, ReturnMsg)

Result = oExcel.GetRowExcel(RowNbr, ColValues, ReturnMsg)

[Kamalaa]

Well the dot net is not capable to run the 64-bit mode and that is why it should remain on the 32-bit throughout the pipeline . in case that your site that is pure code which means that no .NET 1.1 DLLs uploaded to the web server, then the vista may be try to run it all the way through ASP.NET 2.0 and that is pre-installed on Vista in 64-bit mode. If you believe this possibly the reason , it should be probably to make it to work in 32-bit mode through the IIS settings. Can you tell me did you have any success on the Vista 32-bit. then i can able to help you for what you are actually looking for