Macro needed to open first time only and close no saving

[Paperclipper]

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!

 

[tactps]

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]

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]

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]

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]

You could do soemthing simple like adding a check to your macros in a blank cell (A1 in my example below):

Opening macro:

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 to close workbooks
Range("A1").Select
    Selection.ClearContents

 

[Paperclipper]

wow! 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]

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

 

[tactps]

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]

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!