Make Cells Visible Only If Another File Exists

**gwest** · 06-24-2013, 11:23 AM

I have a sheet that summarizes data for several scenarios and displays them. Currently, the cells for all scenarios constantly display some data, the data that were the last to be saved as each respective scenario. However, I only want the data to display in the cells if there is actually a corresponding scenario saved in the same path as the current workbook.

My thoughts tell me this would need to be a macro that runs upon the opening of the current workbook. Then it would need to re-execute anytime a new scenario is saved while the current workbook is open (The SaveScenario macro runs from the current workbook, creates the scenario workbook, and copies the scenario summary data into the cells in question).

Please Help.

Thank you very much.

**ragulduy** · 06-24-2013, 11:50 AM

As a starting point, you could probably use a function like this:

Function file_exists(file_name As String)
Dim fs, fil, fol
Set fs = CreateObject("Scripting.filesystemobject")
Set fol = fs.getfolder(ThisWorkbook.Path)
Do
    For Each fil In fol.Files
        If fil.Name = file_name Then Exit Do
    Next
    file_exists = True
Loop
If Not file_exists = True Then file_exists = False
End Function

to check if the workbook exists.

Then something along the lines of:

if file_exists("Workbook Name") = false then 
   'hide data
else
   'show data
end if

Make Cells Visible Only If Another File Exists

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Make Cells Visible Only If Another File Exists

Re: Make Cells Visible Only If Another File Exists

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions