Worksheets not viewable after xl.workbook.close

[afolson-NOAA]

I have a script that opens a workbook, reads a range, runs a macro, then closes the workbook. The script works fine, but when I go to open the workbook in the Excel Application, none of the worksheets or worksheet tabs are visible. I know the workbook is loaded because the range name dropdown list in the toolbar shows the range names. If I try to open some other similar workbook I have, the worksheets are not visible. Based on some web searches I have discovered if I load an excel add-in (e.g. Solver), the worksheets become visible. Interestingly, I have also found that if I open R_connection_examples.xlsm before I open my workbook, all worksheets are visible.

Any ideas what could be going on? Is there a workaround I can automate in the workbook?

Thanks.

[afolson-NOAA]

A simple workaround I have found is to include the following in the ThisWorkbook scope in VBA:

Private Sub Workbook_Open()
Sheets(1).Visible = True
End Sub

[gdemin]

Hi, @afolson-NOAA,
Could you provide a relevant part of the script which you are using? Does the script create these sheets or just modify existing sheets?

[afolson-NOAA]

Gregory- The strange behavior occurs following the xl.workbook.close(wbName) call, then reopening the wbName workbook in excel. Excel starts up and the workbook opens, and the dropdown for selecting ranges shows the names, but none of the worksheets or worksheet tabs are visible. As mentioned in my followup post, All of the worksheets in the workbook become visible if a worksheet is made visible with a VBA call, an Excel add-in is turned on, or I open the excel.link example workbook provided with the package. The parts of the script that involve excel.link are: library(excel.link) library(tidyverse) library(pcdrisk) # An internal modeling/simulation package. wbName <- "PCDRisk_Input_Template_4.2.3.xlsm" xl.workbook.open(wbName) xls <- xl.get.excel() scenario_info <- xl["Scenarios"] scenarios <- list() for(i in 1:length(scenario_info[[1]])) { xl.sheet.activate(scenario_info[[1]][i]) xls$Run("B_Create_Input_File") # Creates PCDRiskInputTemplate.xlsx var_list <- pcdrisk::read_input() # Reads data in PCDRiskInputTemplate.xlsx scenarios[[scenario_info[[1]][i]]] <- var_list file.remove("PCDRiskInputTemplate.xlsx") } #### A bunch of code to create a tibble from the data in the scenarios list ##### # Cleanup xl.workbook.close(wbName) xls$Quit() rm(var_list, scenario_info, scenarios, wbName, i, xls)

…

On Mon, May 13, 2024 at 4:53 AM Gregory Demin ***@***.***> wrote: Hi, @afolson-NOAA <https://github.com/afolson-NOAA>, Could you provide a relevant part of the script which you are using? Does the script create these sheets or just modify existing sheets? — Reply to this email directly, view it on GitHub <#31 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AW2GMAH6QP67U35YDX2XCEDZCCSUXAVCNFSM6AAAAABHNSUZVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBXGM3TKMBYGA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- *Alan Olson* *Fish Biologist, NEPA Support, Contractor with Lynker in support of* NOAA Fisheries Sustainable Fisheries Division, U.S. Department of Commerce Mobile: (360) 218-4239 www.fisheries.noaa.gov

[gdemin]

As far as I can see you don't create sheets. So it rather strange that visibility flag is changed for existing sheets. Can it be caused by macro 'B_Create_Input_File'?

[afolson-NOAA]

I don't think so. The tabs and worksheets are all visible in the open excel instance until xl.workbook.close() call. The B_Create_Input_File macro does copy a worksheet from one workbook to another, but does nothing in regards to their visibility. The macro does set screenupdating to False and there are some exit points in the macro that fail to turn screenupdating back to True before exiting (bad form!) , but that shouldn't affect excel the next time it is started. Unless other users are having issues, maybe this isn't worth pursuing too far. I have a workaround that corrects the symptom fairly easily. I'm beginning to suspect that tracking down the cause could be a real rabbit hole.

…

On Mon, May 13, 2024 at 9:58 AM Gregory Demin ***@***.***> wrote: As far as I can see you don't create sheets. So it rather strange that visibility flag is changed for existing sheets. Can it be caused by macro 'B_Create_Input_File'? — Reply to this email directly, view it on GitHub <#31 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AW2GMADJ6DFG4QD4KVHY7VTZCDWLZAVCNFSM6AAAAABHNSUZVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBYGIZTCMRSGU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- *Alan Olson* *Fish Biologist, NEPA Support, Contractor with Lynker in support of* NOAA Fisheries Sustainable Fisheries Division, U.S. Department of Commerce Mobile: (360) 218-4239 www.fisheries.noaa.gov

[gdemin]

Ok, if you will have additional details about this issue, keep me in touch. Currently I can't reproduce this behaviour.

[Siddharth-Latthe-07]

Here are some of the Possible Causes:

1. Hidden Worksheets: Your script might be interacting with the workbook while some worksheets are hidden.
2. Macro Behavior: The macro you're running within the script might be intentionally hiding worksheets for specific tasks.
3. Corrupted Workbook: In rare cases, the workbook itself might be corrupted, causing issues with sheet visibility.

Unhide Worksheets: Before opening the workbook in Excel, use your script to unhide all worksheets.

VBA
Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

2. Review Macro Code:

3. Examine the macro code you're running for any lines that might be hiding worksheets. You might find code like ws.Visible = xlSheetHidden within specific sections. Modify the code to keep the worksheets visible after execution.

4. Repair Workbook (Last Resort): If you suspect a corrupted workbook, right-click on the file in Explorer, go to Properties > Open With > Choose another app. Select "Microsoft Excel" and check the box "Always use this app to open .xlsx files." Click OK and try to open the workbook. Excel might attempt to repair it during the process.

Hope, this helps
Thanks