listing a file name when the file is missing

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
140
Office Version
  1. 365
  2. 2019
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.

1662046701704.png


1662046814485.png


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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
VBA Code:
'...
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:
Code:
'...
        End If
NoReceivingFile:                                  '<- added
    Next cell
'...
This code will make your macro skip the processing of the missing file.
 
Upvote 0
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:
VBA Code:
'...
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:
Code:
'...
        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. 1662077268563.png
after i click on OK it closes the file without saving
 
Upvote 0
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 .
1662077268563.png
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:
VBA Code:
'...
'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
'...
 
Upvote 0
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:
VBA Code:
'...
'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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top