We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

cannot access file when exporting from datagridview to excel

Medium Priority
421 Views
Last Modified: 2012-05-11
Experts i  created a windows user control, for our webpage.  I bind a dataset to a datagridview and now i want to export that datagridview to an excel file.  I get this error:  Cannot access "file name here.xls".  I just need a very straight forward way to export this data.  any ideas?  My code fails on the xlWorkBook.SaveCopyAs and also tried xlWorkSheet.SaveAs.  I would like to use this code if possible.  The file_path comes from a SaveFileDialog object.
Public Sub excel_sequence_export(ByVal file_path As String)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To dgtest.RowCount - 2
            For j = 0 To dgtest.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    dgtest(j, i).Value.ToString()
            Next
        Next

        xlWorkBook.SaveCopyAs(file_path)
        '      xlWorkSheet.SaveAs(file_path)
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

Open in new window

Comment
Watch Question

HI

Can you show the call to the method , I think that you are sending the caption of the field instead of its content....

Author

Commented:
Here is the call, and the datagrid is bound before i do the export.  It does loop through all the rows and the data is there.
 Private Sub tsbexcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbexcel.Click
   
   Try
            Dim savefile As New System.Windows.Forms.SaveFileDialog
            savefile.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
            savefile.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
            If (savefile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                filename = savefile.FileName
            End If
          excel_sequence_export(filename)
              Catch ex As Exception
            MessageBox.Show("Error during transfer " & ex.Message)
        End Try
End Sub
I guess that there is a problem when you have blanks in the path like 'My Documents"
Try to verify by using a simple c:\ InitialDirectory
if this is the problem try to set the path as a 8.3 path (dir /x on the directory)
Carlos VillegasFull Stack .NET Developer

Commented:
Hi, did you say:
i  created a windows user control, for our webpage
So you are running this in your client web browser? on IE?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Generally speaking, this

 If (savefile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                filename = savefile.FileName
            End If
          excel_sequence_export(filename)

should be


If (savefile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
         filename = savefile.FileName
         excel_sequence_export(filename)
End If

Author

Commented:
I tried changing the location to c:\test\test.xls  and same error.  Yes this control is being run from IE 8.  I have been able to export to an excel using the same control and a oledb command object.  This method requires me to know all the column and types and i have to create the table.   With this new report the number of columns will be dynamic, it will be much simpler if I can loop through the rows in a datagrid and do a simple export.

 Dim cn As New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}Excel 8.0;HDR=Yes{1}", fileName, ControlChars.Quote)) '{1}, ControlChars.Quote
        Dim cmd As New OleDbCommand(String.Empty, cn)
       

cmd.CommandText = "CREATE TABLE HERE I must know all the columns and datatypes"
                        cmd.ExecuteNonQuery()
                        ' Transfer the data to the worksheet

                        cmd.CommandText = "INSERT ALL HERE, i must pass the column names and parameters"
                        With cmd.Parameters
                          'Every column requires a parameter value and type
                            '.Add("@c_value", OleDbType.Integer).SourceColumn = "MyCol"
                        End With
            Dim da As New OleDbDataAdapter
            da.InsertCommand = cmd
            da.Update(source, "Main")

Author

Commented:
Ok, when i this line of code:
  xlWorkSheet.SaveAs(file_path)  

The excel file is created and it looks like all the data is there, but i get all these microsoft file is corrupted errors and would i like to repair it, when it tries to finish opening.  Not sure what is going on.

Author

Commented:
This is the actual error:  System.Runtime.InteropServices.COMException (0x800A03EC):  Cannot access 'test.xls'
Carlos VillegasFull Stack .NET Developer

Commented:
Hi, I think that your problem raises because your IE is running in protected mode, add your domain name (or local address) to the trusted sites list, and try again.
Carlos VillegasFull Stack .NET Developer

Commented:
How to add to Trusted Sites

Author

Commented:
I am blocked from adding new sites, by our AD profile.  I am not sure that is the problem, why does the other method work?  What about using open xml to import to excel?  I am not stuck on this one method of exporting to excel, it just seemed like the fastest to export from a datagrid to excel.  If there is code that will work from xml to excel i would use that.
Carlos VillegasFull Stack .NET Developer

Commented:
But I need to know if your browser is running in protected mode when you load that control, only to understand better your scenario, the protected mode appears at right bottom of your web browser, it says On or Off when your are in your control page?

Author

Commented:
I dont see that disable protect mode check box, there is a lock icon with a sweeping arrow, which i have set to off and it didnt work, also set it to choose content to block and it didnt work.
Carlos VillegasFull Stack .NET Developer

Commented:
Well, Im trying to analyse your problem, I think that the problems comes because you have the protected mode ON in your IE8, there is a function (IEGetWriteableFolderPath) that you can use to know the correct folder path where you have read/write access, for example see this link so you can know what I'm talking about:
http://social.msdn.microsoft.com/Forums/en/ieextensiondevelopment/thread/10f6bb4d-ff81-4729-bac1-0e8b4ecfbba2

If you can write the file by using the OleDb classes, Im not sure why, remember that your want to use a com object, maybe something is implemented in a different way, but again Im not sure...

Im still dont know is your IE is running in protected mode, can you try to check it?...

Author

Commented:
yes, i will take a look
Carlos VillegasFull Stack .NET Developer

Commented:
Hi, if your problem is caused by the IE protected mode then you must to see this nice article:
http://www.codeproject.com/KB/vista-security/PMSurvivalGuide.aspx

Author

Commented:
Well i am pretty sure, our group policy is set at the Active Directly level, i cant find anywhere, where it shows if its in protected mode.  I dont have the protected mode checkbox,  like in your screen shot and i only have the greyed out lock in the bottom right corner, is there somewhere in my registry where i can check for that mode?

Author

Commented:
I went to this site:  http://www.sevenforums.com/tutorials/63141-internet-explorer-protected-mode-turn-off.html

i dont have that checkbox, there is nothing there.
Carlos VillegasFull Stack .NET Developer

Commented:
@tentavarious: An important question What Windows version are you using? because protected mode only work with Vista/7 windows versions....

Author

Commented:
i am using windows xp

Author

Commented:
One weird thing i noticed, that after i get the message saying it failed, the excel file pops up in the location i specified, but i get errors saying its corrupted.
Carlos VillegasFull Stack .NET Developer

Commented:
Therefore the IE protected mode is not your problem...

Author

Commented:
Ok, so has anyone been able to export a dataset to excel in a activex control?
Carlos VillegasFull Stack .NET Developer

Commented:
Hi, can you use this method to save the file?

Try
    xlWorkSheet.SaveAs(file_path, Excel.XlFileFormat.xlExcel8, , , False, False, False)
Catch ex As Exception
    MessageBox.Show(ex.ToString())
End Try

Open in new window


If an exception is raised please provide the full error stack trace
Carlos VillegasFull Stack .NET Developer

Commented:
I think that I got your answer... let me finish some tests...

Author

Commented:
Ok, it would be great if this code worked.  My only other option was to use a streamwriter and create a csv.
Carlos VillegasFull Stack .NET Developer

Commented:
Ok this is the code:
Public Sub excel_sequence_export(ByVal file_path As String)
	Dim xlApp As Excel.Application = Nothing
	Dim xlWorkBook As Excel.Workbook = Nothing
	Dim xlWorkSheet As Excel.Worksheet = Nothing
	Dim misValue As Object = System.Reflection.Missing.Value

	Try
	    Dim i As Integer
	    Dim j As Integer

	    xlApp = New Excel.Application()

	    xlWorkBook = xlApp.Workbooks.Add(misValue)
	    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

	    For i = 0 To dgtest.RowCount - 2
		For j = 0 To dgtest.ColumnCount - 1
		    xlWorkSheet.Cells(i + 1, j + 1) = _
			dgtest(j, i).Value.ToString()
		Next
	    Next

	    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)

	    xlWorkBook.SaveAs(file_path, Excel.XlFileFormat.xlExcel8)
	    xlWorkBook.Close()

	    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook)
	Catch ex As Exception
	    MessageBox.Show(ex.ToString())
	Finally
	    xlApp.Quit()
	    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp)

	    'This will close your Excel instance
	    GC.Collect()
	End Try
End Sub

Open in new window


First! open your windows task manager and look for EXCEL.EXE instances, end all that instances before testing.

Also please delete your destination xls file (if exists) before try this code

If this solve your problem please consider increase the question points before accept the solution ;)

Author

Commented:
Alright, i will let you know, i was able to get a stream writer to csv working, so if this doesnt work i have another solution.

Author

Commented:
I think that did it one question it is not showing the headers, i changed the -2 to 1 on the rowcount and it failed


    For i = 0 To dgtest.RowCount - 2
            For j = 0 To dgtest.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                  dgtest(j, i).Value.ToString()
            Next
          Next

Carlos VillegasFull Stack .NET Developer

Commented:
What type is dgtest???

Author

Commented:
that is a datagridview
Carlos VillegasFull Stack .NET Developer

Commented:
I think that must be done in this way:
'Headers
For i2 = 0 To dgtest.Columns.Count - 1
    xlWorkSheet.Cells(1, i2 + 1) = dgtest.Columns(i2).HeaderText
Next
'Data
For i = 0 To dgtest.RowCount - 1
	For j = 0 To dgtest.ColumnCount - 1
	    xlWorkSheet.Cells(i + 2, j + 1) = dgtest(j, i).Value.ToString()
	Next
Next

Open in new window

Author

Commented:
I almost got this one thing is puzzling me, when i pass the file name and location using a savefiledailog box, it doesnt work, i get the same error i get from the start.  When i hard code the name leaving out the exstension it does.  What is going on?

  xlWorkBook.SaveAs(file_path, Excel.XlFileFormat.xlExcel8)  'file_path contains the folder location and name

 xlWorkBook.SaveAs("c:\test\test", Excel.XlFileFormat.xlExcel8) 'this works when its hardcoded.


I made sure not to include a file exstension in the file_path variable, i wonder if it has something to do with the savefiledialog box.
Carlos VillegasFull Stack .NET Developer

Commented:
mmm what MS Office version is installed in the PC where you are testing the control?

Author

Commented:
office 2007
Carlos VillegasFull Stack .NET Developer

Commented:
Please try this:
xlWorkBook.SaveAs("c:\test\test123.xls", Excel.XlFileFormat.xlExcel8);

Open in new window

Runs fine?

Author

Commented:
Yes that works, i can have the file location and name stored in a textfield, like so and it still works.

txtname.text = "c:\test\test5.xls"
xlWorkBook.SaveAs(txtname.text, Excel.XlFileFormat.xlExcel8);


If i do this and pass filename to the export it doesnt work.

  Dim savefile As New System.Windows.Forms.SaveFileDialog
            savefile.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
            '  savefile.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
            If (savefile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                filename = savefile.FileName
           End If

If i do this and pass filepath & "\" & filename  to the export it doesnt work.
  Dim savefile As New System.Windows.Forms.SaveFileDialog
            savefile.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
            '  savefile.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
            If (savefile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                   filepath = System.IO.Path.GetDirectoryName(savefile.FileName)
                filename = System.IO.Path.GetFileName(savefile.FileName)
            End If


I have tried both of the above with and without the exstension and it doesnt work, is it something to do with the savefiledialog?

Carlos VillegasFull Stack .NET Developer

Commented:
Ok I will check your code...
Full Stack .NET Developer
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Well, i found the problem, remember my oledb connection i was using to create the excel file, they were sharing the same file and i was opening a connection when i didnt need to.  Once i closed the connection everything worked fine.  Pretty dumb mistake.
Carlos VillegasFull Stack .NET Developer

Commented:
Well, that things happens...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.