Hide/Unhide sheets in an already open excel file

(0) Share

Report

Posted on by EleftheriaK

Hi team,

I am writing a flow like this:

Launch an excel file "Data_1"

runs a macro which refresh a certain table

Save and Close it

I have set a variable for timestamp.

Opens a second excel file "Report"

Run a macro which refresh a power Query reading data from the first excel file "Data_1"

In my excel file I have a hidden sheet named "SETTING" which I want the flow to unhide it, write in a certain cell the timestamp and hide again the sheet before saving it.

I am struggling to write the vb script to do that but the only thing I have found in the web is how to open and unhide all the sheets with the vb script. I Don't want that, I want to unhide a certain sheet from the already opened excel file.

What I have found is:

Set Variable ExcelFile and to the value is the excel path.

'Opens the Excel file'
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = True

'Unhides all the worksheets of the Excel file'
For i=1 To objWorkbook.Sheets.Count
objWorkbook.Sheets(i).Visible = True
Next

'Saves and closes the Excel file'
objWorkbook.Save
objWorkbook.Close SaveChanges = True

Any ideas?

Thank you in advance,

Eleftheria

Categories:

Power Automate Desktop

All responses (5)

Answers (1)

Sort by

OkanMTL 703 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

To unhide a specific sheet you can use the next VBA code:
Sub UnhideSheet()
Sheets("SETTING").Visible = True
End Sub

Good luck
EleftheriaK 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your prompt reply. Your advise is to write a macro and not a vb script, right?
OkanMTL 703 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
My bad,

Use this for VB script:
Set objWorksheet = objWorkbook.Worksheets("SETTING")
objWorksheet.Visible = -1 'Set the sheet's Visible property to visible

write this below the line:
'Unhides all the worksheets of the Excel file'
And inbetween:
Next

Good luck
EleftheriaK 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you again for your response!

You set the Dim objWorksheet but I don't see the set of objWorkbook. I tried it and is not working.
objWorksheet.Visible = -1 the -1 means true?
And how can I write the code for hiding the sheet?
Thank you in advance,
Eleftheria

Verified answer

Kaif_Siddique

2,108 Super User 2024 Season 1 on at

Like (1)

Report

Copy link

Link copied!

Could you try the below VBScript? Added if condition to unhide Sheet2(sheet name) but customize it as per requirements.

'Opens the Excel file'
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = True

'Unhides all the worksheets of the Excel file'
For i=1 To objWorkbook.Sheets.Count
if objWorkbook.Sheets(i).Name = "Sheet2" then
objWorkbook.Sheets(i).Visible = True
end if
Next

If I have answered your question, please mark my post as Solved.

If you like my response, please give it a Thumbs Up.

Regards

Kaif