I am using VB.Net (2.0) to automate MS-Excel.

In the Class, I decla
Private oXL As Microsoft.Office.Interop.Excel.Application = Nothing
Private oWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
Private oWS As Microsoft.Office.Interop.Excel.Worksheet = Nothing


I open the WorkBook when I get an instance of this class:

Public Sub New(Optional ByVal SourceFile As String = "", Optional ByVal
ActivateWorkSheetNamed As String = "", Optional ByVal SetVisible As Boolean =
False)

oXL = New Microsoft.Office.Interop.Excel.Application
oXL.DisplayAlerts = SetVisible
oXL.Visible = SetVisible

If (SourceFile.Trim.Length 0) Then
If File.Exists(SourceFile) Then
oWB = oXL.Workbooks.Open(SourceFile, False, False)
Else
If oXL.Workbooks.Count < 1 Then
oWB = oXL.Workbooks.Add()
ElseIf oXL.Workbooks.Count = 1 Then
oWB = oXL.Workbooks(1)
End If
oXL.SaveWorkspace(SourceFile)
End If
End If

If (oXL.Workbooks.Count < 1) Then
oXL.Workbooks.Add()
oWB = oXL.Workbooks(1)
End If

oWS = oWB.ActiveSheet

If ActivateWorkSheetNamed.Trim.Length 0 Then
Dim I As Integer = 1
For I = 1 To oWB.Worksheets.Count
If oWB.Worksheets(I).Name.ToString.ToUpper =
ActivateWorkSheetNamed.ToUpper Then
oWS = oWB.Worksheets(ActivateWorkSheetNamed)
End If
Next
End If
End Sub

I am getting the above error when calling the following code from my
application, at the line with "-":

Public Function WorkBookExists(ByVal WorkBookName As String) As Boolean
Dim I As Integer = 0
WorkBookExists = False
- For Each oWB In oXL.Workbooks
If (WorkBookName.Trim.ToUpper = oWB.Name.Trim.ToUpper) Then
WorkBookExists = True
Exit For
End If
Next
End Function

Any help would be greatly appreciated.

Hey Miles,
Would that line be giving you an error just because it needs to be -
For Each Micorsoft.Office.Interop.Excel.Workbook oWB In oXL.Workbooks
<< ?


Let me know if you succeed in getting some data into the worksheet. I am
trying to do something similar using C# and while I can open the workbook and
the corresponding worksheet, I get an error when I try to access the cells in
the worksheet...
I tried it in various ways and let me show you the two ways I think should
work very smoothly but .... don't!

This is exactly how the MSDN walkthrough in the article
'http://support.microsoft.com/kb/302084/EN-US/' tells you to do it and yet I
get the exception

Excel.Range range;
Object missingValue = Missing.Value;
---range = ActiveSheet.get_Range("A1", missingValue); <<-----
range.Value2 = data;


Heres another way that the cells should be 'theoretically' able to be
accessed but.... again .... don't!

--- ActiveSheet.Cells[5,5] = data; <<----


Both the marked lines give the same Exception. Would be thankful if any1 can
let me know what the exception is about and what is a solution for it.

Thanks.

- Tanmay


"Miles" wrote:

I am using VB.Net (2.0) to automate MS-Excel.

In the Class, I decla
Private oXL As Microsoft.Office.Interop.Excel.Application = Nothing
Private oWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
Private oWS As Microsoft.Office.Interop.Excel.Worksheet = Nothing


I open the WorkBook when I get an instance of this class:

Public Sub New(Optional ByVal SourceFile As String = "", Optional ByVal
ActivateWorkSheetNamed As String = "", Optional ByVal SetVisible As Boolean =
False)

oXL = New Microsoft.Office.Interop.Excel.Application
oXL.DisplayAlerts = SetVisible
oXL.Visible = SetVisible

If (SourceFile.Trim.Length 0) Then
If File.Exists(SourceFile) Then
oWB = oXL.Workbooks.Open(SourceFile, False, False)
Else
If oXL.Workbooks.Count < 1 Then
oWB = oXL.Workbooks.Add()
ElseIf oXL.Workbooks.Count = 1 Then
oWB = oXL.Workbooks(1)
End If
oXL.SaveWorkspace(SourceFile)
End If
End If

If (oXL.Workbooks.Count < 1) Then
oXL.Workbooks.Add()
oWB = oXL.Workbooks(1)
End If

oWS = oWB.ActiveSheet

If ActivateWorkSheetNamed.Trim.Length 0 Then
Dim I As Integer = 1
For I = 1 To oWB.Worksheets.Count
If oWB.Worksheets(I).Name.ToString.ToUpper =
ActivateWorkSheetNamed.ToUpper Then
oWS = oWB.Worksheets(ActivateWorkSheetNamed)
End If
Next
End If
End Sub

I am getting the above error when calling the following code from my
application, at the line with "-":

Public Function WorkBookExists(ByVal WorkBookName As String) As Boolean
Dim I As Integer = 0
WorkBookExists = False
- For Each oWB In oXL.Workbooks
If (WorkBookName.Trim.ToUpper = oWB.Name.Trim.ToUpper) Then
WorkBookExists = True
Exit For
End If
Next
End Function

Any help would be greatly appreciated.