-
February 7th, 2011, 02:20 PM
#1
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!
-
February 7th, 2011, 07:05 PM
#2
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!
-
February 9th, 2011, 03:39 PM
#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!!!!
-
February 9th, 2011, 04:14 PM
#4
Re: Editing excel with VB
Sure. That's probably VB6 code. Download the samples from here and try them out in VS2010
-
February 10th, 2011, 05:16 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|