.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks .net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
Go Back  Xtreme Visual Basic Talk > > > .net noob - help opening multiple workbooks


Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2008, 03:50 AM
sarahemmm sarahemmm is offline
Newcomer
 
Join Date: Jul 2008
Posts: 6
Unhappy .net noob - help opening multiple workbooks


I need to extract data from a workbook and insert parts of it to one or more different workbooks.

I can open the first workbook just fine, but I get a cast error when I try to open the next one. The function I have is this
Code:
Public Function OpenExcel(ByVal strFilename As String, _
                            ByRef xlsApp As Excel.ApplicationClass, _
                            ByRef wbkApp As Excel.Workbook, _
                            ByRef shtApp As Excel.Worksheet, _
                            ByVal booVisible As Boolean) As Boolean

    Dim s As String

    If xlsApp Is Nothing Then
      xlsApp = New Excel.Application
        If xlsApp Is Nothing Then
          MsgBox("Unable to start Excel")
          Exit Function
        End If
    End If
    If wbkApp Is Nothing Then
      s = Dir(strFilename)
      If s = "" Then
        wbkApp = xlsApp.Workbooks.Add
        wbkApp.SaveAs(strFilename)
      Else
        wbkApp = xlsApp.Workbooks.Open(strFilename)
      End If
      If wbkApp Is Nothing Then
        MsgBox("Unable to open " & strFilename)
        Exit Function
      End If
    End If
    If shtApp Is Nothing Then
      shtApp = wbkApp.ActiveSheet
    End If
    If shtApp Is Nothing Then
      MsgBox("No worksheet found in " & strFilename)
      Exit Function
    End If
    xlsApp.Visible = booVisible

    OpenExcel = True

  End Function
and it is called like this
Code:
	Public filInputFilename As String
	Public xlsInput As New Excel.ApplicationClass
	Public wbkInput As Excel.Workbook
	Public shtInput As Excel.Worksheet

	Public filAnn As String = "Temp Ann.xls"
	Public xlsAnn As Excel.ApplicationClass
	Public wbkAnn As Excel.WorkbookClass
	Public shtAnn As Excel.WorksheetClass
	Public filEar As String = "Temp Ear.xls"
	Public xlsEar As Excel.ApplicationClass
	Public wbkEar As Excel.WorkbookClass
	Public shtEar As Excel.WorksheetClass

    If Not OpenExcel(filInputFilename, xlsInput, wbkInput, shtInput, False) Then
      Exit Sub
    End If
    ' wbkInput is now open
    If Not OpenExcel(TempPath & filAnn, xlsAnn, wbkAnn, shtAnn, False) Then
    ' get cast error on return from function:
    ' An unhandled exception of type 'System.InvalidCastException' occurred in Ann.exe
    '
    ' Additional information: Specified cast is not valid.
      Exit Sub
    End If
    If Not OpenExcel(filEar, xlsEar, wbkEar, shtEar, False) Then
    	Exit Sub
    End If
This is my first attempt with .net (2003, in case that helps) - what have I missed?
Reply With Quote
  #2  
Old 07-03-2008, 06:00 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi sarahemmm,

Welcome to the forum...

That's a pretty good first try out, very nicely structured. But I don't think you want to be opening a new Excel Application instance every time you open a new Workbook, do you? If you do, then I suggest that your OpenExcel method also close down this instance (call xlsApp.Quit) just before it OpenExcel exits.

The other way to go would be to maintain an Excel Application instance the entire time your Application is running, and pass that reference into your OpenExcel method. This way, when you call 'OpenExcel' the 2nd time you are still using the same Excel.Application intance.

But you don't necessarily need the OpenExcel method as a wrapper at all. It's up to you.

Since you are new to Excel and .NET, I would have a look at the Automating Office Programs with VB.NET tutorial. It covers the basics on how to open your new Excel.Application instance and how to shut it down properly.

I hope this helps...
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 07-04-2008, 03:42 AM
sarahemmm sarahemmm is offline
Newcomer
 
Join Date: Jul 2008
Posts: 6
Default

Multiple instances of Excel:
I did wonder about that, and it is next on my list of actions to try.

Actually, I have managed to solve my problem - I had declared the second and third workbooks and worksheets incorrectly. When I changed the declarations to workbook and worksheet, it ran okay (but what are workbookclass and worksheetclass ???).

Thanks for your input - I'm a noob on .net, but not a newbie programmer

Sarah
Reply With Quote
  #4  
Old 07-04-2008, 05:12 AM
sarahemmm sarahemmm is offline
Newcomer
 
Join Date: Jul 2008
Posts: 6
Question More grief

Gah! I spoke too soon...

I can now open the first workbook wbkInput and worksheet shtInput, then create the second workbook wbkAnn. The second worksheet shtAnn is created as a System.__ComObject, which is the same as shtInput, but I cannot write data to it:
Code:
    Try
      booFoundErrors = False
      intAnnRow = 1
      intEarRow = 1
      For i = 2 To shtInput.UsedRange.Rows.Count
        rngInputRow = shtInput.Rows(i)
        strInfoType = CStr(rngInputRow.Cells(1, 5).value)
        strTInfoType = CStr(rngInputRow.Cells(1, 6).value)
        dtStartDate = CDate(rngInputRow.Cells(1, 7).value)
        decAmount = CDec(rngInputRow.Cells(1, 13).value)
        decNumber = CDec(rngInputRow.Cells(1, 15).value)
        If strInfoType = "0008" Then
          MsgBox(shtAnn.Cells(intAnnRow, 1).GetType.ToString)
          shtAnn.Cells(intAnnRow, 2).value = dtStartDate
          shtAnn.Cells(intAnnRow, 3).value = decAmount
          shtAnn.Cells(intAnnRow, 4).value = decNumber
          intAnnRow = intAnnRow + 1
Code runs fine up to the MsgBox line. The msgbox tells me that cell(1,1) is a System.__ComObject. It runs through the next lines without error, but when I investigate the spreadsheet (which is saved on exit), it is empty.

The IDE offers only .GetType as an entry after shtAnn.Cells(intAnnRow, 2) - should I use this? If so, how?

Do I have to convert the data back into a range type or something?
Reply With Quote
  #5  
Old 07-04-2008, 05:19 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Sarah,

Oh, no I didn't mean to imply "noob" to programming in general, honest. But .NET and Excel (or .NET with Microsoft Office programs in general) is definitely a new environment, which is why I recommended the tutorial.

I'm glad you got it going, I didn't notice the declarations. Yes, you are using 'ApplicationClass', 'WorkbookClass', and 'WorksheetClass' in your code. In theory, you should be using 'Application', 'Worksheet', and 'Workbook' instead. I realize that 'Application', 'Worksheet', and 'Workbook' are all, officially, interfaces and so using code like:

Code:
Dim xlApp = New Application()
makes no sense if Application is an interface... However, the Primary Interop Assembly (PIA) for Excel puts in some extra code that allows this "interface" to be created. What is created is actually an ApplicationClass, which you are using via your Application interface variable.

In short, just use 'Application', 'Worksheet', and 'Workbook' and don't directly create or declare 'ApplicationClass', 'WorkbookClass', and 'WorksheetClass' types.

There is a discussion of this here:

Don't use ApplicationClass (unless you have to)

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks .net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks .net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
 
.net noob - help opening multiple workbooks
.net noob - help opening multiple workbooks
 
-->