Issues with excel open & close

sharshra

Active Member
Joined
Mar 20, 2013
Messages
270
Office Version
  1. 365
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?
Code:
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>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
For loop has lot more functionalities. It's a lengthy code & it's not possible to paste all those. But, the problem is to open & close the secondary sheet only once. Not it is doing every time & thereby substantially increasing the execution time.
Code:
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 do to somethings ...................................................... here............................. sometings here............ <CODE>            (code for remaining functionalities goes here...........................             .......................................................................             .......................................................................)      End If   Next   xlsSecInput.Close(SaveChanges: = False)Next</PRE>
</CODE></CODE>
 
Upvote 0
Andrew,
Thanks for the response. My question was a bit different. I want to open secondary sheet only if emp id from master sheet changes.

For example, 1111111 is emp id from master sheet & there are 10 rows with this emp id in master sheet. Secondary sheet with name 1111111.xls will open when 1111111 was read 1st time. When 1111111 was read 2nd time from master file, it should not open again. It will remain open till all 10 rows from master are read. After reading 10th row, 1111111.xls should get closed.
Next record in master sheet is 222222. Secondary sheet 2222222.xls should open when 2222222 was read 1st time & should remain open till all 15 rows of 2222222 is read from master sheet. This cycle repeats.

Now secondary sheet opens & closed for every row of data read from master sheet, though there is no change in the sheet opened & closed. This is causing execution time to shoot exorbitantly.
 
Upvote 0
Tried different ways to check if file is not already open. Searched internet but could not find correct piece of code. I am sure few lines will do this job, but not getting that. One of the codes I tried is as follows. If anyone can help with few lines of code, it will be of great help.
Code:
    Private Function IsFileAlreadyOpen(ByVal strFileName As String) As Boolean
        Dim bExists As Boolean = False
        Dim xlProcess As Process() = Process.GetProcessesByName("EXCEL")
        'check if any instance of Excel is open
        If xlProcess.Length > 0 Then
            Dim xlApp As Excel.Application = Interaction.GetObject(, "Excel.Application")
            'check if the workbook is open
            For Each xlWB As Excel.Workbook In xlApp.Workbooks
                If xlWB.Name = strFileName Then
                    bExists = True
                    Exit For
                End If
            Next
        End If
        Return bExists
    End Function
 
Upvote 0
Andrew,
Tried that as well. Also tried few other code snippets. I am sure it must be simple, but not getting that.
 
Upvote 0
Can any one suggest few lines of code please? It should open secondary sheet when emp id is encountered for first time. It will remain open till another emp id is encountered. When new emp id comes, it should close the previous emp id secondary sheet.
Code:
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 for funvtionalities goes here ...........
             ..............................................
             .............................................>
      End If
   Next
   xlsSecInput.Close(SaveChanges: = False)
Next
</CODE>
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top