Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi All,

I get error " could not decrypt file" when i use this code for import data from Excel sheet. The excel sheet have password protected. A simple Excel sheet which not have a password protected is import successful.
Try
            Dim ImportCon As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data source='" & txtFilePath.Text.Trim & "';Extended Properties=Excel 8.0")
            Dim ImportCmd As New OleDbCommand("select * from [sheet1$]", ImportCon)
            'Dim ImportCmd As New OleDbCommand("SELECT * INTO [MS Access;DATABASE=" & txtFilePath.Text & ";PWD=theagereport].[tblNEW] FROM [Sheet1$]", ImportCon)

            Dim ImportAdp As New OleDbDataAdapter(ImportCmd)
            ImportAdp.TableMappings.Add("Table", "InvoiceDue")
            Dim ImportDS As New DataSet
            ImportAdp.Fill(ImportDS)
            grdInvoiceDueData.DataSource = ImportDS.Tables(0)
        Catch ex As Exception
            KryptonMessageBox.Show("Import Error:-" + ex.Message, "Update Invoice Due", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try


Please help me.

Thanks in Advance.
Posted
Updated 10-Jun-11 0:03am
v2

If the OleDb classes won't work with password protected Excel files u can use the code for its solution.

Imports Excel = Microsoft.Office.Interop.Excel ' at the top of the module
' add refference to Ms excel 11.0 object libray
' add refference to Ms office 11.0 object libray

VB
Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

       Dim xlsApp As Excel.Application
       Dim xlsbook As Excel.Workbook
       Dim xlsSheet As Excel.Worksheet
       Dim m_Rw As Integer
       Dim i As Integer
       Dim j As Integer

       xlsApp = CreateObject("Excel.Application")
       xlsApp.Workbooks.Open("c:\Myfile.xls", , , ,My excelfile Password)
       xlsbook = xlsApp.ActiveWorkbook
       xlsSheet = xlsbook.Sheets(1)

       ' insert column headers...
       dataGridview1.ColumnCount = xlsSheet.UsedRange.Columns.Count
       dataGridview1.ColumnHeadersVisible = True
       For i = 0 To xlsSheet.UsedRange.Columns.Count - 1
           dataGridview1.Columns(i).Name = xlsSheet.Cells(1, i + 1).Value.ToString
       Next

       ' insert rows and data to its cells....
       Dim var1(xlsSheet.UsedRange.Columns.Count) As String ' array for consisting cell value
       m_Rw = 0
       For i = 2 To xlsSheet.UsedRange.Rows.Count - 1
           dataGridview1.Rows.Add()
           For j = 0 To xlsSheet.UsedRange.Columns.Count - 1

               If xlsSheet.Cells(i, j + 1).Value = Nothing Then
                   var1(j) = ""
               Else
                   var1(j) = xlsSheet.Cells(i, j + 1).Value.ToString
               End If

               dataGridview1.Item(j, m_Rw).Value = var1(j)

           Next j
           m_Rw = m_Rw + 1
       Next i

       xlsSheet = Nothing  'disconnect from the Worksheet
       xlsbook = Nothing
       xlsApp.Quit()       'Close (and disconnect from) Excel
       xlsApp = Nothing

       Me.Cursor = Cursors.Default

   End Sub


' please reply.Working or not, Thanks...
 
Share this answer
 
v2
Comments
raviprajapat 13-Jun-11 9:59am    
Working ok with Password protected Excel sheet but when retrieve data from excel error occur :-"Object Reference not set to an instance of an object "
For i = 0 To xlsSheet.UsedRange.Columns.Count - 1
dataGridview1.Columns(i).Name = xlsSheet.Cells(1, i + 1).Value.ToString
Next
It's simple. The OleDb classes won't work with password protected Excel files.

Your only workaround is spelled out here[^]. The code is in VB6, but it's translatable to VB.NET.

Also, the user is going to have to know the password to the file, otherwise this won't work.
 
Share this answer
 
v2
Comments
raviprajapat 8-Jun-11 17:18pm    
I tried this but there is new error occur :-"The Microsoft jet database engine cannot open the file. it is already opened exclusively by another user or you need permission to view its data"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900