Open Excel File via vba and hide/unhide worksheet

[lmarconi]

Good morning all.
I have this question: i have 4 forms which we can call form1, form2, from3 and form4. In each form i have a button used to open and excel file (always same filename and always in same folder).
In the excel file i have 4 worksheets (we can call ws1, ws2, ws3 and ws4). What i need is a vba code to put in each button which will open the excel file and hide all ws depending from the form where i'm.
Example : if i'm in form1, i want to unhide only ws1 and ws2, ws3 and ws4 should be hidden; if i'm in form2, i want to unhide only ws2 and ws1, ws3 and ws4 should be hidden; etc.


Any kindly suggestion please ?
Cheers,

[Gicu]

Hi,
Please show us what you tried, this is simple Excel automation, open the Excel workbook in VBA and loop through its sheets and set the visible property accordingly:
https://www.automateexcel.com/vba/hide-unhide-sheets/

Cheers,

[lmarconi]

This is how to solve: i add this in the Click Event of each the Button, changinf the "visible" value.

'-------------------------------------------------------
#DoCmd.Close acForm, "frm_waiting"


#Dim xlApp As Object
#Set xlApp = CreateObject("Excel.Application")

# xlApp.Workbooks.Open ("\\musnas04\cpd_projects\CTO_OR\01- DOM COMMON\00- DOM- Common ORAC File (Access)\ORAC Parameters Details.xlsx")
# xlApp.ActiveWorkbook.Sheets(1).Visible = False
# xlApp.ActiveWorkbook.Sheets(2).Visible = True
# xlApp.ActiveWorkbook.Sheets(3).Visible = False
# xlApp.ActiveWorkbook.Sheets(4).Visible = False
# xlApp.Visible = True


# DoCmd.Close acForm, "frm_waiting"
'--------------------------------------------------------

I don't like the open and close form because the idea is to inform the user that the file is going to be open (it takes few second and i want avoid they think it not works), but this is what coming in my mind.
Cheers,

[Gicu]

Here is a bit nicer way to do it, as you don't have to repeat it in your four forms, add the code below as a public sub in a standard module:

Public Sub xlOpenExcel(iSheet as integer)
Dim xlApp As Object, i as Integer
Set xlApp = CreateObject("Excel.Application")


xlApp.Workbooks.Open ("\\musnas04\cpd_projects\CTO_OR\01- DOM COMMON\00- DOM- Common ORAC File (Access)\ORAC Parameters Details.xlsx")


For i=1 to 4
	xlApp.ActiveWorkbook.Sheets(1).Visible = IIF(i=iSheet,True,False)
Next i


xlApp.Visible = True


DoCmd.Close acForm, "frm_waiting"


End Sub
'To use from your 4 forms
'Call xlOpenExcel(1) from form1, xlOpenExcel(2) from form2,etc.

Cheers,

[lmarconi]

Here is a bit nicer way to do it, as you don't have to repeat it in your four forms, add the code below as a public sub in a standard module:

Public Sub xlOpenExcel(iSheet as integer)
Dim xlApp As Object, i as Integer
Set xlApp = CreateObject("Excel.Application")


xlApp.Workbooks.Open ("\\musnas04\cpd_projects\CTO_OR\01- DOM COMMON\00- DOM- Common ORAC File (Access)\ORAC Parameters Details.xlsx")


For i=1 to 4
    xlApp.ActiveWorkbook.Sheets(1).Visible = IIF(i=iSheet,True,False)
Next i


xlApp.Visible = True


DoCmd.Close acForm, "frm_waiting"


End Sub
'To use from your 4 forms
'Call xlOpenExcel(1) from form1, xlOpenExcel(2) from form2,etc.

Cheers,

Great,
much appreciate it.
Cheers,