Save, Close, Then Reopen Workbook

Oct 2, 2007

I am testing the following section of code.


Sub Macro1()
Dim NewWB As Workbook, NewName As String
Set NewWb = Workbooks.Add
NewName = Application.GetSaveAsFilename(ThisWorkbook.Name)
NewWb.SaveAs NewName
NewWb.Close False
Workbooks.Open (ThisWorkbook.Path & "/" & ThisWorkbook.Name)
End Sub
It seems like I need to rename the sheet I am working on in order for this code to work. If I do not, it tells me that it "cannot save this workbook with the same name as another open workbook".

I am trying to use this code to get around an error with Excel described here: [url]

I would like to have the sheet save itself (not "Save As"), close, reopen, and continue running the macro. What adjustments do I need to make to this code to do this. I have tried different things, but I must be getting the syntax wrong.

View 9 Replies


ADVERTISEMENT

Ontime Causes Workbook To Reopen After Close

Jun 18, 2007

i want vba to show a msgbox every 5 sec & it should start as soon as the file is opened. Below is the code & it works. Problem is, that when i close the file it immediately reopens and starts the "msgbox proess". Only if i close excel completely, this problem goes away (& thats not a very proffessional way to do it!!). Can someone pl look at this one & help ?

Private Sub workbook_open()
Call Macro4
End Sub

Sub Macro4()
Dim i As Integer
MsgBox "test!!"
NextTick = Now + TimeValue("00:00:05")
Application .OnTime NextTick, "Macro4"
End Sub

View 6 Replies View Related

Close And Reopen Workbook From Macro Stored Inside Workbook

Mar 30, 2007

I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?

View 4 Replies View Related

Close And Reopen After A Set Time

Jul 4, 2009

I'm enquiring about this just out of intellectual curiosity.

View 9 Replies View Related

Close / Reopen Excel Using Taskkill

Mar 17, 2014

I have the following code that needs to execute. Basically, every once in a while, our terminal program encounters an error that makes it so Excel and it can't talk to each other. The only way to fix this is by killing the entire process tree of both terminal and Excel, or restarting the computer. I've written this bit of code in the event it starts happening while someone is using the macro - it checks to see if the two can talk, and if they can't, it just kills both programs.

[Code] .....

Is there any way to launch Excel with a 8 second delay, so that once the kill command executes, Excel re-opens? Or could I write a batch file with delay built in to execute prior to the kill command? I could use Shell to execute that prior to killing Excel, but I'm not sure if that's possible....

View 11 Replies View Related

Macro Close Without Saving Reopen

Jan 6, 2010

Using excel 2003, I have a ss that uses filters and sorting. I can get to the data I want but must reopen the workbook if I want to start over and extract different information. I tried installing this VBA:

Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub

Sub OpenMe()
MsgBox "I'm Back!"
End Sub

This is my first time at inserting VBA. Usually I just use keystroke macros. I have set my Excel VB Book options to: Option Explicit double clicked the sheet I was using and under general inserted the code, closed Ctrl+Q, saved workbook. When I reopened and tried to use it all I get is I'm Back! nothing else happens. One time it actually ran and reopened but it appeared that the workbook was saved before closing because it was still sorted. What I need is it to completely unsorted as it is when I first open it so I can start over.

View 4 Replies View Related

Save And Close Workbook Using VBA

Dec 11, 2013

How can i save and close one workbook in excel with vba code.

View 2 Replies View Related

Save Workbook & Close

Jul 8, 2006

One of my computers pulls info from my cash register once everyday (sales info). Then I have a excel monthly sales sheet that pulls the info from the register info and puts it in the right places. So the computer pulls the register info at 9:20pm everyday, then I have my task manager open excel at 9:25 everyday and it has a workbook open macro to automatically pull the info into the excel spreadsheet. Here's my problem-- I need to close it.

I'm sure I could use a workbook close macro, but when it tries to close it won't it come up with a save, don't save, cancel prompt? Nobody will be there to hit SAVE so it won't close? Is there just a macro that could save it then close it?

View 9 Replies View Related

Save Backup Of Workbook Before Close

Dec 15, 2009

I call the macro below from a Workbook_BeforeClose sub; however, if the user has not saved changes before they hit the close then the Sub SaveFile runs and then after the backup save they are then asked do you want to save changes.

I initially had this sub run with Workbook_BeforeSave, but I don't really want to run this everytime the user selects save.

Any ideas on how to how the save changes first and then the backup?

View 6 Replies View Related

Close Workbook Without Save Changes Dialog

Jan 9, 2009

I'm not sure what I'm missing here, but I am trying to exit a spreadsheet using VBA code without saving changes.

The following code, to my mind, should work, but I keep getting a dialog asking if I want to save changes.

'* ensure workbook does not request user to save it...
ThisWorkbook.Saved = True
'* exit Excel...
Application.Quit

View 9 Replies View Related

Save As And Close Active Workbook

May 13, 2009

I am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.

View 9 Replies View Related

Close Workbook Without Save Prompts

Oct 2, 2007

Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.

I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.

View 4 Replies View Related

Macro To Open / Close / Save Workbook?

Mar 14, 2014

I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.

So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).

View 7 Replies View Related

Save Today() As Value To Prevent Changes On Reopen

Jul 30, 2008

I have a spreadsheet that containt a conditional formula to place the current date aka Today() into a cell based on if an entry in the previous cell.

I put in this formula:
=IF(I131>="1",TODAY()," ")

My boss told me to update to this one: (same result)
=IF(I130>="1",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))," ")

BOTH will change the saved sheet data if we open it tomorrow (due to using Today()). We want to retain the date that it was entered on (which will always be the date he puts entry into sheet).

Is there a way to save the sheet and update the code for Today() to the date value so it will appear as a date when it's opened in the future?

View 9 Replies View Related

Open Workbook / Copy / Close Without Offer To Save?

Mar 12, 2014

I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.

The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?

Here's a sample of one of the menu button's code:

Code:

Private Sub CommandButton18_Click()
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx")
With wb.Worksheets("Sheet1")
.Unprotect Password:="pass"
ActiveWorkbook.Worksheets("Sheet1").Copy
End With

[Code].....

View 7 Replies View Related

Open Workbook, Find Last Row & Paste, Save And Close

Jun 13, 2008

What i need to do is Open a Closed workbook (which i have done in the code below)

1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)

2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work. The red is where it hangs up.

openpath = ActiveWorkbook.Path & ""
Set wb = Workbooks.Open(openpath & "Template.xls")
With wb.Sheets("Contractor Info")
. Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select
ActiveCell.Value = Msg1
ActiveCell.Offset(0, 4).Value = Msg2
ActiveCell.Offset(0, 5).Value = Msg3
ActiveCell.Offset(0, 3).Value = Msg5
ActiveCell.Offset(0, 2).Value = Msg4
ActiveCell.Offset(0, 1).Value = Msg6
ActiveCell.Offset(0, 6).Value = Msg7
Application.Calculation = xlCalculationManual..................

View 2 Replies View Related

Macro Not Working Except In One Tab, Must Save And Reopen To Work In Others.

Oct 17, 2009

I have a wierd issue. I have this macro for a workbook i use and if i run the macro in the first tab it runs and works fine. When i select the other tab and run it it gives me a debug error. If i close the worksheet save it and reopen it and run it in the other tab it runs fine.

So in otherwords i have to run the macro in the tab desired, save it , and always close and reopen the file to run it in the other tabs. I know the code is correct because it runs flawlessly without errors but i must be missing something.

Also i currently have the macro to run on the active tab/worksheet. Can i have it run on multiple tabs? i have 15 20 and 30 tabs in this example file but in my real file i have many more milage intervals ( some tabs i would not want to select ). It gets tedious having to select one tab at a time, run the macro, rinse and repeat.

For Example Run Macro First, in the first tab. It works perfectly. Go to the second tab and run the first macro. It gives a debug error. If you save the file and reopen it and go to teh second tab and run the macro again it works.

View 8 Replies View Related

Date - Sheet To Copy The Info To Another Workbook, Save And Close

May 3, 2007

I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.

for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.

the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.

i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.

View 9 Replies View Related

Save Workbook On Workbook Close

Aug 26, 2006

I snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.

What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.

Private Sub CommandButton4_Click()
Dim sAnswer As String
Dim i As Integer

' ****************
' ** Exit Button**
' ****************

When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..

View 9 Replies View Related

Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?

Mar 8, 2013

I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.

View 8 Replies View Related

Auto Save And Close Workbook After User Defined Length Of Idle Time

Dec 5, 2008

I have a workbook, "A", that while open I need to be able to detect any type of activity within other open Excel workbooks "B...Z" that are simultaneously open.

I understand that I would need to use the Class Module with a Public WithEvents function but I am stuck here.

View 5 Replies View Related

ActiveX Combobox - Linked Cell Reference Lost After Save / Close And Re-open Workbook

Feb 11, 2013

Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.

View 13 Replies View Related

Reopen A Workbook

Jul 25, 2008

action
Work Book . save ***
Action
Workbook. save AS.......
Workbook close

I would like to at this point reopen the orignal workbook as saved in the line marked with ***

I think before the save i need to record the location and filename to a variable "file"

then workbook.open = "File"

View 9 Replies View Related

Code, That Disables Save, Save As And Close Commands

Nov 2, 2009

Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

View 2 Replies View Related

Compile Error On Close Of Excel 2003 But Not On Close Of Workbook

Mar 22, 2012

I am using Excel 2003.

It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.

These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.

I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.

These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.

Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.

View 9 Replies View Related

Save And Close In VBA

Nov 22, 2006

I want adding is a bit of code to save the document. When it saves i want the contents of cells c5 and c7 as the name of the saved document.

So it would be saved as c5 - c7

Sub mike()
'
'
Rows("1:1").Select
Range("B1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Cells.Select
Range("B1").Activate
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Windows("HSBC logo.xls").Activate
ActiveSheet.Shapes("Picture 1").Select
Selection.Copy
Windows("Template1.xls").Activate.........

View 9 Replies View Related

Close And Save Application

Dec 5, 2008

When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View 2 Replies View Related

How To Add Save And Close Button

Sep 3, 2013

I know how to add a save and close button but i do not know how to make it instantly available in every new workbook that i open. Currently i click the button and it looks for the macro in the first excel book that i created it in.

how do i make that macro become a default in every workbook?

View 1 Replies View Related

No To Save On Sheet Close

Jul 24, 2006

I have a macro that has to open another worksheet and get data and close the sheet. The problem is when the sheet closes after I have gotten the data it asks if I want to save it. I don't want the user of my macro to see this option because it slows things down. To avoid this in the past I had the worksheet save itself so that it wouldn't ask me to save. Now the worksheet is large and it takes a while to save and it is getting slow. Is there any way not to save it but close it without having excel asking if you want to save it?

View 2 Replies View Related

Save As In Before Close Event

Jan 16, 2007

I need to use the BeforeClose event to save the workbook with the same name when a user closes it. (82 users and they all seem to want to give it their own name.) I have the following code in the BeforeClose event but would like to eliminate the prompt "this file already exists, do you want to replace it" I have tried using the Application.DisplayAlerts = False but this seems to stop the workbook from saving.
I have a public demention called wbName and is set to the workbook name in an outo open macro

Public wbName
Public Sub auto_open()
wbName = ActiveWorkbook.Name

UserForm2.Show
MsgBox "PLEASE do Not insert rows/columns or enter calculations" _
& Chr(10) & " " _
& Chr(10) & "Enter Only Account Name, Date, and Corresponding_ Calls/Details", vbCritical, "Caution"
End Sub

View 6 Replies View Related







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