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



Code:
   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.

Code:
     
   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