listing a file name when the file is missing

[rholdren]

Goo Morning
(Using Excel 365)
I have a piece of code that reads a path and file name in column a then goes out and opens that file and copies a specific range. Then it reads a corresponding file name in column b goes out and opens that file and pastes the info if got from the file in column A. When someone has the file open in column B it puts that file name in column a of a different worksheet. This works very nicely except if the file is not present it does not list then name of the file and it closes the file down (the one with the code) and doesn't save the file. Any thoughts on this would be greatly appreciated.

Sub Post_7Months()



'
Application.ScreenUpdating = False
Sheets("7MONTHS").Select
'
Dim columnX As Range, cell As Range
Set columnX = Range("A2:A4")




Dim path1, path2 As String
Dim FileOpen As String
Dim ifilenum As Long

For Each cell In columnX




ChDir "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Original Report Data"

'copies current cell value into path1
path1 = cell.Value
Workbooks.Open Filename:=path1

Range("A2:M13").Select
Selection.Copy

Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True


ChDir "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Reports To Be Sent"

'copies the vaule of the offset to thr right cell into path2
path2 = cell.Offset(0, 1).Value
'If Not IsFileOpen = path2 Then
'Workbooks.Open "C:\MyTest\volker2.xls"
On Error Resume Next
ifilenum = FreeFile()
Open path2 For Input Lock Read As #ifilenum
Close #ifilenum
Application.CutCopyMode = False
If Err.Number <> 70 Then 'file is close
Workbooks.Open Filename:=path2
Sheets("Detail").Select
Range("A5:M16").Select
Application.DisplayAlerts = False
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Application.DisplayAlerts = True
Range("B5:B17").Select
Selection.NumberFormat = "0"
Columns("B:B").EntireColumn.AutoFit
Range("A3").Select
Sheets("Summary").Select
ActiveWorkbook.Save
ActiveWindow.Close
Else

Sheets("Dashboard").Select

Range("A2").Select
'Range("d8").Value = path2
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = path2
'ActiveCell.PasteSpecial Paste:=xlPasteValues


End If


Next cell
'
'
Exit Sub
Application.DisplayAlerts = False
Range("E3").Value = "Process Complete"


Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Range("d8").Value = path2
ActiveCell.PasteSpecial Paste:=xlPasteValues


'
'
Resume Next ' go back to the line following the error

End Sub

 

[rollis13]

Just to keep you going utile you decide what to do when a receiving file isn't found.
Add this line to this part of the macro:

'...
For Each cell In columnX
    ChDir "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Original Report Data"
    'copies current cell value into path1
    path1 = cell.Value
    If Dir(path1) = "" Then GoTo NoReceivingFile           '<- added
    Workbooks.Open Filename:=path1
'...

and this other here:

'...
        End If
NoReceivingFile:                                  '<- added
    Next cell
'...

This code will make your macro skip the processing of the missing file.

 

[rholdren]

Just to keep you going utile you decide what to do when a receiving file isn't found.
Add this line to this part of the macro:

'...
For Each cell In columnX
    ChDir "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Original Report Data"
    'copies current cell value into path1
    path1 = cell.Value
    If Dir(path1) = "" Then GoTo NoReceivingFile           '<- added
    Workbooks.Open Filename:=path1
'...

and this other here:

'...
        End If
NoReceivingFile:                                  '<- added
    Next cell
'...

This code will make your macro skip the processing of the missing file.

That works really well as far as not closing the worksheet but if the file isn't found I do get the error. I need it to copy the name in column A of the dashboard and then keep going. The above works really well if the file is open but not if it is missing. On the bright side I learned something new here.
after i click on OK it closes the file without saving

 

[rollis13]

The above works really well if the file is open but not if it is missing.

This statement is incorrect because the check If Dir(path1) = "" Then GoTo NoReceivingFile '<- added is done before opening the file Workbooks.Open Filename:=path1 .

after i click on OK it closes the file without saving

This probably is a completely different problem, the indicated path has nothing to do with the paths in your macro in post #1:
"S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Original Report Data" or "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Reports To Be Sent"

I need it to copy the name in column A of the dashboard and then keep going.

To have a feedback in sheet Dashboard when Receiving file is missing change this parte of the macro:

'...
'copies current cell value into path1
path1 = cell.Value
'--- check if file existes, if not add to Dashboard ----------------------
If Dir(path1) = "" Then
    Sheets("Dashboard").Select
    Range("A2").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell = path1 & " - NOT FOUND"
    GoTo NoReceivingFile
End If
'--- end check -----------------------------------------------------------
Workbooks.Open Filename:=path1
'...

 

[rholdren]

This statement is incorrect because the check If Dir(path1) = "" Then GoTo NoReceivingFile '<- added is done before opening the file Workbooks.Open Filename:=path1 .

This probably is a completely different problem, the indicated path has nothing to do with the paths in your macro in post #1:
"S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Original Report Data" or "S:\ReportingDepartment\ReportingAnalyst\Projects\REX\Reports To Be Sent"


To have a feedback in sheet Dashboard when Receiving file is missing change this parte of the macro:

'...
'copies current cell value into path1
path1 = cell.Value
'--- check if file existes, if not add to Dashboard ----------------------
If Dir(path1) = "" Then
    Sheets("Dashboard").Select
    Range("A2").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell = path1 & " - NOT FOUND"
    GoTo NoReceivingFile
End If
'--- end check -----------------------------------------------------------
Workbooks.Open Filename:=path1
'...

I believe it found a missing file from column A (which hasn't happened before). I'm getting ready to make the recommended changes now. I really appreciate the input

 

[rholdren]

I believe it found a missing file from column A (which hasn't happened before). I'm getting ready to make the recommended changes now. I really appreciate the input

I'm getting a type mismatch error on the If Dir(path1) = "" Then

 

[rollis13]

That's because variable 'path1' is empty. Most likely, or there is no data in A2:A4 or your active sheet isn't '7MONTHS'.
It's up to you to add error checking to your macro.