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!

 

[Smitty]

See: http://www.mrexcel.com/board2/viewtopic.php?t=71029&highlight=run+macro+onefor some pointers on how to set your flag.

Why isn't the path consistent between machines?

Smitty

 

[Paperclipper]

thanks! i'll check it out.

the path is different because i am writing the macro while the workbk sits on teh network drive (layers and layers of subdirs) and sometimes my boss takes it onto his personal computer.

so if i have it hardcoded, it bombs!

also, the link across the workbks is using hte old path too!?

i need help!

 

[Smitty]

So it sounds like you'll need to perform some logical tests to see if the File & Path exist. Here are some functions (courtesy of John Walkenbach) that will do that:

Function FileExists(fname) As Boolean
  If Dir(fname0 <> "" Then _
     FileExists = True _
  Else FileExists = False
End Function

Function PathExists(pname) As Boolean
  '  Returns True if the Path exists
  Dim x As String
  On Error Resume Next
  x = GetAttr(pname) And 0
  If Err = 0 Then PathExists = True _
  Else PathExists = False
End Function

Better ye, get your Boss to stop mucking about with the workbook. If you're on a network, you should be able to change the settings so that only you can modify it.

Hope that helps,

Smitty

 

[Paperclipper]

hmm... to clarify, it's something that i have to load onto different machines, and the path will be different (especially if its on a laptop off the network.)

boss will need to be able to click on macro button that opens the source files (so that he doesnt have to open them individually - there's too many and even though zipped together, this makes it easier for him and others who dont know which are needed...)

so question remains on how to get it to open workbks that are in the initiating workbk's folder without predefined paths?:

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

 

[Smitty]

Take a look at the FileSearch Object:

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\My Documents"
    .SearchSubFolders = True
    .FileName = "Run"
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
End With

The VBA help file documents it pretty well.

Smitty

 

[Paperclipper]

hmm... thanks Smitty! but i wouldnt even know what subdir to search for! for instance, my boss might save it to disk and take to any computer and save all the files to any subdir.

that's why i thought it would be natural to look at the current directory or at most look in a subfolder within that folder (whatever that happened to be).

am i missing something here?

 

[tactps]

Another option is to have an input box for the directory, but that would require the user to know where the files are located and to type the path correctly.

The final option would be to look through all mapped drives, but this would be slow and tedious.

 

[Nimrod]

The following is a demo of how to test if a book is open or not..

The demo checks to see if a Contracts Breakdown 1980.xls is open ...

Public Sub BookOpenDemo()
If bBookOpen("Contracts Breakdown 1980.xls") Then
MsgBox "Book Open"
Else
MsgBox "Book Closed"
End If
End Sub


Function bBookOpen(wbName As String) As Boolean
On Error Resume Next
bBookOpen = Len(Workbooks(wbName).Name)
End Function

 

[Nimrod]

The following example test to see if book is already open or not ... if book not open then it searches the MasterBooks current directoy and subdirectories to find the requested book . If book found it is opened...


Public Sub DemoOpenBook()
' if the book is not open then look in all subdirectories until found
' and open it

If Not bBookOpen("Contracts Breakdown 1980.xls") Then
If OpenBook("Contracts Breakdown 1980.xls") Then
MsgBox "Book has been found and opened"
Else
MsgBox "Book was not found in any subdirectory"
End If

End If
End Sub


Function OpenBook(Bk) As Boolean
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = True
.Filename = Bk
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
If .FoundFiles.Count > 0 Then
FullPath = .FoundFiles(1)
OpenBook = True
Workbooks.Open Filename:=FullPath
Else
OpenBook = False
End If
End With
End Function

Function bBookOpen(wbName As String) As Boolean
On Error Resume Next
bBookOpen = Len(Workbooks(wbName).Name)
End Function