?
Solved

Manipulating Excel from VB.Net

Posted on 2005-03-07
6
Medium Priority
?
227 Views
Last Modified: 2010-04-23
Hi

How can I do the following Excel operations from my VB.Net program ?

1. If a particular .xls file is already open by the user, then lock it so that for the duration of my program, the user cannot make ANY changes to the Excel workbook ?

2. Insert some values in an Excel workbook. Pls give example with any dummy values.

3. In a particular sheet in the workbook, find the last such row where any data has been entered in any of the columns. I need this so that I can insert data from my program in the NEXT row.

Bascially I am writing a program which is run once daily and which reads values from an Access database and appends them in an Excel workbook on a daily basis. Every day it has to add one row of data to the already existing values.

Thnx. I will appreciate any code samples or relevant links.


0
Comment
Question by:rajesh_khater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
bman9111 earned 1500 total points
ID: 13477506
not sure if this is a good start for u or if u need more

Imports Excel
Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim excelapp As Excel.Application
    Dim workbook As Excel.Workbook
    Dim worksheet As Excel.Worksheet
    Dim xlLast As Excel.Range
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(104, 96)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            excelapp = New Excel.Application
            workbook = excelapp.Workbooks.Open("C:\test.xls")
            worksheet = workbook.Worksheets(1)

            With worksheet
                xlLast = .Range("A65536").End(Excel.XlDirection.xlUp).Offset(1, 0)
            End With

            xlLast.Value = "jojkjk"

            With worksheet
                .Save()
                .Close()
            End With





        Catch er As Exception
            MsgBox(er.Message)
            excelapp.Workbooks.Close()
        Finally

        End Try

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        excelapp.Workbooks.Close()
    End Sub
End Class


make sure u add the reference of excel to ur project,

0
 
LVL 8

Expert Comment

by:bman9111
ID: 13481081
did this help????
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 13482920
This might help. But what abt point no 1?

1. If a particular .xls file is already open by the user, then lock it so that for the duration of my program, the user cannot make ANY changes to the Excel workbook ?

0
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.

 
LVL 8

Expert Comment

by:bman9111
ID: 13482955
if the file is already opened by someone else then u will not be able to do anthing with it,

however u can lock it if u are access it....

0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 13483129
What other approach can I take if the Excel file is already open ?

1. Can  I force the user to save and close ?
2. Can I save and close myself programmatically ?
3. Can i make the workbook / Excel app invisible so that the user cannot do anything with it ?

4. Also, if the workbook is not already open, but I open it under program control, how do I make sure that the user does not change anything in the workbook, till my program is over ?
0
 
LVL 8

Expert Comment

by:bman9111
ID: 13486264
1. Can  I force the user to save and close ?

Now does the user have to have access to this file. Maybe u can create a program that connects the the excel file instead of them going into it directly.

There is really no way of remotly closeing the file, unless u all have xp and then maybe u could terminate the excel.exe session.

Is this file in 1 location?

How many are touch this file and if someone has it open how do u know who it is??


2. Can I save and close myself programmatically ? yes the code I gave u will save and close the file. What I would try is open up the file in excel, and then run my program to see what happens. I will try to do the same.

3. Can i make the workbook / Excel app invisible so that the user cannot do anything with it ?
please clearify on this....


4. Also, if the workbook is not already open, but I open it under program control, how do I make sure that the user does not change anything in the workbook, till my program is over ?

go ahead and run my program sample and then try to open the same file by excel. It will do the read-only, notify error message box. so u will not have to worry about the other user touching it.


0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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