Macro needed to open first time only and close no saving

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
Hi everyone. I'm trying this out for the first time...

I have a spreadsheet that pulls in data from other sheets (the source datafiles). In this summary sheet, I have a listbox that can do scenario analyses.

So I need two macros:
1) opens the associated source datafiles the first and only the first time
that I execute the macro, since the files will be left open in case there are more recalculations (bearing in mind that this file will be used on different computers so the path should point to the current directory that the summary sheet has been saved to, and thus NOT a full programmer-specified path, and NOT a user-defined one either.)

2) closes the associated source datafiles without saving them

Thank you all very very much!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Welcome to the board.

The best way to do this is to record a macro which opens the files required, and then another to close them without saving.

If the workbook is open, the macro should not try to open them again.

Do the files that you want to open change (either location or name)? If so, how do you want the macro to determine which files should be opened?
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
Thanks for the welcome! :)

on open: it tries to reopen the files when they are already open and telling me that the copy is already open, asks if i want to reopen.

on close: recording the macro that closes still asks me if i want to save before closing.

here is the code that i have so far:

Sub OpenUp()
' need to set a flag so that it will only do it once... how to set that up??
If flagg = 3187 Then
Exit Sub

End If
' need to make sure its the right path, (should be in same directory as the summary file or in the subfolder of the directory with the summary file.)
ChDir _
"S:\Contracts\Contract Data Sources"
Workbooks.Open Filename:= _
"Contracts Breakdown 1980.xls" _
, UpdateLinks:=0
' ...
Workbooks.Open Filename:= _
"Contracts Breakdown 1990.xls" _
, UpdateLinks:=0
Windows("Contract Breakdown Summary_Macro_Enabled.xls").Activate
End Sub


Sub CloseUp()
' as they are closing, still asks if i want to save
Windows("Contracts Breakdown 1980.xls").Activate
ActiveWorkbook.Close
' ...
Windows("Contracts Breakdown 1990.xls").Activate
ActiveWorkbook.Close
End Sub
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Try this:
Before code:
Application.DisplayAlerts=false

After code:
Application.DisplayAlerts=true

This will stop the alerts coming up.

I am not sure if this will automatically reopen files if already open, or if this is even a problem. If so, repost and advise.
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110

ADVERTISEMENT

great! :) that solved the "save option boxes" upon closing...

but by bypassing the "Re-open files?" boxes, Excel auto-reopens the source files... and that isnt what I want when I have a couple dozen files...

cant I make set a variable and set a flag for when the source files are opened the first time? then if the flag is set, it bypasses the open sub and wont run a second time?

Thanks for your help! :)
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
You could do soemthing simple like adding a check to your macros in a blank cell (A1 in my example below):

Opening macro:
Code:
macro()
range("A1").select
if activecell.value <> 1 then
Code to open macro
endif
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
end sub

Closing macro:
Code:
    Code to close workbooks
Range("A1").Select
    Selection.ClearContents
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110

ADVERTISEMENT

wow! :biggrin: hey that was exactly what I was thinking, except i forgot how to script it! (its been a while since first learning vba!) thanks!

one last thing though... is this necessary? i tried to set the flags inside the macros themselves.. is this possible?

this is the code (2 subs) i had tried and didnt work:

---------------------------------------------------------------------
Sub ScenChange()

' Does this Initialize a variable? and set it to a unique number?
Dim flagg As Integer
flagg = 775

' This should open the files the first time and then set the flag
OpenUp

' Recalculate the workbks now that the source datafiles are open
Calculate

' Does this set the flag so that subsequent operation will bypass
' the open sub the next time? Or Does this NOT stay in Excel's memory?

flagg = 3187

End Sub
---------------------------------------------------------------------

Sub OpenUp()

' Can this work to see if flag has been set, (and if so, unnecessary to
' continue opening files sub)

If flagg = 3187 Then
Exit Sub
End If

' the path will change for each machine i load this on... so how do i code
' to open up the source workbks in either the current dir or a subfolder
' within it?

ChDir _
"S:\Contracts\Contract Data Sources"
Workbooks.Open Filename:= _
"S:\Contracts\Contract Data Sources\Contracts Breakdown 1980.xls" _
, UpdateLinks:=0

' Return to initiating workbk
Windows("Contract Breakdown Summary_Macro_Enabled.xls").Activate

End Sub


THANKS SO MUCH!!!
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
The reason is that each sub is individual (ie the "flagg" is undefined in sub openup).

To do this you need to make it "public", but you need a smarter bunny than I to do this! (any MVP's interested in 10 seconds of work???)
:cool:
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Another method is to use this:

If Workbooks.Count > 4 Then
Exit Sub
End If

This will count the workbooks that are currently open
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
thanks! i will stick with teh first until i learn public variables...

But here is the most important question I still have:

' the path will change for each machine i load this on... so how do i code
' to open up the source workbks in either the current dir or a subfolder
' within it?

ChDir _
"S:\Contracts\Contract Data Sources"
Workbooks.Open Filename:= _
"S:\Contracts\Contract Data Sources\Contracts Breakdown 1980.xls" _
, UpdateLinks:=0

also, the formula link btwn each spreadsheet is looking in that old path when they are saved onto another machine? does the zipping up of the file and resaving break the link somehow?

Thank you!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,054
Members
425,451
Latest member
JohnBrooksBiddle

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