Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

cannot access file when exporting from datagridview to excel

Posted on 2011-04-26
41
Medium Priority
?
388 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

0
Comment
Question by:tentavarious
  • 20
  • 18
  • 2
  • +1
41 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 35470932
HI

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

Author Comment

by:tentavarious
ID: 35471037
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
0
 
LVL 19

Expert Comment

by:elimesika
ID: 35471109
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)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35471620
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35473852
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
0
 

Author Comment

by:tentavarious
ID: 35475340
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")
0
 

Author Comment

by:tentavarious
ID: 35475633
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.
0
 

Author Comment

by:tentavarious
ID: 35475697
This is the actual error:  System.Runtime.InteropServices.COMException (0x800A03EC):  Cannot access 'test.xls'
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35475891
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35476013
How to add to Trusted Sites
0
 

Author Comment

by:tentavarious
ID: 35476148
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35476232
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?
0
 

Author Comment

by:tentavarious
ID: 35476322
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35476413
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?...
0
 

Author Comment

by:tentavarious
ID: 35476469
yes, i will take a look
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35476511
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
0
 

Author Comment

by:tentavarious
ID: 35477333
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?
0
 

Author Comment

by:tentavarious
ID: 35477379
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35478090
@tentavarious: An important question What Windows version are you using? because protected mode only work with Vista/7 windows versions....
0
 

Author Comment

by:tentavarious
ID: 35478130
i am using windows xp
0
 

Author Comment

by:tentavarious
ID: 35478148
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35478371
Therefore the IE protected mode is not your problem...
0
 

Author Comment

by:tentavarious
ID: 35478454
Ok, so has anyone been able to export a dataset to excel in a activex control?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35478717
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
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35478808
I think that I got your answer... let me finish some tests...
0
 

Author Comment

by:tentavarious
ID: 35478903
Ok, it would be great if this code worked.  My only other option was to use a streamwriter and create a csv.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35479141
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 ;)
0
 

Author Comment

by:tentavarious
ID: 35479162
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.
0
 

Author Comment

by:tentavarious
ID: 35479250
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

0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35479280
What type is dgtest???
0
 

Author Comment

by:tentavarious
ID: 35479305
that is a datagridview
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35479341
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

0
 

Author Comment

by:tentavarious
ID: 35485942
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35486033
mmm what MS Office version is installed in the PC where you are testing the control?
0
 

Author Comment

by:tentavarious
ID: 35486052
office 2007
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35486096
Please try this:
xlWorkBook.SaveAs("c:\test\test123.xls", Excel.XlFileFormat.xlExcel8);

Open in new window

Runs fine?
0
 

Author Comment

by:tentavarious
ID: 35486194
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?

0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35486597
Ok I will check your code...
0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 35486717
Hello, please use this code to get the correct file name path:
    Function GetFileNamePath() As String
        Using mySaveFileDialog As New System.Windows.Forms.SaveFileDialog()
            mySaveFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
            mySaveFileDialog.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
            mySaveFileDialog.AddExtension = False
            If (mySaveFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                Return System.IO.Path.ChangeExtension(mySaveFileDialog.FileName, "xls")
            Else
                Return String.Empty
            End If
        End Using
    End Function

    Sub ExportToExcel()
        Dim file_path As String = GetFileNamePath()
        If String.IsNullOrEmpty(file_path) Then Exit Sub

        excel_sequence_export(file_path)
    End Sub

Open in new window


Notice how the ExportToExcel() method use it.
0
 

Author Comment

by:tentavarious
ID: 35487029
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35487118
Well, that things happens...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question