asked on

Excel 2016 - running code on Hidden Workbook

I have a procedure where I open a workbook (Source.xlsx), do some clean up including Delete some Columns, then Save and Close.
I'm experimenting to see if I can do that with the Source.xlsx 'hidden'. (to try and make the steps in that clean up quicker/cleaner/not confuse the user)

This is my code

Sub Macro1()
    
    Dim wkbSource As Workbook
    Dim wkbApp As Workbook
    
    Set wkbApp = ThisWorkbook
   
    Workbooks.Open Filename:= _
        "C:\Hidden workbook test\Source.xlsx"

    Windows("Source.xlsx").Activate
    
    Set wkbSource = ActiveWorkbook    
    
    wkbSource.Windows(1).Visible = False
    
    MsgBox wkbSource.Sheets("Sheet1").Range("C4").Value
    wkbSource.Sheets("Sheet1").Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
        
    wkbSource.Windows(1).Visible = True
    
    wkbSource.SaveAs Filename:= _
        "C:\Hidden workbook test\SourceOutput.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

    wkbSource.Windows(1).Close
    
End Sub

Open in new window

I'm finding that, if I save when the workbook is Visible = False then when I Open the Saved SourceOutput.xlsx I is INVISIBLE, but Excel thinks it's already open.

And, the Delete Column is erroring.

Question: am I trying to do something impossible? ie. run code on a workbook that's not Visible?
And (!) can a workbook really be saved in a Visible = False state?!!! for it to Open 'invisible'? (or am I hullucinating?!!!)

Thanks

Fabrice Lambert

To open a workbook without seeing it, you first need to instanciate a new excel application, set it to invisible (if needed), then open the workbook.
Something like in the Following:

Dim app As Excel.Application
Set app = New Excel.Application
app.visible = false    '// maybe not required

Dim wb As Excel.Workbook
Set wb = app.workbooks.open ""C:\Hidden workbook test\Source.xlsx"

Dim ws As Excel.worksheet
Set ws = wb.worksheets("Sheet1")

Dim rng As Excel.Range
Set rng = ws.Range("C4")

msgbox rng.value
wb.columns("E:E").delete shift:=xlToLeft
wb.saveAs "C:\Hidden workbook test\SourceOutput.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
wb.close
app.quit

Open in new window

Additional notes:
You should not need to activate anything (this is slow, and provide a bad user experience).
Avoid like the pest objects such as Selection, the global Workbooks, Cells or Range collection, ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart ect ect …. because these objects are tightly linked to user interractions, may not have the value (state) you think, in other words they are chaotic, and you don't want to mess up with chaotic objects.

hindersaliva

ASKER

Thank Fabrice. Worked great!

I had to modify a typo on the Workbook.Open line.
My final code is

Sub GetDataFromInvisibleWorkbookAfterDataManipulation()

    Dim app As Excel.Application
    Set app = New Excel.Application
    app.Visible = False    '// maybe not required
    
    Dim wb As Excel.Workbook
    Set wb = app.Workbooks.Open(Filename:=ThisWorkbook.Path + "\Source.xlsx")
    
    Dim wbThis As Workbook
    Set wbThis = ThisWorkbook
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    Dim rng As Excel.Range
    Set rng = ws.Range("C7").CurrentRegion
    
    'MsgBox rng.Value
    
    rng.Copy
    
    wbThis.Sheets("Sheet1").Range("B10").Select
    ActiveSheet.Paste
    Range("B10").Select
        
    ws.Columns("E:E").Delete shift:=xlToLeft
        'don't want to save Source.xlsx
        wb.Saved = True
    wb.Close
    app.Quit

End Sub

Open in new window

hindersaliva

ASKER

A further question on having another instance of Excel running.

If the second instance, which is hidden, is not destroyed (say code terminates early) what will be the effect on the user?

I'm wondering if they'll have any unexpected behavior. eg. double-click an Excel file to open and nothing happens (because it has opened in a hidden instance of Excel?
?

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

Ok Fabrice, I'll put that in.

BTW just spotted an absurdity in my 'final' test code on this line.

ws.Columns("E:E").Delete shift:=xlToLeft

Open in new window

It should, of course, be higher up, before the copy/paste.