steensommer
asked on
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.E
On Error Resume Next
wb = xlApp.Workbooks("WorkbookN
On Error GoTo 0 'now reset error handling
If wb Is Nothing Then MsgBox("Workbook is not open")
End Function
Regards Steen
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.E
On Error Resume Next
wb = xlApp.Workbooks("WorkbookN
On Error GoTo 0 'now reset error handling
If wb Is Nothing Then MsgBox("Workbook is not open")
End Function
Regards Steen
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
How do you link your xlApp to Excel? You need a New, CreateObject, or GetObject somewhere. See the example from the link already given.
ASKER
I will look at the examples!
ASKER
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(B
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.Applic
With xlApp
.Workbooks.Open(oPath & owb)
.Visible = True
End With
Else
xlApp.Workbooks(owb).Activ
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\
.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:=oP
End With
End If
End If
xlBook = Nothing
xlApp = Nothing
End Sub
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(B
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.Applic
With xlApp
.Workbooks.Open(oPath & owb)
.Visible = True
End With
Else
xlApp.Workbooks(owb).Activ
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\
.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:=oP
End With
End If
End If
xlBook = Nothing
xlApp = Nothing
End Sub
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
have you referenced Excel in your project?
also have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;301982
ASKER
Hi
The newest version - excel 10?
and yes I have made the reference!
Steen
The newest version - excel 10?
and yes I have made the reference!
Steen
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
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
ASKER
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
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
ASKER
....hm harder than expected
ASKER
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
Imports Microsoft.Office.Interop
... but I still get an error in
xlApp = GetObject(, "Excel.Application"): Can't create Active-X component
The error lies there but I don't know how to fix it. Any other Danish people could help you?
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.E
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 += 1
Next
'Add the data
Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlApp.Range("A2").Offset(i
Next
xlApp.Rows("1:1").Select()
xlApp.Selection.Font.Bold = True
xlApp.Selection.Interior.C
xlApp.Cells.Select()
xlApp.Cells.EntireColumn.A
xlApp.Range("A1").Select()
xlApp = Nothing
Catch ex As Exception
stspanel2.Text = ex.Message
Exit Sub
End Try
End Sub
Private Sub ExportExcel()
Try
Dim xlApp As New Microsoft.Office.Interop.E
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 += 1
Next
'Add the data
Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlApp.Range("A2").Offset(i
Next
xlApp.Rows("1:1").Select()
xlApp.Selection.Font.Bold = True
xlApp.Selection.Interior.C
xlApp.Cells.Select()
xlApp.Cells.EntireColumn.A
xlApp.Range("A1").Select()
xlApp = Nothing
Catch ex As Exception
stspanel2.Text = ex.Message
Exit Sub
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
ASKER
Maybe you have some code to use???
:0)
Steen
:0)
Steen
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.
ASKER
Are you sure that you will get an error - The workbook is Shared!
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.
ASKER
No in a SHARED workbook ihe file will be opened again and not as read-only!
ASKER
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(Workbo
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Private Sub ToolStripMenuItem1_Click(B
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.Applic
næste:
If WorkbookOpen(owb) = False Then
With xlApp
.Workbooks.Open(oPath & owb)
.Visible = True
End With
Else
'xlApp.Workbooks(owb).Acti
MsgBox("Observationsskemae
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\
.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
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(Workbo
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Private Sub ToolStripMenuItem1_Click(B
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.Applic
næste:
If WorkbookOpen(owb) = False Then
With xlApp
.Workbooks.Open(oPath & owb)
.Visible = True
End With
Else
'xlApp.Workbooks(owb).Acti
MsgBox("Observationsskemae
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\
.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
ASKER
Private xlApp As Microsoft.Office.Interop.E
Steen