3 Replies - 8590 Views - Last Post: 04 December 2011 - 06:05 PM Rate Topic: -----

#1 sigridish  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 09-November 11

export excel data to datagrid using openfiledialog

Posted 01 December 2011 - 06:36 PM

hi all! i have here a code for browse button

    Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click
     
    'prompt user to select Excel name and folder path
     
    Dim openFileDialog1 As System.Windows.Forms.OpenFileDialog
    openFileDialog1 = New System.Windows.Forms.OpenFileDialog
     
    With openFileDialog1
    .Title = "Excel Spreadsheet"
    .FileName = ""
    .DefaultExt = ".xls"
    .AddExtension = True
    .Filter = "Excel Worksheets|*.xls; *.xlsx"
     
    If .ShowDialog = Windows.Forms.DialogResult.OK Then
    excelPathName = (CType(.FileName, String))
     
    If (excelPathName.Length) <> 0 Then
    Me.TextBox1.Text = excelPathName
    Else
     
    End If
    End If
    End With
     
    End Sub


and for view button
    'test open up excel spreadsheet
    Dim objExcel As New Excel.Application
    Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName)
    Dim objSheet As Excel.Worksheet = objBook.Worksheets(1)
    objExcel.Visible = True
    Dim bolFlag As Boolean = True
    Dim excelRow As Integer = 7
    Dim excelCol As Integer = 1
    Dim DGVRow As Integer = 1
    Dim strCell1 As String
    Dim strCell2 As String
    Dim strCell3 As String
    Dim strCell4 As String
    Dim strCell5 As String
    Dim strCell6 As String
    Try
    Do While bolFlag = True
     
    If Convert.ToString(objSheet.Cells(excelRow, 1).value) = "" Then
    bolFlag = False
    Exit Do
    End If
     
    With DataGridView1
     
    strCell1 = CType(objSheet.Cells(excelRow, 1).value, String)
    strCell2 = CType(objSheet.Cells(excelRow, 2).value, String)
    strCell3 = CType(objSheet.Cells(excelRow, 3).value, String)
    strCell4 = CType(objSheet.Cells(excelRow, 4).value, String)
    strCell5 = CType(objSheet.Cells(excelRow, 5).value, String)
    strCell6 = CType(objSheet.Cells(excelRow, 6).value, String)
     
    DGVRow += 1
    excelRow += 1
     
    strCell1 = ""
    strCell2 = ""
    strCell3 = ""
    strCell4 = ""
    strCell5 = ""
    strCell6 = ""
    End With
    Loop
    Catch ex As Exception
    MessageBox.Show(ex.Message)
    Finally
    objBook.Close()
    objExcel.Quit()
    End Try
    End Sub




everytime i hit the view button a message box comes out saying exception HResult 0x800A01A8 also the excel file opens. i dont want the excel file to be opened please help

Is This A Good Question/Topic? 0
  • +

Replies To: export excel data to datagrid using openfiledialog

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: export excel data to datagrid using openfiledialog

Posted 01 December 2011 - 07:07 PM

Hi sigridish! Here is where you're setting the workbook to open it:
Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName)  
Dim objSheet As Excel.Worksheet = objBook.Worksheets(1)  


And the reason it is visible to you is this:
objExcel.Visible = True

Was This Post Helpful? 0
  • +
  • -

#3 _HAWK_  Icon User is online

  • Master(Of Foo)
  • member icon

Reputation: 1064
  • View blog
  • Posts: 4,167
  • Joined: 02-July 08

Re: export excel data to datagrid using openfiledialog

Posted 01 December 2011 - 07:21 PM

(CType(.FileName, String)) cast not required.

What line is the code failing on? Set a breakpoint and debug it.
Was This Post Helpful? 0
  • +
  • -

#4 sigridish  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 09-November 11

Re: export excel data to datagrid using openfiledialog

Posted 04 December 2011 - 06:05 PM

hi JimmyBo!
this is my new code

 Dim fName As String = ""
        OpenFileDialog1.InitialDirectory = "C:\Documents and Settings\t-rpalberto\Desktop"
        OpenFileDialog1.Filter = "Excel Worksheets|*.xls; *.xlsx"
        OpenFileDialog1.FilterIndex = 2
        OpenFileDialog1.RestoreDirectory = True
        If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            fName = OpenFileDialog1.FileName
        End If
        Me.TextBox1.Text = fName
        Dim TextLine As String = ""
        DataGridView1.Columns.Add("Customer Name", "Customer Name")
        DataGridView1.Columns.Add("Sales Group", "Sales Group")
        DataGridView1.Columns.Add("Customer Type", "Customer Type")
        DataGridView1.Columns.Add("Type Of Industry", "Type Of Industry")
        DataGridView1.Columns.Add("RM", "RM")
        DataGridView1.Columns.Add("Senior RM", "Senior RM")
        If System.IO.File.Exists(fName) = True Then
            Dim objReader As New System.IO.StreamReader(fName)
            Do While objReader.Peek() <> -1
                TextLine = objReader.ReadLine()
                Me.DataGridView1.Rows.Add()
            Loop
        Else

            MsgBox("File Does Not Exist")
        End If


its working but my excel file has 5 sheets. and when i open my file i want the name of the sheets to be displayed in my combo box. can you help me with that? as of now, whats happening is that i created a dummy excel file with only one sheet. and also there is data displayed in my datagrid. can you guys help me with that?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1