Close Book1, Open Book2, Activate Macro1 Of Book2

Mar 6, 2010

I have this code in Book1:


sub OpenCloseMacro1()
Workbooks.Open Filename:="E:closeopenBook2.xlsm"
Workbooks("Book1.xlsm").Close SaveChanges:=True
Application.Run ("Book2.xlsm!Macro1")
End Sub
It closes Book1 and saves it, it opens book2, but Macro1 does not activate. Why?

by the way. Macro1 is:


Sub Macro1()
Userform1.show
End sub


sub OpenCloseMacro1()
Workbooks.Open Filename:="E:closeopenBook2.xlsm"
Application.Run ("Book2.xlsm!Macro1")
Workbooks("Book1.xlsm").Close SaveChanges:=True

End Sub
It opens book2, and activates macro1, but Book1 is not closed

View 9 Replies


ADVERTISEMENT

Check To See If File Is Open, And If It Is Open, Activate It

May 14, 2009

I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:

View 5 Replies View Related

Activate Another Open Workbook

Aug 15, 2008

I have 2 workbooks open

Book1 has a call to ChangeBook()
Book2 is empty

Function ChangeBook()
Application.Workbooks("Book2.xls").Activate
End Function

ChangeBook() is located in a .xla file loaded on Excel launch.

Calling the function from Book1 does not cause Book2 to activate.

Running the function via the F5 key in the IDE works fine.

I am modifying existing routines that have problems and being able to activate certain books at will would make the changes much easier.

View 9 Replies View Related

Activate Sheet After Open Workbook..?

Jan 26, 2010

On a command button in a userform, I have this code to open another workbook:

View 2 Replies View Related

Workbooks.Open Does Not Activate The Workbook

Nov 5, 2008

I have a workbook containing macros that opens a second one (no macros) and does some 'data mining'.

The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):

Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name

The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...

If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code.

tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming

View 9 Replies View Related

Activate Sheet And Disable Tabs On Open

Dec 6, 2008

I am trying to create an event macro that would deactivate column and row headers when the workbook is launched and activate a sheet. i am not managing to do this,

View 2 Replies View Related

Activate Open Workbook From Cell Reference

Aug 20, 2008

activate an open workbook based on the workbook name that is typed into a cell that is a named range?

So, I keep wanting to do this:
Windows.Activate Filename:=Range("MyRange")

Because this works:
Workbooks.Open Filename:=Range("MyRange")

View 9 Replies View Related

How Can I Activate An Open Workbook By Macro In Seperate Instances

Feb 24, 2009

I have a program that has all Excel Workbooks in seperate instances of Excel. There is a very sound reason for doing this.

The user has maybe 3 to 10 workbooks open. There are times when a workbook is active and has a Macro Link to open one of the already open workbooks.

When the user clicks the link, they naturally get an error message stating that the workbook is already open. Then they have to close the error msg and click on the Macrosoft Tab and look thru the list of open workbooks and then click the one they are looking for. Additionally, in this Menu Program the user really doesn't even have to know the name of the various workbooks.

I hope everyone will believe me when I state that this program works berautifully.

Right now I am simply cleaning up and making a few little things work better.

QUESTION When the user clicks on a macro link that is to open a workbook that is already open, how - On error - can I have the macro continue on and activate the requested workbook - - - Please remember they are all in separate instances of Excel.

Since the code I'm using can determine if the requested workbook is already open, I think there has to be a way to activate that workbook.

View 14 Replies View Related

Workbooks Take Long Time To Activate When Vb Editor Is Open

Mar 17, 2007

I have the VB Editor open and am manually activating different workbooks in Excel (with 20/25 modules each), the VB Editor goes through a process of maximising each and every module in the workbook I have selected before I can edit any code or, indeed, do anything in the workbook. This process takes about 8-10 seconds every time I select a different workbook in excel! I have played around with the settings "Full Module View" in the options section of the VB editor, but to no effect.

View 3 Replies View Related

Decrease Macro1 Running Time (Copy And Paste Values)

Aug 20, 2014

1- Open enclosed file.
2- Run Macro1.
3- Wait some time completing running Macro1.
4- Run Macro2.
5- Wait some time completing running Macro2.
6- Look MessageBox to see that what is Macro2 running time. (Note: My Macro2 running time is 06 minutes and 49 seconds)

My question is how can I decrease Macro2 running time ?

I am open all ideas like copy data to NotePad and paste again excel etc.

View 3 Replies View Related

Open Close VBA

Apr 14, 2008

Can I take

Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
and do something like this?

Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls") Not _
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")

I have a userform that I would like with a click of a button to open the workbook and then click the same button to close the workbook after it has been viewed.

View 9 Replies View Related

Using Book1 In A Macro

Jan 19, 2007

I have a macro that I run every day that creates a new book - Book 1, does stuff to it and saves it with a new name.

I always leave my computer on (and logged on) overnight. when i leave for the day, I just Lock it - CTRL ALT DEL - Lock Computer.

almost always this works fine, I run that macro every day, and almost always it uses Book 1 when creating the new book. Even though I haven't logged off/on since the last time it ran...And the macro works fine referring to Book1.

But every once in a while, I can't put any schedule on it, it doesn't work. Excel remembers that I've already created book1, and moves to book2. then the macro fails because it's looking for book1. - easily remedied by debugging and replacing book1 with book2....but a pain.

I also notice that at the same time that happens, I have another wierd problem...

I have a web query that I refresh every day. When I first open the worksheet for the day, I have to go and enter the userid/password for the web query.

but any time the 1st problem is happening, I no longer have to put in the userid/password in the web query.

here's the snippet of code that copies 4 tabs from one workbook and puts it into a new workbook...

Sheets("Manager MTD").Select
Sheets("Manager MTD").Copy
ActiveWindow.ActivateNext
Sheets("Manager WTD").Select
Sheets("Manager WTD").Copy Before:=Workbooks("Book1").Sheets(1)
ActiveWindow.ActivateNext
Sheets("Agent MTD").Select
Sheets("Agent MTD").Copy Before:=Workbooks("Book1").Sheets(1)
ActiveWindow.ActivateNext
Sheets("Agent WTD").Select
Sheets("Agent WTD").Copy Before:=Workbooks("Book1").Sheets(1)
the end of the macro saves the new file with filename ACP_Temp.xls

View 9 Replies View Related

File Is Getting Close When Open It

Jan 16, 2009

When i open excel it give an error and it close. My computer up to date about office upgrades. And i try to repair my office but it doesnt solve my problem.

View 7 Replies View Related

Vba To Open And Close The File?

Jun 3, 2014

Below is the code to open a csv file and paste it in the workbook "ResultFile". When i click on a button, a csv file will open to copy the values and paste it in "Resultfile". Now, i want to close the csv or excel file to be closed once copying has done.

where to put the close file.

[Code] ....

View 2 Replies View Related

VBA On Open/Close Conumdrum

Jul 27, 2006

I'm creating a template that will be opened by users and saved to a new name based on info provided by the user. I have macros triggered when opening and closing the workbook. The Before Closing sub does some data transfer between workseets. I'm running into a problem with the Open sub, however. I wanted to give the user a chance to abort the opening, giving a dialog that allows canceling. I wanted to just close the workbook.

However, if I include an Active.Workbook.Close line in the Open sub, it of course triggers the Before Close sub, but it leaves the On Open sub hanging. The workbook closes, but the next time I open it, the dialog in the Open sub doesn't come up. Close again, and it works ok or quit Excel between openings and it is ok. If I let the Open macro complete, bypassing the remainder of the macro if the user indcates canceling, how to I trigger closing within VBA? If the Open macro completes without an Activeworkbook.close statement things will just sit there until the user initiates closing.

Is there a way to terminate the Open sub from within the Before Closing sub if I create a flag to indicate closing started during the open sub or some other way around this problem?

View 5 Replies View Related

Close All Open Workbooks Except......

Feb 25, 2008

I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb

Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

View 3 Replies View Related

Open, Print And Close

Apr 10, 2008

When i open an excel file, i need it to print 3 copies and then close itself without saving. This is because i have a file that must have 3 copies printed everyday. I know its lazy =) but i'm sure it will be very useful and i have seen it on a file before however i cannot successfully replicate it.

View 3 Replies View Related

PERSONAL Loads Now Not Book1

Jun 6, 2006

I had made some code changes that didn't end up working as I wanted and this called for PERSONAL to be called and acted on when excel was activated. Now I have deleted the code but still on opening excel is persists in loading PERSONAL. urk. How can I go back to loading Book1 on open?

View 9 Replies View Related

Open Userform When You Close A Spreadsheet

Mar 5, 2007

I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.

When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).

View 9 Replies View Related

Using VBA To Open And Close Source File

Feb 1, 2010

I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.

My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:

I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.

View 11 Replies View Related

Close ALL Open Windows Folders

Jul 10, 2012

I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.

Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit

Loop

View 3 Replies View Related

Open Worksheet - Refresh And Close

Feb 11, 2013

I have a batch script which runs the following line to open my excel sheet:

start m:exportad.xls

when this sheet opens it automatically refreshes from a csv file.

The problem is that I want this sheet to open, refresh and close. To close Excel I have been using the taskkill command in my batch script, but I have found that using this method doesn't update the sheet properly - the sheet only updates properly if I close Excel in the correct way, but this means manual input and I want this to be automatic.

View 3 Replies View Related

Open And Close File With No Extension

Feb 21, 2013

I opened a file, by file I mean a "file" with no extension that has commas separating it.

So I recorded a macro to open up the file and this works fine.

How do I close it? I tried

wk1 = ThisWorkbook.Name 'Main Worksheet
...
Workbooks(wk1).Activate
ActiveWindow.Close

But the problem is that "wk1" is not set equal to the name, probably because the file is not an excel file, it has no extension.

View 2 Replies View Related

Grouping Open / Close Shortcut?

Apr 30, 2013

I know the shortcut for grouping columns in excel is Alt + D + G + G or Shift + Alt + Right Arrow. Is there a shortcut to actually open and close grouped columns (i.e., the equivalent of clicking on the + sign at the top) without using the mouse?

View 1 Replies View Related

Close Open Instance Of Excel

Aug 6, 2013

I use the following code to open an existing open workbook in a new instance of Excel...

Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...

Public Sub Re_open_workbook()
Application.DisplayAlerts = False
ThisWorkbook.Save

[Code].....

View 1 Replies View Related

Automatically Open And Close All Groupings?

Apr 10, 2014

Is there a way to automatically open or close all grouped rows on a specific worksheet or for specific rows?

View 5 Replies View Related

Open Only The User Form ... Or Close Everything

Mar 22, 2006

Assume that the workbook Book1.xls opens a user form named myUserForm.

I'm trying to show only the user form on the desktop when I open Book1.xls, with Book1.xls minimized on the Taskbar.

And , if the Cancel button on the form is clicked, then unload the form and close Book1.xls.

That simple!

In ThisWorkbook, I have the event:

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
ShowTheForm
End Sub
On the Form, I have the Cancel button:

Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
End Sub

The above procedure shows the form, with Microsoft Excel blank window in the background, and produces a small bar at the bottom left of the screen for Book1.xls
Click the Cancel button, and both the Form and Book1.xls close, but the blank Excel window remain open !

I suppose I would not be able to edit this version of Book1.xls, but that is fine for now!

View 9 Replies View Related

Open, Hide, Unhide, Close

Oct 22, 2008

this has to be the easiest thing ever. I want to (on opening one file) open another, and hide it (the second file), and then when closing the first file, I want to close the second file as well....

View 9 Replies View Related

Checking If File Open.txt Then Close

Feb 25, 2009

I need to check if a .txt file is open. If yes, need to close it.

View 9 Replies View Related

Open All Files In Folder & Close

Dec 29, 2006

I am trying to append 365 text files into a single Excel sheet. The files have unique names corresponding to the day of data that they represent (i.e. 0101.txt, 0102.txt, 0103.txt etc.) I am looking for some code that will loop through a folder, open each file, copy all data, and append it without the headers into the single Excel sheet. Does anyone have some VBA code that would do this?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved