Closing and re-opening the same file in excel

**thompsy121** · 10-15-2008, 04:16 PM

Hi All,

I was wondering if there is any code using an on-click method. When clicking a button i need excel to exit the file/sheet without saving (no prompts) and then re-open the exact same file/sheet once it has closed.

In a way this is like a refresh button.

Advice or code to help me would be appreciated as VBA is not my strong point.

Thanks

**Stuie** · 10-15-2008, 04:35 PM

Hi thompsy121,

To close a work book you can use the following code

Please Login or Register  to view this content.

To ensure no prompts apear you can use this

Please Login or Register  to view this content.

and finally to open a workbook you can use this

Please Login or Register  to view this content.

so final code looks like this

Please Login or Register  to view this content.

Please note that when you turn display alerts off it will use the default value as the answer (in this case it is no, so the workbook will not save).

**thompsy121** · 10-16-2008, 05:51 AM

Hi thanks for that,

The form closes ok, but somehow it does not open back up. I have checked the file name path and that is fine????

**royUK** · 10-16-2008, 06:16 AM

Once you have closed the workbook the macro will stop running, so it cannot re-open itself.

Create a Template workbook instead

http://www.excel-it.com/excel_templates.htm

**thompsy121** · 10-16-2008, 08:48 AM

thanks for that,

Is there possibly another way where i can just close down the worksheet and then re-open it leaving Microsoft Excel open????

Thanks

**royUK** · 10-16-2008, 08:50 AM

The macro is in the workbook, when the workbook closes the macro stops running. You could possibly put the code into an addin or PERSONAL>xls

**thompsy121** · 10-16-2008, 09:30 AM

The reason i need this code is i have a workbook that links data from another workbook which is updated daily. The workbook i need the code on will be displayed on a central computer via a plasma screen.

What i need is where workbook 1 is updated, i need workbook 2 to update while being on screen.

Can this be done automatically? If not, is there some code i can use to refresh workbook 2 via an on-click method?

Help on this would be really appreciated.

Thanks

**Stuie** · 10-16-2008, 09:33 AM

If you used links you cud make it refresh the links to show the updated data.

or could you not have another workbook open that will close and re open the other workbook. if you turn off screenupdating while closing and opening you would hardly see it do anything but change the data on the sheet

and sorry about my code earlier i wasnt thinking when i came up with it. lol

**thompsy121** · 10-17-2008, 03:29 AM

Do you know any good code i could use as an automatic refresh every X seconds, or an on-click refresh?

Thanks

**Stuie** · 10-17-2008, 04:05 AM

to get a time in Excel the only way that i could think of would be to use an infinate loop that checks the time and then when the time is in x number of seconds execute the code and then start all over.

if not another way to open and close files every x seconds would be to write a .bat file i think would do it (im not 100% sure) i dont have much experiance with them

**thompsy121** · 10-18-2008, 06:45 AM

Originally Posted by Stuie

to get a time in Excel the only way that i could think of would be to use an infinate loop that checks the time and then when the time is in x number of seconds execute the code and then start all over.

if not another way to open and close files every x seconds would be to write a .bat file i think would do it (im not 100% sure) i dont have much experiance with them

Thanks for that Stuie, does any1 else know how to do this what Stuie suggested?

Thanks

**ravishankar** · 10-18-2008, 12:35 PM

Hi
You can probably use scheduled tasks from control panel. It opens excel file at preset time. How frequently you want it refreshed? In otherwords what is the value of X
Ravi

**thompsy121** · 10-19-2008, 07:12 AM

Originally Posted by ravishankar

Hi
You can probably use scheduled tasks from control panel. It opens excel file at preset time. How frequently you want it refreshed? In otherwords what is the value of X
Ravi

I would need it refreshed every 5 seconds if possible.

**Stuie** · 10-19-2008, 07:23 AM

if you are considering a picece of third pary software, i have experiance with VB.net and could put together a small app that will take the file name and file path, open it up and then close and re-open every 5 seconds this wouldnt be too difficult at all the only problem being i only have office 2007 and i dont have the class library for off 2003

**thompsy121** · 10-19-2008, 08:00 AM

Thanks for the offer Stuie,

But i've just looked over the last part of formula you suggested.

so final code looks like this

Please Login or Register  to view this content.

I have only used the following part - Workbooks.Open "Place file name here" 'Opens a workbook
Application.DisplayAlerts = True 'Turns alerts back on[/CODE]

As this league workbook is a view only table, there is no need to save any updated figures. Ive tried it and i think it works. Do you think this is an ok way to do it.

If the file is already open, this in a way forces a close and then re-opens.

Thanks

**Stuie** · 10-19-2008, 08:05 AM

yea i cant see any problem or errors occuring as you are only opening the workbook up.

are you using imported data??

**thompsy121** · 10-19-2008, 08:08 AM

Im just using sumproduct formulas to pull data from another workbook which does not need to be opened.

I think this is working fine

**Stuie** · 10-19-2008, 08:12 AM

ok then, good luck with it

if you need any further help i wil happily help

**thompsy121** · 10-19-2008, 08:17 AM

Will do mate, thanks for all your advice

**Stuie** · 10-19-2008, 08:19 AM

no probs glad to help

**ravishankar** · 10-19-2008, 09:11 AM

HI
Try the following codes

Please Login or Register  to view this content.

Save this macro in a new file and run it.Enter the filename and filepath to be opened and closed. check if it is getting refreshed each time or not.
Ravi