macro: close this workbook and open it again

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, guys,

Can we close and reopen Thisworkbook with code ?
something like
Code:
wbn = ThisWorkbook.Name
ThisWorkbook.Close False
Workbooks.Open Filename:=wbn
which doesn't work

kind regards,
Erik
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I don't think so, but am curious as to why you would want to do this?

The only way I could think of would be by public functions in the personal workbook, but I couldn't even begin to be able to code it.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
tactps,

Thanks for your attention.
but am curious as to why you would want to do this?
I regularly need to paste some large PDF-files into a datasheet of an excelfile. This files contain large ratelists, but are saved in different ways. Data are sometimes clustered together, some cells are not well ordered or contain unnecessary information...
It's impossible to run a "fixed" macro on those tables, instead some "semi-manual" things have to be done. Therefore I've made a workbook with several buttons to transpose tables, delete rows, cut parst of cells and paste them in adjacant ones. This is reducing my work from about 30-45 minutes to about 4-5.
BUT: :oops: after clicking buttons you can't undo unless yo make a copy of the page before each buttonclick or code some other complicated stuff OR ...
start each macro ThisWorkbook.Save
if there is a problem ==> Close without changes and reopen :p

If this is impossible the most simple way would then be for me to make a copy of the values on another sheet at the start of each macro.

Kind regards,
Erik
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348
maybe this

Code:
Sub test_code()
' this code in another workbook
Windows("book1.xls").Activate ' name of the book to be closed - must be saved to that fullname can be returned
xx = ActiveWorkbook.FullName
ActiveWorkbook.Close False
Workbooks.Open Filename:=xx

End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

pcc,

Yes! That's an option. Thank you.
(although it's not my favorite job to work around the problems this way)

I've never used it but can we make "general buttons independent from any workbook" and then run macros with them? If anybody can tell me how to search on the Board - how are called those "external macros" ...

kind regards,
Erik
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi erik,

Two options occur to me:

1. In your Personal.xls (or an add-in) have a routine that creates a custom menu. On this custom menu have a button that calls a routine that will do the Close and Open on the ActiveWorkbook (probably best to put a warning message in here in case somebody else uses the routine and loses a load of information!).

2. Use the OnTime event.
Code:
Sub GoodbyeAndHello()
    Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
    ThisWorkbook.Close savechanges:=False
End Sub

Sub OpenMe()
    'MsgBox "Hello again"
End Sub
HTH
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
HAHAHAHA!!!
:p :p :p

really funny
HELLO AGAIN

thank you Richie
so i'ts possible !!!
glad to have you here!

(y)
Erik
 

Forum statistics

Threads
1,147,620
Messages
5,742,177
Members
423,708
Latest member
vagosh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top