• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Controlling excel within VB .Net

Hi

VB .Net windows project. I'm opening an Excel Workbook from my project. To check if the workbook is allready open I use the following Function.
The function is not working as it should - it always return a: "Workbook not open" which means that more than one copy off the workbook is opened.  What is wrong?

    Public Function WorkbookOpen(ByVal WorkbookName As String) As Boolean
        'Dim wb As xlapp.workbook
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        On Error Resume Next
        wb = xlApp.Workbooks("WorkbookName")
        On Error GoTo 0 'now reset error handling
        If wb Is Nothing Then MsgBox("Workbook is not open")
    End Function

Regards Steen
0
steensommer
Asked:
steensommer
  • 12
  • 5
  • 5
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
Hi steensommer,

From where is your xlApp coming?

If you want to reuse already opened Excel instance, you need to use the GetOBject method. See http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_20972338.html for an example.

Cheers!
0
 
steensommerAuthor Commented:
Hi

    Private xlApp As Microsoft.Office.Interop.Excel.Application

Steen
0
 
Éric MoreauSenior .Net ConsultantCommented:
steensommer,

How do you link your xlApp to Excel? You need a New, CreateObject, or GetObject somewhere. See the example from the link already given.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
steensommerAuthor Commented:
I will look at the examples!
0
 
steensommerAuthor Commented:
Hi
I have tried to implement some of the surgested but it still isn't correct. I get the message: " Can't create Active-X component:

    Private Sub ToolStripMenuItem1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem1.Click
        Dim oPath As String = "\\server\faelles\Index data\Observationsskemaer\"
        Dim owb As String = Cpr & ".obs"

        If Cpr <> "" Then

            If Dir(oPath & owb) <> "" Then
                Try
                    'Undersøger om Excel er startet
                    xlApp = GetObject(, "Excel.Application")

                    For Each xlBook In xlApp.Workbooks

                        If xlBook.Name <> owb Then

                            xlApp = CreateObject("Excel.Application")
                            With xlApp
                                .Workbooks.Open(oPath & owb)
                                .Visible = True
                            End With

                        Else
                            xlApp.Workbooks(owb).Activate()

                        End If

                    Next

                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try


            End If

        Else

            Dim Msg, Style, Title, Response
            Msg = "Skal der oprettes et nyt observationsskema?"    ' Define message.
            Style = vbYesNo + vbDefaultButton2     ' Define buttons.
            Title = "Meddelelsesbox"    ' Define title.
            Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then
                With xlApp
                    .Workbooks.Open("\\server\faelles\Index\dokumenter\Observationsskabelon\Patient obs.xls")
                    .Visible = True
                    Dim WB = xlApp.ActiveWorkbook

                    With WB.Worksheets("Ordination")
                        .Range("K2").Value = Navn
                        .Range("F2").Value = Cpr
                        .Range("H2").Value = HCV
                    End With

                    xlBook.SaveAs(Filename:=oPath & Cpr & ".obs")
                End With

            End If

        End If
        xlBook = Nothing
        xlApp = Nothing


    End Sub
0
 
Éric MoreauSenior .Net ConsultantCommented:
which version of Excel do you have on your PC?

have you referenced Excel in your project?

also have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;301982
0
 
steensommerAuthor Commented:
Hi
The newest version - excel 10?
and yes I have made the reference!
Steen
0
 
Éric MoreauSenior .Net ConsultantCommented:
I suspect that your erro occurs on this lne:
xlApp = GetObject(, "Excel.Application")

and I see by your comments that your OS and probably your Office is not English. It may explain your problem.

Are you able to have this line without compile error (like the example of the last link provided)?
 Dim oXL As Excel.Application
0
 
steensommerAuthor Commented:
You could be wright - the OS is Danish.
An  error occurs at the mensioned line and when writing your last line I get an error:
Type 'Excel.Application' is not defined.

...and what ca I do about it?

Steen
0
 
steensommerAuthor Commented:
....hm harder than expected
0
 
steensommerAuthor Commented:
Using the following line in my project - I can Dim oXL as Excel.Application without problem.
Imports Microsoft.Office.Interop


... but I still get an error in
 xlApp = GetObject(, "Excel.Application"): Can't create Active-X component

0
 
Éric MoreauSenior .Net ConsultantCommented:
The error lies there but I don't know how to fix it. Any other Danish people could help you?
0
 
MageDribbleCommented:
This code works for me to generate the Excel object.  Just copy and paste the xlApp part and you should be all set.

    Private Sub ExportExcel()
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application
            Dim dc As DataColumn
           'adoDataset = my global dataset
            Dim dt As DataTable = adoDataset.Tables(0)
            Dim iCols As Int32 = 0
            xlApp.Workbooks.Add()
            xlApp.Visible = True
            'Add the column headings for the Customers
            For Each dc In dt.Columns
                xlApp.Range("A1").Offset(0, iCols).Value = dc.ColumnName
                iCols += 1
            Next
            'Add the data
            Dim iRows As Int32
            For iRows = 0 To dt.Rows.Count - 1
                xlApp.Range("A2").Offset(iRows).Resize(1, iCols).Value = dt.Rows(iRows).ItemArray()
            Next
            xlApp.Rows("1:1").Select()
            xlApp.Selection.Font.Bold = True
            xlApp.Selection.Interior.ColorIndex = 15
            xlApp.Cells.Select()
            xlApp.Cells.EntireColumn.AutoFit()
            xlApp.Range("A1").Select()
            xlApp = Nothing
        Catch ex As Exception
            stspanel2.Text = ex.Message
            Exit Sub
        End Try
    End Sub
0
 
MageDribbleCommented:
the part to create the object is just this one piece of code:


Dim xlApp As New Microsoft.Office.Interop.Excel.Application
0
 
steensommerAuthor Commented:
Hi

I have tried the code and the Active-X Component error disappeared.
.....but my problem is not that I can't open or create an Excel workbook!
The problem is that I am trying to detect weather or not a given workbook is opened and if open the workbook should be activated - this code (workes fine i VBA for Excel) just doesn't work!
Steen
0
 
MageDribbleCommented:
open the workbook with this instance of excel and try to write to it.  if you get an error it is already open.  if it is already open then you can use windows API to make it the active window.
0
 
steensommerAuthor Commented:
Maybe you have some code to use???
:0)
Steen
0
 
MageDribbleCommented:
i don't have the code off the top of my head.  I'll have to dig into some old code.  I did something similar to this - not exact but similar.  If you don't create/find a solution for this then I'll look for it.
0
 
steensommerAuthor Commented:
Are you sure that you will get an error - The workbook is Shared!
0
 
MageDribbleCommented:
if the file is already open then you will only be able to open as read-only so you'll error once you try to make changes and save.
0
 
steensommerAuthor Commented:
No in a SHARED workbook ihe file will be opened again and not as read-only!
0
 
steensommerAuthor Commented:
Hi
For some reason this works:

    Public Function WorkbookOpen(ByVal WorkbookName As String) As Boolean
        'Returns TRUE if the workbook is open
        WorkbookOpen = False
        On Error GoTo WorkBookNotOpen
        If Len(xlApp.Workbooks(WorkbookName).Name) > 0 Then
            WorkbookOpen = True
            Exit Function
        End If
WorkBookNotOpen:

    End Function

    Private Sub ToolStripMenuItem1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem1.Click
        Dim oPath As String = "\\server\faelles\Index data\Observationsskemaer\"
        Dim owb As String = Cpr & ".obs"

        If Cpr <> "" Then

            If Dir(oPath & owb) <> "" Then
                'Undersøger om Excel er startet
                On Error Resume Next
                'Dim xlApp As New Excel.Application
                xlApp = GetObject(, "Excel.Application")

                If Err.Number = 0 Then GoTo næste
                'Ellers starter vi excel
                xlApp = CreateObject("Excel.Application")

næste:

                If WorkbookOpen(owb) = False Then

                    With xlApp
                        .Workbooks.Open(oPath & owb)
                        .Visible = True
                    End With
                Else
                    'xlApp.Workbooks(owb).Activate()
                    MsgBox("Observationsskemaet er allerede opstartet")
                End If

            Else
                Dim xlApp As New Excel.Application

                Dim Msg, Style, Title, Response
                Msg = "Skal der oprettes et nyt observationsskema?"    ' Define message.
                Style = vbYesNo + vbDefaultButton2     ' Define buttons.
                Title = "Meddelelsesbox"    ' Define title.
                Response = MsgBox(Msg, Style, Title)
                If Response = vbYes Then
                    With xlApp
                        .Workbooks.Open("\\server\faelles\Index\dokumenter\Observationsskabelon\Patient obs.xls")
                        .Visible = True
                        Dim WB = xlApp.ActiveWorkbook

                        With WB.Worksheets("Ordination")
                            .Range("K2").Value = Navn
                            .Range("F2").Value = Cpr
                            .Range("H2").Value = HCV
                        End With

                        WB.SaveAs(Filename:=oPath & Cpr & ".obs")
                    End With

                End If

            End If
            xlBook = Nothing
            xlApp = Nothing

        End If
    End Sub

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 12
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now