Issues with excel open & close

[sharshra]

I am writing excel macros in vb.net coe to do certain things. There is a master sheet from which it reads emp ids & opens another secondary excel sheet. If emp id from master sheet don't change, secondary sheet should not be closed. If there is a change in emp id from master sheet, it should open another secondary sheet & continue to keep it open till another different emp id from master sheet is encountered.

Suppose, emp id 1111111 has 10 rows of data in master sheet. It should open secondary sheet corresponding to this emp id only once & keep it open till next emp id is encountered. Now secondary sheet opens 10 times & closes 10 times. This is making execution time too long. Instead, it should open only once & close once.

I have the code as follows. There must be better ways to make this happen. Any ideas please?

For i = 1 to intMasterRows
    For j = 1 to intMasterCols
        If j = 3 Then
            xlsSecInput = xlsApp.Workbooks.Open("C:\" & intEmpid & ".xls")
            xlsSecWorksheet = xlsSecInput.Worksheets(1)
            xlsSecRange = xlsSecWorksheet.UsedRange

<CODE to somethings here............
       <CODE do to sometings here.............................
        ......................................................
        ......................................................>
      End If
   Next

   xlsSecInput.Close(SaveChanges: = False)
Next

</CODE></CODE>

 

[Andrew Poulsom]

Where are you assigning a value to intEmpid?

 

[sharshra]

I am assigning intEmpid after reading 1st column from each row from master sheet, as seen in the code below.

For i = 1 to intMasterRows
    'Read Empid from 1st column of master sheet rows
    'intEmpid = value from 1st column from row i

    For j = 1 to intMasterCols
        If j = 3 Then
            xlsSecInput = xlsApp.Workbooks.Open("C:\" & intEmpid & ".xls")
            xlsSecWorksheet = xlsSecInput.Worksheets(1)
            xlsSecRange = xlsSecWorksheet.UsedRange
            
            (code for remaining functionalities goes here
             line 1
             line 2.....
             line n)
            
      End If
   Next
   xlsSecInput.Close(SaveChanges: = False)
Next

 

[Andrew Poulsom]

Show me the actual code.

 

[sharshra]

Andrew,
I used a workaround to solve this problem. Code is too big to paste in this forum. In brief, I have done the following & it works . However, there may be better ways to do this. Welcome any suggestions.

-In the first line after For loop, use CountIf to count the emp id in the sheet
-Initiate a counter to count the occurances & increment by 1 every time For loop executes
-Open secondary file only when counter is 1
-After the For loop, check the counter value
-If counter value is equal to count of emp id, close the secondary file
-Reset the counter to 1

For i = 1 to intMasterRows
    'Count emp id occurances using CountIf function
    intEmpIdCount = xlsApp.WorksheetFunction.CountIf(xlsMasterRange.Range("A2:A566"), "=" & xlsMasterRange.Cells(i,"A").value)
    intEmpIdCounter = intEmpIdCounter + 1
    
    For j = 1 to intMasterCols
        If j = 3 Then
            If intEmpIdCounter = 1 then
                xlsSecInput = xlsApp.Workbooks.Open("C:\" & intEmpid & ".xls")
                xlsSecWorksheet = xlsSecInput.Worksheets(1)
                xlsSecRange = xlsSecWorksheet.UsedRange
            End If
            (code for remaining functionalities goes here
             line 1
             line 2.....
             line n)
            
      End If
   Next

   If intEmdIdCounter = intEmpIdCount then
       xlsSecInput.Close(SaveChanges: = False)
       intEmpIdCounter = 0
   End If
Next

I had used this last week itself, but could not respond to you. Sorry about that.