Reopen workbook
 

Hello,

I want to reopen a workbook trough VBA without saving.

But when I close then I lose my code and it does'nt continue.

I've tried this

Dim Pad As String
Pad = ActiveWorkbook.Path
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad


Thx for helping me out

Weeepie

Reopen workbook
 

Weeepie,

Use FullName, not Path:

Dim Pad As String
Pad = ActiveWorkbook.FullName
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad

As long as the activeworkbook is not the book with the code, that should
work well.

HTH,
Bernie
MS Excel MVP


"Weeepie" wrote in message
...
Hello,

I want to reopen a workbook trough VBA without saving.

But when I close then I lose my code and it does'nt continue.

I've tried this

Dim Pad As String
Pad = ActiveWorkbook.Path
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad


Thx for helping me out

Weeepie

Reopen workbook
 

That's the problem, it should reopen the same workbook.

Isn't it possible to use a bypass. Like creating a new workbook to so I can
close en reopen from that one?

Weeepie



"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Weeepie,

Use FullName, not Path:

Dim Pad As String
Pad = ActiveWorkbook.FullName
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad

As long as the activeworkbook is not the book with the code, that should
work well.

HTH,
Bernie
MS Excel MVP


"Weeepie" wrote in message
...
Hello,

I want to reopen a workbook trough VBA without saving.

But when I close then I lose my code and it does'nt continue.

I've tried this

Dim Pad As String
Pad = ActiveWorkbook.Path
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad


Thx for helping me out

Weeepie

Reopen workbook
 

Maybe you can use application.ontime to run a do-nothing macro. As long as
excel is open (not necessarily your workbook), excel will reopen a workbook to
run that macro.

Option Explicit
Sub testme()

Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & ThisWorkbook.Name & "'!DummyMac"

ThisWorkbook.Close savechanges:=False

End Sub
Sub DummyMac()
'uncomment this line for testing???
'MsgBox "hi"
End Sub

Depending on your security settings, you may get prompted to allow macros to
open.

You may want to take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx


Weeepie wrote:

Hello,

I want to reopen a workbook trough VBA without saving.

But when I close then I lose my code and it does'nt continue.

I've tried this

Dim Pad As String
Pad = ActiveWorkbook.Path
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Pad

Thx for helping me out

Weeepie

--

Dave Peterson

Reopen workbook
 

This is working.

But each time I restart the tool I get this message
"The macro ,path and name!DummyMac, can' be executed. The macro is not
available etc...."

But the other macros or working .

Is it possible to disable this message

I've tried displayalerts = false but it returns anyway.

Weeepie



"Dave Peterson" schreef in bericht
...
Maybe you can use application.ontime to run a do-nothing macro. As long
as
excel is open (not necessarily your workbook), excel will reopen a
workbook to
run that macro.

Option Explicit
Sub testme()

Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & ThisWorkbook.Name & "'!DummyMac"

ThisWorkbook.Close savechanges:=False

End Sub
Sub DummyMac()
'uncomment this line for testing???
'MsgBox "hi"
End Sub

Depending on your security settings, you may get prompted to allow macros
to
open.

Reopen workbook
 

Did you add the macro named dummymac to your workbook's project?

It would be the simplest solution.

And make sure that that macro is in a General module--not under ThisWorkbook or
behind a worksheet.

Weeepie wrote:

This is working.

But each time I restart the tool I get this message
"The macro ,path and name!DummyMac, can' be executed. The macro is not
available etc...."

But the other macros or working .

Is it possible to disable this message

I've tried displayalerts = false but it returns anyway.

Weeepie

"Dave Peterson" schreef in bericht
...
Maybe you can use application.ontime to run a do-nothing macro. As long
as
excel is open (not necessarily your workbook), excel will reopen a
workbook to
run that macro.

Option Explicit
Sub testme()

Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & ThisWorkbook.Name & "'!DummyMac"

ThisWorkbook.Close savechanges:=False

End Sub
Sub DummyMac()
'uncomment this line for testing???
'MsgBox "hi"
End Sub

Depending on your security settings, you may get prompted to allow macros
to
open.


--

Dave Peterson

Reopen workbook
 

Hi Dave,

I replaced dummymac to a module.

Now it's working perfect.

Thank you verymuch for your support.

Weeepie




"Dave Peterson" schreef in bericht
...
Did you add the macro named dummymac to your workbook's project?

It would be the simplest solution.

And make sure that that macro is in a General module--not under
ThisWorkbook or
behind a worksheet.

Weeepie wrote:

This is working.

But each time I restart the tool I get this message
"The macro ,path and name!DummyMac, can' be executed. The macro is not
available etc...."

But the other macros or working .

Is it possible to disable this message

I've tried displayalerts = false but it returns anyway.

Weeepie

"Dave Peterson" schreef in bericht
...
Maybe you can use application.ontime to run a do-nothing macro. As
long
as
excel is open (not necessarily your workbook), excel will reopen a
workbook to
run that macro.

Option Explicit
Sub testme()

Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & ThisWorkbook.Name & "'!DummyMac"

ThisWorkbook.Close savechanges:=False

End Sub
Sub DummyMac()
'uncomment this line for testing???
'MsgBox "hi"
End Sub

Depending on your security settings, you may get prompted to allow
macros
to
open.


--

Dave Peterson