Macro to open workbook

sdrubezki

New Member
Joined
Aug 5, 2010
Messages
16
Hello everyone, I have a workbook and the first sheet is an index to go to other sheets in the same file, each sheet is a form.

The info for those forms is in different workbooks, so what I need is a macro to open a certain workbook when a certain sheet is selected, keep it hiden and close it when user hits a send button.

Can anyone assist?
Thanks much in advanced.
Sebastian
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Hello everyone, I have a workbook and the first sheet is an index to go to other sheets in the same file, each sheet is a form.

The info for those forms is in different workbooks, so what I need is a macro to open a certain workbook when a certain sheet is selected, keep it hiden and close it when user hits a send button.

Can anyone assist?
Thanks much in advanced.
Sebastian


Maybe:

Code:
Private Sub Worksheet_Activate() ' goes into the certain sheet code
    Workbooks.Open Filename:= _
        "D:\PATH\Certain Workbook.xls"
    ActiveWindow.Visible = False
End Sub

Call the below in the SEND buttons click/doubleclick event

Code:
Sub CLOSEIT()
'
'
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub
 

sdrubezki

New Member
Joined
Aug 5, 2010
Messages
16
Hi again, it worked perfectly! Thanks. One more question: I want to avoid excel from asking if to save the "certain workbook", just need to close it without saving when hitting "send". I tried a few options to change the ActiveWorkbook.Save line but didn't succeeded. Can you help?

Thanks in advanced.
 

sperschler

New Member
Joined
Apr 10, 2009
Messages
1

ADVERTISEMENT

Maybe:

Code:
Private Sub Worksheet_Activate() ' goes into the certain sheet code
    Workbooks.Open Filename:= _
        "D:\PATH\Certain Workbook.xls"
    ActiveWindow.Visible = False
End Sub

Call the below in the SEND buttons click/doubleclick event

Code:
Sub CLOSEIT()
'
'
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub

In the CLOSEIT Sub, insert the Application.DisplayAlerts = False code as the first line. In its final state it should look something like this....
Code:
Sub CLOSEIT()
'
'
    Application.DisplayAlerts = False
    Workbooks("Certain Workbook.xls").Activate
    ActiveWindow.Visible = True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub
 
Last edited:

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
In the CLOSEIT Sub, insert the following code
Code:
Application.DisplayAlerts = False

This will only not show the dialog box but the workbook will save.

So instead of
Code:
ThisWorkbook.Save
ThisWorkbook.Close

Substitue this whole code with
Code:
ThisWorkbook.Close SaveChanges:=False
<CODE>
</CODE>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,320
Messages
5,658,134
Members
418,429
Latest member
Regila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top