Hiding an Excel workbook loses focus 3

[JPJeffery]

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

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

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:

Set wbNewReport = Workbooks.Add
ActiveWindow.Visible = False
wbNewReport.Activate

What am I missing here?

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[mintjulep]

Don't really on activewindow. Be explicit.

wbNewReport.visible = false

 

[JPJeffery]

Don't really on activewindow. Be explicit.

wbNewReport.visible = false

Excellent idea (and I'd like to be similarly more explicit all the way through my code), but in this instance it just gives

Run-time Error '438':

Object doesn't support this property or Method

The variable is defined as

Public wbNewReport As Workbook

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[Andrzejek]

Your [tt]wbNewReport[/tt] does not have [tt].Visible[/tt] property:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Excel workbook may be viewed through one or more windows. Typically people use one window, so:
[pre]wbNewReport = Workbooks.Add
wbNewReport.Windows(1).Visible = False[/pre]
You may set ScreenUpdating to False before, to avoid blinking.

combo

 

[JPJeffery]

Sub CallsReport_01_Add_AdvancedFilterSheet()
    Set wbNewReport = Workbooks.Add
    wbNewReport.Windows(1).Visible = False

    strNewReportName = wbNewReport.Name
    strNewSheetName = "AdvancedFilter"
    sub_RenameSheet strNewReportName, strNewSheetName

    ' Create the Column headers
    Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select
    i = 1
    For Each Column In arrColumns
        ActiveCell.Value = arrColumns(i)
        ActiveCell.Offset(0, 1).Select
        i = i + 1
    Next
    Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select
End Sub

Sub sub_RenameSheet(strNewReportName, strNewName)
    Dim CSVSheetName As String
    CSVSheetName = Workbooks(strNewReportName).ActiveSheet.Name
    Workbooks(strNewReportName).Sheets(CSVSheetName).Name = strNewName
End Sub

The line

Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select

fails with Run-time error 1004 "Select method of Range class failed" though. Still learning how to be explicit about which Workbook/Worksheet to target!

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[mintjulep]

There is no need to use, and you should avoid using .select.

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.

 

[combo]

In the first line of procedure you assign workbook to variable ([tt]Set wbNewReport = Workbooks.Add[/tt], I guess that somewhere in the module you have [tt]Dim wbNewReport As Workbook[/tt]). Now it is shorter to refer directly to workbook:
[tt]wbNewReport.Worksheets(strNewSheetName). ...[/tt]
instead of:
[tt]Workbooks(strNewReportName).Worksheets(strNewSheetName). ...[/tt]

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 [tt]Dim wsAdvancedFilter as Worksheet[/tt] and next assign first sheet of newly created workbook: [tt]Set wsAdvancedFilter = wbNewReport.Worksheets(1[/tt]). Now you can directly rename the worksheet or pass to procedure worksheet and new name:
[pre]Sub sub_RenameSheet(wsToRename As Worksheet, strNewName)
wsToRename.Name = strNewName
End Sub[/pre]
called by: sub_RenameSheet wsAdvancedFilter, strNewSheetName

I'm with mintjulep, avoid selecting and activating, from [tt]wsAdvancedFilter[/tt] you have direct access to cells using .Range( ) or .Cells( ) for instance.


combo

 

[JPJeffery]

There is no need to use, and you should avoid using .select.

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.

Totally agree! It does feel like a very bad habit (born from starting with recorded macros).

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.

Top quality staring! :-D

...because you're right,, that's exactly what it does. I didn't bother pasting the array code, but for completeness, here it is:

Public arrColumns(1 To 10) As String

    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"

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[JPJeffery]

In the first line of procedure you assign workbook to variable (Set wbNewReport = Workbooks.Add, I guess that somewhere in the module you have Dim wbNewReport As Workbook). Now it is shorter to refer directly to workbook:
wbNewReport.Worksheets(strNewSheetName). ...
instead of:
Workbooks(strNewReportName).Worksheets(strNewSheetName). ...

Nice!

Thanks, both. I appreciate all your help (as I strive to learn how to VBA properly).

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[mintjulep]

Try this.

Public Sub Apply_Headers(Target_ws As Worksheet, Target_cell As String, Headers As Variant)

    Target_ws.Range(Target_cell).Resize(, UBound(Headers)).Value = Headers

End Sub

 

[Andrzejek]

Since you have hard-coded values for your header, the simple way would be to just:

Sub SimpleHeader()

Sheet1.Range("[red]A1:J1[/red]").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:

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   [green]'just for kicks[/green]
End With

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JPJeffery]

There is no need to use, and you should avoid using .select

Is

.Activate

a better choice?

I'm asking because the line

wsAdvancedFilter.Range("A1").Activate

produces that "Run-time error '1004': Activate method of Range class failed" error when I have

wbNewReport.Windows(1).Visible = False

in operation...

Here's the full sub (and yes, I know I've not yet changed the FOR EACH code to write those column headers!):

Sub CallsReport_01_Add_AdvancedFilterSheet()
    Set wbNewReport = Workbooks.Add
    Set wsAdvancedFilter = wbNewReport.Worksheets(1)
    wbNewReport.Windows(1).Visible = False
    
    wsAdvancedFilter.Name = "AdvancedFilter"
    wsAdvancedFilter.Range("A1").Activate
    
    i = 1
    For Each Column In arrColumns
        ActiveCell.Value = arrColumns(i)
        ActiveCell.Offset(0, 1).Select
        i = i + 1
    Next
    wsAdvancedFilter.Range("A1").Activate
End Sub

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[Andrzejek]

If you want to populate your Excel sheet, you do not need .Select or .Activate
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:

Option Explicit

Sub Andy()
Dim intS As Integer
Dim intR As Integer
Dim intNo As Integer

With ThisWorkbook
    For intS = 1 To .Sheets.Count
        With .Sheets(intS)
            For intR = 1 To 10
                .Cells(intR, 1).Value = intNo
                intNo = intNo + 1
            Next intR
        End With
    Next intS
End With

End Sub

.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

 

[combo]

Except of specific actions there is no need to select/activate cells you work with.
You can for instance directly:
[pre]With wsAdvancedFilter
For i = 1 To 10
.Cells(1,i) = arrColumns(i)
Next
End With[/pre]

or relative to specific address:
[pre]With wsAdvancedFilter.Range("A1")
For i = 1 To 10
.Offset(0,i - 1) = arrColumns(i)
Next
End With[/pre]

combo