Append results

Stuart,

The following code applies the technique that you are looking for, I think. This code displays a message box that tells the user the path for the active workbook as well as the paths for other workbooks that are open. The basic idea is to create a string variable that keeps getting more data added to it. I suggest you copy this code into a blank workbook and step through it to see what it does. I think you'll then be able to easily modify it to suit your needs.

enjoy

Sub File_Path()

Dim strPath As String
Dim strActiveName As String
Dim strFullName As String
Dim wkbk As Workbook
Dim strMsg As String

strMsg = "The following workbooks are also open:" & Chr(13)

For Each wkbk In Workbooks
If UCase(wkbk.Name) = "PERSONAL.XLS" Then
strMsg = strMsg
ElseIf wkbk.Name <> ActiveWorkbook.Name Then
strMsg = strMsg & Chr(13) & Chr(9) & wkbk.FullName
ElseIf wkbk.Name = ActiveWorkbook.Name Then
strMsg = "The following workbook is active:" & Chr(13) & Chr(13) & Chr(9) & ActiveWorkbook.FullName & Chr(13) & Chr(13) & Chr(13) & strMsg
End If
Next wkbk

MsgBox strMsg

End Sub

ps: have more than one workbook open to get the full "effect". (I've since modified this code so that the "The following workbooks are also open" phrase only appears when there are in fact other workbooks open, but that modified code is on my machine at work. This code will still illustrate the technique though.)

Thanks for the info, but it doesn't quite solve my problem (I am having trouble adapting it to my needs). What I am after, is a message box, that states: Reports not received: Adelaide, Burnside, Darwin...etc.

The test for the file I have:

If Dir("G:\Member Relationships\Weekly Reports\Branch Reports\Adelaide.xls") = vbNullString Then

It is the message box I am having trouble with - I can not get the names to append after each other.

Thanks
Stuart MsgBox strMsg End Sub

: I am trying to set up a message box that tells me which reports I have received at a particular time. I can search for a file, and if it is there, copy some info from it, but all I can come up with if the file is not there is a separate message box for each missing file. Can I somehow append the names of the missing files, and have 1 summary message at the end of processing, telling me which are missing? : Thanks in advance.

Stuart,

Sorry it's taken so long for me to get back to you - the day job got in the way.

It looks like you're looping through your files to find which ones aren't there. Before you get into the loop, initialize your message variable like this:

MyMessage = "Reports not received: "

Then, once you find out that a report hasn't been received, your code would look like:

MyMessage = MyMessage & Filename & ", "

You would just use the above code line at each place in your If statements where you've found that a file doesn't exist.

If you step through the code I posted previously, and check the value of the variable strMSG as it is "built" (that is, as more data is added to it) I think you will see how this works.

To clarify some of the code: Chr(13) makes the text move to another line and Chr(9) is a "tab" so that the text becomes indented.

Hopefully though by now you've already got this all figured out.

take care

Thanks for the info, but it doesn't quite solve my problem (I am having trouble adapting it to my needs). What I am after, is a message box, that states: Reports not received: Adelaide, Burnside, Darwin...etc. The test for the file I have: If Dir("G:\Member Relationships\Weekly Reports\Branch Reports\Adelaide.xls") = vbNullString Then It is the message box I am having trouble with - I can not get the names to append after each other. Thanks Stuart : Stuart,