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 https://github.com/gdemin/excel.link/issues/31#issuecomment-2107375080, 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 https://github.com/gdemin/excel.link/issues/31#issuecomment-2108231225, 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.