Macro to open workbook

[sdrubezki]

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

 

[John Davis]

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:

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

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

 

[sdrubezki]

Thank you! I'll try this.

 

[sdrubezki]

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]

Maybe:

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

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....

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

 

[kpark91]

In the CLOSEIT Sub, insert the following code

Application.DisplayAlerts = False

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

So instead of

ThisWorkbook.Save
ThisWorkbook.Close

Substitue this whole code with

ThisWorkbook.Close SaveChanges:=False

<CODE>
</CODE>

 

[sdrubezki]

Thank you both for your great help.