[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

How to have two open workbooks with just one visible

When we switched from Excel 2010 to 2013, we noticed intense screen flashing while long macros created in Excel 2010 were running even though the macro began with
Application.ScreenUpdating = False

Open in new window

and ended with
Application.ScreenUpdating = True

Open in new window

I found out that this code no longer allows me to have two open workbooks where the one we open manually is visible and the other we open by VBA is not visible. This was due largely to my use of code like
Workbooks(1).Sheets(intCounter).Activate

Open in new window

to switch between workbooks. I've learned that using code like
Workbooks(1).Sheets(intCounter).Range("C5:H5").Value = strPONumber

Open in new window

resolves the flashing problem.
The one problem with still remains is how to hide the opening of the second workbook from the user while still allowing them to see the changes that are happening in the first workbook. In the first workbook I'm using code like
Application.StatusBar = strMsg

Open in new window

to let them know how the macro is progressing.
Every solution I have tried either gives a big white "flash" while the second workbook opens and is hidden, or completely disables both workbooks so that the status bar shows nothing and the program appears to be "locked" until the macro finishes.
Surely using Excel like this is so common that there has to be a solution. But everything I've been able to find so far may work on other versions of Excel, but not 2013.
I'd appreciate some help.
0
maderitetech
Asked:
maderitetech
  • 3
3 Solutions
 
rspahitzCommented:
Another possible solution is to bypass the Excel interface directly and treat the workbook as an object.  The code is not trivial but would look something like this:

    Dim wkbk As Workbook
    Set wkbk = Application.Workbooks.Open("your file here.xls")' you may need to app a parameter to identify the format
    wkbk.Sheets(1).Value = ""' here's where you'd manipulate the workbook
    wkbk.Close
    Set wkbk = Nothing

Open in new window


I'm not sure, but you may also have to add to the end:
    Application.Quit
But I'm not sure if that quits all workbooks or only the one you opened.
0
 
maderitetechAuthor Commented:
I'm not familiar with bypassing the Excel interface directly and treat the workbook as an object. I found another solution by using a userform to update the status of the macro.
0
 
maderitetechAuthor Commented:
My code already had the following for opening the workbooks. This may be in part what rspahitz was suggesting. WB1 is the visible workbook, WB2 the one I want to hide.

Dim app As New Excel.Application
Dim WB1 As Excel.Workbook
Dim WB2 As Excel.Workbook
Set WB1 = ThisWorkbook
Set WB2 = app.Workbooks.Add("my_hidden_excel_workbook")

Open in new window

0
 
maderitetechAuthor Commented:
I intended to use the status bar to update macro progress, but the userform works just as well, allows me to keep only the workbook I want visible while the other one is hid. I did not use any suggested solutions. I do not know if they would have worked.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now