[nighttrain]

Hello guys,

Actually i am developing programs which are manipulating on Excel. Until today i was working with only 1 excel file - i mean filling sheet from database and do some calculating, but only on 1 excel file. Today i need to manipulate on 2/3 excel files and i don't know how should i program my template which i am using it looks like that:

How should i implement another excel woorkbook and work with two woorkboks?

Imports Excel = Microsoft.Office.Interop.Excel

Module Main

    'Create Excel objects
    Public objApp As New Excel.Application
    Public objWb As Excel.Workbooks = objApp.Workbooks
    Public objXlApp As Excel.Application = objWb.Application

    'Work directory path
    Public WorkDirectory = "D:\Files\Reports\"

    Sub Main()

Try

            With objXlApp

                'Open main workbook
                .Workbooks.Open(Filename:=WorkDirectory & "Data.xls")

                'Set Visible/Screen updating
                .Application.DisplayAlerts = False
                .Application.Visible = True
                .Application.ScreenUpdating = True

                .Sheets("Data").Activate()
                'Delete previous data
                .Cells.Select()
                .Range("A1").Value = "ID"

'another operations...
'another operations...
'.....................

End With

        Catch ex As Exception
            Call ex
        Finally

            'Realase all excel objects
                NAR(objWb)
                NAR(objApp)
                objXlApp.Quit()
                NAR(objXlApp)
                GC.Collect()
                GC.WaitForPendingFinalizers()
        End Try
    End Sub

End Module

This post has been edited by nighttrain: 16 August 2012 - 04:57 AM

[Bort]

All you need to do is duplicate these lines:

'Create Excel objects
Public objApp As New Excel.Application
Public objWb As Excel.Workbooks = objApp.Workbooks
Public objXlApp As Excel.Application = objWb.Application

So you have:

'Create Excel objects
Public objApp As New Excel.Application
Public objWb As Excel.Workbooks = objApp.Workbooks
Public objXlApp As Excel.Application = objWb.Application

Public objApp2 As New Excel.Application
Public objWb2 As Excel.Workbooks = objApp2.Workbooks
Public objXlApp2 As Excel.Application = objWb2.Application

Public objApp3 As New Excel.Application
Public objWb3 As Excel.Workbooks = objApp3.Workbooks
Public objXlApp3 As Excel.Application = objWb3.Application

Then just do with the second and third instances the same thing you did with the first.

[Ionut]

I'm not quite fond of Bort's solution, though he has a point.
Don't duplicate

'Create Excel objects
Public objApp As New Excel.Application
Public objWb As Excel.Workbooks = objApp.Workbooks
Public objXlApp As Excel.Application = objWb.Application

What if you have to work with 10 workbooks and you create an new Excel application for each one of it? Then you will have 10 Excel processes that can reside into the memory until you reboot your computer. It's not very nice.

An Excel application is a collection of workbooks. By default, Excel is an MDI application, this means that each workbook you open attaches to the existing process.Programmatically, when you create an Excel application you create a new process, so it's your duty to make sure you keep everything clean in the client's system.
When working with Excel objects, I would suggest you to assign every object you intend to use to a variable.
For example

With objXlApp  
  'Open main workbook  
  .Workbooks.Open(Filename:=WorkDirectory & "Data.xls")  

Here you would better use

With objXlApp  
  'Open main workbook  
  Dim wbData as Excel.Workbook = .Workbooks.Open(Filename:=WorkDirectory & "Data.xls")  
  'do the job with wbData 
  Dim wbAnotherDataFile as Excel.Workbook = .Workbooks.Open(Filename:=WorkDirectory&"AnotherData.xls")
  'do the job with wbAnotherDataFile
'In the end release wbAnotherDataFile and wbData

Now looking to your code, I don't understand why do you need this line

Public objXlApp As Excel.Application = objWb.Application 

objApp is the parent of the objWb. And objWb.Application points to the parent of the objWb which is objApp.

To summarize, create a new Excel application and create there how many workbooks you feel like. But remember, you are using unmanaged code(COM component) in a managed code environment. This means you have to be careful how you use all those objects and make sure you don't loose them out of your sight. GarbageCollector will be very lazy in this case and you might end up with a freezing application.

This post has been edited by Ionut: 16 August 2012 - 01:47 PM

[nighttrain]

Hello Ionut, i think you are right its beeter to have just 1 instance of Excel App because of memory usage. So i maked 1 instance and then set workbooks to variavles so i can use it on operations like this, is that what you proposed me?: (correct me if i am wrong please):

Imports Excel = Microsoft.Office.Interop.Excel

Module Main

    'Create Excel objects
    Public objApp As New Excel.Application
    Public objWb As Excel.Workbooks = objApp.Workbooks
    Public objXlApp As Excel.Application = objWb.Application

    Public WorkDirectory = "C:\Users\user\Documents\Visual Studio 2005\Projects\Working with Excels\"

    Sub Main()
        Try
            With objXlApp

                objXlApp.Visible = True
                objXlApp.ScreenUpdating = True

                Dim wbMain As Excel.Workbook = .Workbooks.Open(Filename:=WorkDirectory & "Main.xls")
                Dim wbSecond As Excel.Workbook = .Workbooks.Open(Filename:=WorkDirectory & "second.xls")

                wbMain.Sheets("Sheet2").Range("B1").Value = wbSecond.Sheets("Sheet2").Range("B1").Value
                wbMain.Sheets("Sheet2").Range("C1").Value = wbSecond.Sheets("Sheet2").Range("C1").Value

                wbMain.Sheets("Sheet3").Range("B1").Value = wbSecond.Sheets("Sheet3").Range("B1").Value
                wbMain.Sheets("Sheet3").Range("C1").Value = wbSecond.Sheets("Sheet3").Range("C1").Value

                'wbMain.Activate() 'don't need to activate sheet to do any of operations (only for view)
                wbSecond.Sheets("Sheet1").Range("B1").Value = wbMain.Sheets("Sheet1").Range("B1").Value
                wbSecond.Sheets("Sheet1").Range("C1").Value = wbMain.Sheets("Sheet1").Range("C1").Value

                wbMain.Save()
                wbMain.Close(True)
                wbSecond.Save()
                wbSecond.Close(True)

            End With

        Catch ex As Exception
            Console.WriteLine("Error occurs: " & ex.ToString)
        Finally

            NAR(objWb)
            NAR(objApp)
            objXlApp.Quit()
            NAR(objXlApp)
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try

    End Sub

End Module

But you could see that operations will be "long" (e.g):

wbMain.Sheets("Sheet3").Range("B1").Value = wbSecond.Sheets("Sheet3").Range("B1").Value

do you know better way to handle this? And all what i have been wrote is good way to operate on excel?

P.S I dont need to always activate workbooks/sheets to operate (e.g) copying data betwen workbooks or something, right?

This post has been edited by nighttrain: 17 August 2012 - 01:02 AM

[Ionut]

Quote

But you could see that operations will be "long" (e.g):

For this kind of operations, there is no other way. The same thing it's done in VBA, it's an Excel limitation.
If you have to copy a range of cells(as I see in your code), you can assign ranges as

'Attention! Untested code, but you can find references on the internet on this subject, if it doesn't work
wbMain.Sheets("Sheet2").Range("B1:C1") = wbSecond.Sheets("Sheet2").Range("B1:C1")

And no, you don't have to activate a workbook to work with it.

Now, another hints:

objXlApp.Visible = True  
objXlApp.ScreenUpdating = True  

For optimization, this should be set to false. If your Excel application is visible, you can set to false ScreenUpdating. But Excel application can be invisible for the user until all the calculation is done and you have to show him the result(if you have to).
Another hint, don't make calculation directly with Excel cells, like

wbMain.Sheets("Sheet2").Range("B1").Value = wbMain.Sheets("Sheet2").Range("B1").Value + wbMain.Sheets("Sheet2").Range("C1").Value + other things

It's very slow. And if you have a range of 1000 of rows to do calculations on, you can go drink a coffee. A faster way it's to save to .NET objects(arrays or other variables), do all the stuff and then copy the result back.

The rest of the code seems good, but

Public objApp As New Excel.Application  
 Public objWb As Excel.Workbooks = objApp.Workbooks  
 Public objXlApp As Excel.Application = objWb.Application  

Try cleaning this piece of code as I said in my previous post.

[nighttrain]

So What is correct way to change above code to propere:

Public objApp As New Excel.Application  
 Public objWb As Excel.Workbooks = objApp.Workbooks  
 Public objXlApp As Excel.Application = objWb.Application  

[Ionut]

Public objApp As New Excel.Application  

And use everywhere in your code objApp instead of objXlApp

[nighttrain]

So the code should looks like this at now (correct if i am wrong):

Imports Excel = Microsoft.Office.Interop.Excel

Module Main

    'Create Excel objects
    Public objApp As New Excel.Application
    'Public objWb As Excel.Workbooks = objApp.Workbooks         -> no needed
    'Public objXlApp As Excel.Application = objWb.Application   -> no needed

    Public WorkDirectory = "C:\Users\user\Documents\Visual Studio 2005\Projects\Working with Excels\"

    Sub Main()
        Try
            With objApp

..........
..........
End With

        Catch ex As Exception
            Console.WriteLine("Error occurs: " & ex.ToString)
        Finally

            objApp.Quit()
            NAR(objApp)
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try

    End Sub

End Module

Another question is do i need NAR(objApp) or its enough to have objApp.Quit() ? - without NAR(objApp) its clearing EXCEL anyway from processes but mayby should i not delete this line anyway? What you think?

~NAR is my procedure which make me sure that all was clear from processes:

Module RealaseExcelObjects

    Public Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

End Module

This post has been edited by nighttrain: 17 August 2012 - 05:45 AM

[Ionut]

First question: yes, it is correct.
Second question : you do need NAR. The correct way to dispose COM objects is the way you do it in NAR. The application will close with Quit, but also you have to dispose the object. Otherwise, it might get stucked in memory and the process created by your application will hang until machine's reboot.

[nighttrain]

I got additional question here also, let's say i would like to create function outside of this module in seperated module
e.g:

Private Function getLastFilledColumnSource(parameter as worksheet)
Dim i As Integer
i = 1
While parameter.Cells(4, i).Value <> Nothing
  i = i + 1
Wend
getLastFilledColumnSource = i - 1
End Function

How to create correct constructor:

(parameter as worksheet)

and how from main module set to those function correctly sheet name?

[Ionut]

Firstly, don't forget to add a return type to your function.

Private Function getLastFilledColumnSource(parameter as worksheet) as Integer
Dim i As Integer
i = 1
While parameter.Cells(4, i).Value <> Nothing
  i = i + 1
Wend
getLastFilledColumnSource = i - 1
End Function

Now, in your case, you can use a module or a shared function. Even if the differences are not that important, I would go on the second option(details in this discussion. For example:

Class Utilities

Public SharedFunction getLastFilledColumnSource(parameter as Worksheet) as Integer
'code
End Function

End Class

How do you called that function? Being a shared function you don't have to instantiate an object of type Utilities

Public Sub DoWork
   Dim someWorksheetUsedHere as Worksheet

   'stuff
   Dim lastGoodColumn as Integer = Utilities.getLastFilledColumnSource(someWorksheetUsedHere)
   'stuff
end Sub

The code of getLastFilledColumnSource is correct in the matter of the parameter usage. It might be a little bit slow and this will be noticed if the sheets contain large used ranges or if the function is often called. You will see this after you make some performance checks. Maybe in your case, it will work flowless.

[nighttrain]

I am still facing the problem with sometimes excel.exe is staying in taskmgr.I got a lot of reports automatized, but sometimes just one excel.exe is in the taskmgr. I always trying to schedule my applications in the other time than other automatized reports, but could be a reason that if one application is executing and the second is starting in the sametime, could it be possible that excel.exe will stay because of that? What could be done in that situation,should i use always another names for e.g objXLApp or... i dont know..?

This post has been edited by nighttrain: 29 October 2012 - 12:18 PM

[Ionut]

Can you explain a bit the entire process? It's not very clear to me. You said you schedule your application. This means that your application runs at different points in time. Does it run on the same files? How often your application runs? How much data has to process? Is there a database from where it extracts those records that are processed?
An application remains in memory for different reasons:
- infinite loop or a very slow execution
- some sort of deadlock - tries to access a resource that is not available and a poor coding method keeps waiting instead of existing with an error code.
- this is in particular with excel - the application waits to confirm a message, for example: you try to close a worbook and excel prompts that message box to ask you if you want to save or that compatibility box that appears whenever you try to save from xlsx to xls. That message is invisible for everybody, but excel just waits for someone to push one of the buttons.
-many others that are not in my mind right now.

What I suggest you to do: log files. It's the easiest way. Keep track of the execution. Every time you start a process and do some things there, lof them. This way you will see what happens and which step is not completed.

[nighttrain]

Hello Ionut -for first thank you for all help you gave me not only here.
Of course i am using connection to database then i am filling the sheets, and then some operations doing. I am working almost with other files because the reports are diffrentso the names of files are diffrent and so on.
So i tried to applications not run in the same times... Mayby sometimes one application is executing when the another is starting to execute. And now i have almost the all time slots in the night period occupated, so i am wonder if i can schedule the new apps in the times where already are scheduled other existing applications..

I am using always the same template for that its look like this:

Imports Excel = Microsoft.Office.Interop.Excel
Imports Oracle.DataAccess.Client
Imports System.net.mail
Imports System.Runtime.InteropServices

Module Main

    Public objApp As New Excel.Application

    Public LocalDir = "D:\Reports\"

    Sub main()

        Try

            With objApp

                '---------------------------------------------------------------------------------------------------
                ' Open workbook daily report template
                .Workbooks.Open(Filename:=LocalDir & "Report.xls")

                '.Application.DisplayAlerts = False
                .Application.Visible = True
                '.Application.ScreenUpdating = False

               
                .Sheets("Data").Activate()

                .Cells.Select()
                .Selection.Delete(Shift:=Excel.XlDirection.xlUp)  

                .Range("A2").Select()

.............. operations on excel...
.............. operations on excel...

                Catch ex As OracleException
                    
                Finally
                    conn.Close()

                End Try

            End With

        Catch ex As Exception
            Call ErrorLog("Module Module1.vb (Main Catch), Sub Main: " & ex.ToString)
        Finally

            objApp.Quit()
            NAR(objApp)
            GC.Collect()
            GC.WaitForPendingFinalizers()

            'Debug.WriteLine("Sleeping...")
            System.Threading.Thread.Sleep(5000)

            Else
               
            End If
        End Try

    End Sub

    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

End Module

SO i am wonder its possible to run e.g 2 apps in the same time or someting. I wonder if that is posible, because that line is creating:

Public objApp As New Excel.Application

which all is doing mayby its only need to change the diffrent name of that for each project e.g:

Public objAppSomeReport2 As New Excel.Application
Public objAppAnotherNameReport As New Excel.Application

Please of answer

[Ionut]

I think I might have an idea. As I see you open the template and start working with it. This means you keep the file opened. So, one of the scenarios here is :
- you open the file
- the action delays from some reason, so another task starts.
- the file is opened and the new process can open it as read-only=> means the blocking = the process stays in memory.

If that is a template, I would strongly suggest you to make a copy of it and then do your work with that.

dim newFileName as string = string.Concat(LocalDir, "Report", DateTime.Now.ToString(), ".xls")
File.Copy(LocalDir & "Report.xls", newFileName)
.Workbooks.Open(Filename:=newFileName)

Now, I would like to explain something(I hope the process is clear in my head):
- every instance of an application that opens an Excel process, opens its own Excel process.

Public objApp As New Excel.Application

App1 and App2 uses the code above. App1 create a process(has one references to objApp) and App2 create a second process(has a different reference to the same objApp).