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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,970
Messages
5,508,467
Members
408,686
Latest member
celobacara

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top