Hiding an Excel workbook loses focus
Hiding an Excel workbook loses focus
(OP)
Hi
I have some VBA code code in a workbook for summarising a CSV file report. Let's call this workbook "Reporter"
One of the first things it does is create a new workbook in to which all the data is imported, filtered, tidied/formatted and so on.
All works well but even with...
...it still opens a workbook-less Excel window, whereas I'd rather it would show nothing at all until it creates the final spreadsheet.
After a little research I discovered...
But then I found out that after running that line the Active window is now the original "Reporter" file, even if I try to force the new workbook to be the Active workbook:
What am I missing here?
I have some VBA code code in a workbook for summarising a CSV file report. Let's call this workbook "Reporter"
One of the first things it does is create a new workbook in to which all the data is imported, filtered, tidied/formatted and so on.
All works well but even with...
CODE --> VBA
Application.ScreenUpdating = False
...it still opens a workbook-less Excel window, whereas I'd rather it would show nothing at all until it creates the final spreadsheet.
After a little research I discovered...
CODE --> VBA
ActiveWindow.Visible = False
But then I found out that after running that line the Active window is now the original "Reporter" file, even if I try to force the new workbook to be the Active workbook:
CODE --> VBA
Set wbNewReport = Workbooks.Add ActiveWindow.Visible = False wbNewReport.Activate
What am I missing here?
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
wbNewReport.visible = false
RE: Hiding an Excel workbook loses focus
Excellent idea (and I'd like to be similarly more explicit all the way through my code), but in this instance it just gives
CODE --> Basic
The variable is defined as
CODE --> Basic
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Hiding an Excel workbook loses focus
You may set ScreenUpdating to False before, to avoid blinking.
combo
RE: Hiding an Excel workbook loses focus
CODE --> Basic
The line
CODE --> Basic
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
All that .select does is manipulate the GUI, which since you have turned off with Application.ScreenUpdating = False has no point.
Just work directly with whatever .range or other object that you need to manipulate.
I've spent about 5 minutes staring at your code. All it seems to try to accomplish is add some headers in row A cells I think. It's far too complex for that. Also, the desired headers seem to be expected in an array named arrColumns, but that is nowhere to be found.
RE: Hiding an Excel workbook loses focus
wbNewReport.Worksheets(strNewSheetName). ...
instead of:
Workbooks(strNewReportName).Worksheets(strNewSheetName). ...
A new workbook has one or more worksheets, depending on user setting. It is safe to assume that you work with first worksheet, so you may declare Dim wsAdvancedFilter as Worksheet and next assign first sheet of newly created workbook: Set wsAdvancedFilter = wbNewReport.Worksheets(1). Now you can directly rename the worksheet or pass to procedure worksheet and new name:
called by: sub_RenameSheet wsAdvancedFilter, strNewSheetName
I'm with mintjulep, avoid selecting and activating, from wsAdvancedFilter you have direct access to cells using .Range( ) or .Cells( ) for instance.
combo
RE: Hiding an Excel workbook loses focus
Totally agree! It does feel like a very bad habit (born from starting with recorded macros).
Top quality staring!
...because you're right,, that's exactly what it does. I didn't bother pasting the array code, but for completeness, here it is:
CODE --> Basic
CODE --> Basic
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
Thanks, both. I appreciate all your help (as I strive to learn how to VBA properly).
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
CODE --> VBA
RE: Hiding an Excel workbook loses focus
CODE
Sub SimpleHeader() Sheet1.Range("A1:J1").Value = Array("UPN", "User Display Name", "Caller ID", _ "Call Direction", "Number Type", "Destination Dialed", "Destination Number", _ "Start Time", "End Time", "Duration Seconds") End Sub
But I do like mintjulep's approach:
CODE
Option Explicit Public arrColumns(1 To 10) As String Sub AddHeaders() arrColumns(1) = "UPN" arrColumns(2) = "User Display Name" arrColumns(3) = "Caller ID" arrColumns(4) = "Call Direction" arrColumns(5) = "Number Type" arrColumns(6) = "Destination Dialed" arrColumns(7) = "Destination Number" arrColumns(8) = "Start Time" arrColumns(9) = "End Time" arrColumns(10) = "Duration Seconds" Call Apply_Headers(Sheet1, "A1", arrColumns) End Sub Public Sub Apply_Headers(Target_ws As Worksheet, Target_cell As String, aryHeaders() As String) With Target_ws.Range(Target_cell).Resize(, UBound(aryHeaders)) .Value = aryHeaders .EntireColumn.AutoFit 'just for kicks End With End Sub
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Hiding an Excel workbook loses focus
Is
CODE --> MicrosoftVisualBasic
I'm asking because the line
CODE --> MicrosoftVisualBasic
CODE --> MicrosoftVisualBasic
Here's the full sub (and yes, I know I've not yet changed the FOR EACH code to write those column headers!):
CODE --> MicrosoftVisualBasic
JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)
RE: Hiding an Excel workbook loses focus
You can be in Sheet1 and populate Sheet5 if you fully qualify objects in your code.
For example, let's populate any number of Worksheets in your Workbook with some numbers:
CODE
.Select and .Activate are used when you record the Macro, but that happens while you click on anything when recording. Not really needed in 'real' VBA code.
IMO
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Hiding an Excel workbook loses focus
You can for instance directly:
or relative to specific address:
combo