Editing excel with VB
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Editing excel with VB

  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Question Editing excel with VB

    Hi everyone,

    I am trying to write code that will open an excel worksheet that was picked by the user, pick out specific information, and output that information into a chart. So far I have the code to allow the user to choose the excel file they want to use:

    #Private Sub openButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles openButton.Click

    Dim obj As New Process

    OpenFileDialog1.ShowDialog()

    strFileName = OpenFileDialog1.FileName

    Process.Start(strFileName)

    End Sub

    Now I need help creating a code to control the excel sheet the user picked, and pick out a specific range of cells. I've heard using ADO is the best way, but I am new to Visual Basic and I don't really understand how to use ADO. Any help would be greatly appreciated!

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,981

    Re: Editing excel with VB

    We post code about that every few weeks. Try searching for EXCEL in this forum. I've posted a sample dozens of times.

    Welcome to the forums, btw!
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Feb 2011
    Posts
    3

    Re: Editing excel with VB

    Thanks for the welcome dglienna! I took your advice and searched through the forums, and found a code that I think I can manipulate to work how I want it. However, there are a few errors that I can't resolve from the original code. Could my errors be happening because I think this code was generated in older versions of Visual Studio (I am currently using Visual Studio 2010 Express)?

    Code:
    Option Explicit On
    
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Text
    Imports Excel = WindowsApplication1.Excel
    Imports System.Windows.Forms
    
    Public Class Form1
    
        Dim sqlconn As SqlConnection
        Dim sqlcmd As SqlCommand
        Dim DA As SqlDataAdapter
        Dim DR As SqlDataReader
        Dim DS As System.Data.DataSet
        Dim DT As System.Data.DataTable
    
        'common variable
        Dim connstr As String
        Dim excelPathName As String = String.Empty
        Dim bolUpdate As Boolean = False   ' false = update not done
        Dim txtExcelFolderName As Object
    
        Private Sub openButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles openButton.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  (*.xls)| *.xls|All File(*.xls)|.xls"
    
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    excelPathName = (CType(.FileName, String))
                End If
            End With
    
    
        End Sub
    
        Private Sub exitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitButton.Click
            Me.Close()
        End Sub
    
        Private Sub dataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataButton.Click
            'test open up excel spreadsheet
    
            Dim objExcel As New Excel.Application   'error says "type Excel.Application is not defined"
            Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName)
            Dim objSheet As Excel.Worksheet = objBook.Worksheets(1)
            objExcel.Visible = True
    
    
            objExcel = CType(CreateObject("Excel.Application"), Excel.Application)
            objBook = CType(objExcel.Workbooks.Open(excelPathName), Excel.Workbook)
            objBook.Activate() 'error says "Activate is not a member of WindowsApplication1.Excel.Workbook"
    
            objSheet = CType(objExcel.Worksheets(1), Excel.Worksheet)
            objExcel.Visible = True
            objSheet.Activate() 'error says "Activate is not a member of WindowsApplication1.Excel.Workbook"
    
            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
            Dim strCell7 As String
            Dim strCell8 As String
            Dim strCell9 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)
                        strCell7 = CType(objSheet.Cells(excelRow, 7).value, String)
                        strCell8 = CType(objSheet.Cells(excelRow, 8).value, String)
                        strCell9 = CType(objSheet.Cells(excelRow, 9).value, String)
    
                        .Rows.Add(New String() {strCell1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9})
    
    
                        DGVRow += 1
                        excelRow += 1
    
                        strCell1 = ""
                        strCell2 = ""
                        strCell3 = ""
                        strCell4 = ""
                        strCell5 = ""
                        strCell6 = ""
                        strCell7 = ""
                        strCell8 = ""
                        strCell9 = ""
    
                    End With
    
                Loop
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            Finally
    
                objBook.Close()
                objExcel.Quit()
            End Try
    
        End Sub
    End Class
    My errors
    1) Dim objExcel As New Excel.Application 'error says "type Excel.Application is not defined"
    ^ this error appears, and recommends I change the Excel.Application to other things, but if I change it, errors are created in other parts of the method. Any ideas why this is happening?

    2) objBook.Activate() 'error says "Activate is not a member of WindowsApplication1.Excel.Workbook"

    Thank you all for your help!!!!

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,981

    Re: Editing excel with VB

    Sure. That's probably VB6 code. Download the samples from here and try them out in VS2010
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jan 2002
    Posts
    195

    Re: Editing excel with VB

    I posted an exsample on this in the .net forum last week. Very basic snippets with detaled notes that you could easyly modify to your needs.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center