macro: close this workbook and open it again

[erik.van.geit]

Hi, guys,

Can we close and reopen Thisworkbook with code ?
something like

wbn = ThisWorkbook.Name
ThisWorkbook.Close False
Workbooks.Open Filename:=wbn

which doesn't work

kind regards,
Erik

 

[tactps]

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]

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

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]

maybe this

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]

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

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.

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]

HAHAHAHA!!!


really funny
HELLO AGAIN

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


Erik