Open and Refesh several excel files at same time Help

[jpskiller]

Hi,

I am attempting to write a basic excel automation program, which will have a scheduling system, so could have several files which need to be refreshed at same time, but may take different times to refresh each file which could run into next schedulled timer

Intend to use timer control to fire files off at specified times.

i.e

@10:00 - 3 files to refresh, but 2 may take 5-10 mins
@10:05 - 1 file take 2 mins

etc

Basic code to Open And Refresh taken from web with slight changes

   Public Sub OpenExcel(ByVal FileName As String)

        If IO.File.Exists(FileName) Then
            Dim Proceed As Boolean = False
            Dim xlApp As Excel.Application = Nothing

            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlApp.Visible = True

            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
            xlWorkBook.RefreshAll()
 
           ' Intend to have separate proc to refresh connections by turning background  off
           ' Also separate proc to refresh pivot tables

            xlWorkBook.Save()
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
        End If
   End Sub

   Public Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
   End Sub

I tried to add threading but have no real experience with doing that.


Test open files simultaneously and refresh, as far as i know I cant pass parameters so I tried to use a variable to set the filename to refresh from Openfiles sub but this did not work.

I thought about maybe building some kind of que and just refresh each file and keep chcking time to maintain it, but was hopeing for better solution.

How could I achieve this or am I going about it all wrong.

     
   Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim T1, T2, T3 As Thread

        T1 = New Thread(AddressOf OpenFiles)
        T2 = New Thread(AddressOf OpenFiles)
        T3 = New Thread(AddressOf OpenFiles)

         a = 1
        T1.Start()

        a = 2
        T2.Start()

        a = 3
        T3.Start()

   End Sub

   Dim a As Integer

   Private Sub OpenFiles()
        If a = 1 Then OpenExcel("C:\Temp\Excel\Book1.xlsx")
        If a = 2 Then OpenExcel("C:\Temp\Excel\Book2.xlsx")
        If a = 3 Then OpenExcel("C:\Temp\Excel\Book3.xlsx")
   End Sub

[si_the_geek]

In addition to creating Threads directly, you can create Tasks, which have more easy to use functionality, eg:

   Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
      Dim tasks(2) As Task
      tasks(0) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book1.xlsx") )
      tasks(1) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book2.xlsx") )
      tasks(2) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book3.xlsx") )

      Task.WaitAll(tasks)
      MessageBox.Show ("All files finished")
   End Sub

Note however that just because your code will be running threaded, it doesn't necessarily mean Excel will (and that could vary by version of Excel, and by computer).

[jpskiller]

In addition to creating Threads directly, you can create Tasks, which have more easy to use functionality, eg:

   Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
      Dim tasks(2) As Task
      tasks(0) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book1.xlsx") )
      tasks(1) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book2.xlsx") )
      tasks(2) = Task.Run( Sub() OpenExcel("C:\Temp\Excel\Book3.xlsx") )

      Task.WaitAll(tasks)
      MessageBox.Show ("All files finished")
   End Sub

Note however that just because your code will be running threaded, it doesn't necessarily mean Excel will (and that could vary by version of Excel, and by computer).

Thanks for this,

I have added Imports System.Threading.Tasksbut have following issue the Task.Run gives me an error 'Run' is not a memeber of 'System.Threading.Tasks.Task'

Is this because of the version I am using, currenlty I am restricted to VB 2010 and .Net 4.0

[jpskiller]

Hi,

Managed to get it working by using the Task.Factory.StartNew

   
        Dim tasks(2) As Task
        tasks(0) = Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book1.xlsx")))
        tasks(1) = Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book2.xlsx")))
        tasks(2) = Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book3.xlsx")))

        Task.WaitAll(tasks)

Thought I would try using a listof so I could add as many Tasks as I require.

        Dim tasks As New List(Of Task)()
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book1.xlsx"))))
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book2.xlsx"))))
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book3.xlsx"))))

        Task.WaitAll(tasks.toarray)

I realise the WaitAll is wating unttil all tasks are complete before allowing program to continue to be used, is there anyway to do this so can continue to use program, i.e. can it be done in background?

[si_the_geek]

As you don't want the WaitAll you can simply remove it - that was just an example of some of the functionality provided by Tasks.

It is worth keeping the List tho, as you will presumably want some kind of management of the tasks (eg: if the user tries to close your program, it is a good idea to stop the tasks). To enable that kind of thing, you'll need to declare the List outside of the routine.

[jpskiller]

Cheers, that's great, this multithreading is useful but also getting complicated very quickly

How on earth would I update controls on a form, I added to end off Procedure OpenExcel:

    If FileName = "C:\Temp\Excel\Book1.xlsx" Then TextBox1.Text = FileName & " Done"
            If FileName = "C:\Temp\Excel\Book2.xlsx" Then TextBox2.Text = FileName & " Done"
            If FileName = "C:\Temp\Excel\Book3.xlsx" Then TextBox3.Text = FileName & " Done"

Cross-thread operation not valid: Control 'TextBox1' accessed from a thread other than the thread it was created on.

[si_the_geek]

Unfortunately dealing with UI elements from a thread is a bit awkward, because you can't be sure that changes are safe.

There is an explanation of issues and several solutions here:
https://docs.microsoft.com/en-us/dot...forms-controls

In this case I think something like the SetText routine would be the best way to go, but note that unfortunately you will need one routine per textbox you want to interact with (or one routine with more complex code).

[jpskiller]

Great should be able to work out so way to write to a form control.

Do you have any examples of how I could track the threads I start with the above method fso I know when they have completed so I can get status and know when each thread is complete

[jpskiller]

I have updated the task start to

      Dim tasks As New List(Of Task)()
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book1.xlsx")), CancellationToken.None, TaskCreationOptions.None, TaskScheduler.Default))
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book2.xlsx")), CancellationToken.None, TaskCreationOptions.None, TaskScheduler.Default))
        tasks.Add(Task.Factory.StartNew((Sub() OpenExcel("C:\Temp\Excel\Book3.xlsx")), CancellationToken.None, TaskCreationOptions.None, TaskScheduler.Default))

As I understand this would e more equivelent to Task.Run which I can not use, and I believe somehow this is the key to monitoring and being able to cancel threads if required, just dont know how