Open workbook only if workbook exists

Neil54

New Member
Joined
Jan 24, 2005
Messages
17
I currently have a macro that pulls in data from 5 different workbooks that are saved as dates, e.g., 20050117.xls, 20050118.xls, 20050119.xls, 20050120.xls, 20050121.xls. The macro then successfully copies the named cells in each activeworkbook and pastes them accordingly to the named cells in thisworkbook. Obviously the macro breaks if one of the aforementioned files does not exist. How would I amend the macro to continue it to run if if fact one (or more) of the 5 dated files does not exist?

Thanks,
Neil
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Might get away with adding "On Error Resume Next" to the top of your code which says "If the code encounters an error, do not tell me about it and just go to the next line of code". Of course the next line of code might be a problem (i.e. overwriting data). Can you paste the code that you have?
 
Upvote 0
Neil54 said:
I currently have a macro that pulls in data from 5 different workbooks that are saved as dates, e.g., 20050117.xls, 20050118.xls, 20050119.xls, 20050120.xls, 20050121.xls. The macro then successfully copies the named cells in each activeworkbook and pastes them accordingly to the named cells in thisworkbook. Obviously the macro breaks if one of the aforementioned files does not exist. How would I amend the macro to continue it to run if if fact one (or more) of the 5 dated files does not exist?

Thanks,
Neil

Neil,

Could you post your code to get a better understanding what you are working with.
 
Upvote 0
Neil54 said:
I currently have a macro that pulls in data from 5 different workbooks that are saved as dates, e.g., 20050117.xls, 20050118.xls, 20050119.xls, 20050120.xls, 20050121.xls. The macro then successfully copies the named cells in each activeworkbook and pastes them accordingly to the named cells in thisworkbook. Obviously the macro breaks if one of the aforementioned files does not exist. How would I amend the macro to continue it to run if if fact one (or more) of the 5 dated files does not exist?

Thanks,
Neil

Hi Neil, Welcome to the board

Basically if you know the Full Path and File name then just Test if it is there via this method

Code:
Sub Tester()
Dim YouFullpathname As String


YouFullpathname = "C:\Documents and Settings\Administrator\Desktop\20050117.xls"
'
'
'Other code
'
'

'// Check to see if it is there
If Len(Dir(YouFullpathname)) = 0 Then
    '// Not here so tell me
    MsgBox YouFullpathname & " Doesn't exist!", vbCritical, "Error"
    '// get out
    Exit Sub
End If

'
'
'continue with more code

End Sub

OR you could make the routine into a Boolean Function and call it each time you need to see if the file exits ... search this board for File Exists
 
Upvote 0
Here's the code so far (with Ivan's suggestions!) I've defined [PathMon} within the spreadsheet to look up the appropriate path. Ivan's suggestion works, however the macro stops after the message box. I would like to replicate the first part of the code where each day's path is defined; can I define everything at the top or do I have to go in order (define mon, run monday's file, define tues, run tuesday's file, etc.)?

Thanks!

Dim MondayPnL As String
MondayPnL = [PathMon]

If Len(Dir(MondayPnL)) = 0 Then
' File Check
MsgBox PathMon & "Monday's File Does Not Exist!", vbOK, "error"
' Get Out

Exit Sub
End If
Workbooks.Open(Filename:=[PathMon], UpdateLinks:= _
0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[MondayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[MondayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
Exit Sub
Workbooks.Open(Filename:=[PathTues], UpdateLinks:= _
0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[TuesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[TuesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close

Workbooks.Open(Filename:=[PathWed], UpdateLinks:= _
0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[WednesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[WednesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close

Workbooks.Open(Filename:=[PathThurs], UpdateLinks:= _
0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[ThursdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[ThursdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close

Workbooks.Open(Filename:=[PathFri], UpdateLinks:= _
0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[FridayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[FridayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close

End Sub
 
Upvote 0
I think this should work. It checks the directory. If the path is not found then it returns an error and stops the macro. If the directory is found it continues to check to see if the Monday file is found in that directory, if it is, it runs your code for Monday. If it isn't then it just skips it without warning and goes on to Tuesday, etc. You can certainly modify it to return an error for each file that does not exist. This assumes that all files are in the same folder. You could probably modify the code to loop so you do not have to repeat your part 5 times, but I didn't get a chance to look at it..:

Code:
Option Explicit

Sub macro1()

    Dim strPath As String
    Dim strMonPnL As String, strTuesPnL As String, strWedPnL As String, strThursPnL As String, strFriPnL As String
        
    strPath = "c:\mypath\"
    strMonPnL = "20050117.xls"
    strTuesPnL = "20050118.xls"
    strWedPnL = "20050119.xls"
    strThursPnL = "20050120.xls"
    strFriPnL = "20050121.xls"

    'verify strPath ends with \
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    'verify directory exists
    If Not DirExists(strPath) Then
        MsgBox strPath & "The path Does Not Exist!", vbOK, "error"
    End If
    
    
    If FileExists(strPath & strMonPnL) Then 'verify Monday file exists
        Workbooks.Open(Filename:=[strpath & strmonpnl], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
        ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[MondayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[MondayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End If
    
    If FileExists(strPath & strTuesPnL) Then
        Workbooks.Open(Filename:=[strPath & strTuesPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
        ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[TuesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[TuesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End If

    If FileExists(strPath & strWedPnL) Then
        Workbooks.Open(Filename:=[strPath & strWedPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
        ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[WednesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[WednesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End If

    If FileExists(strPath & strThursPnL) Then
        Workbooks.Open(Filename:=[strPath & strThursPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
        ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[ThursdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[ThursdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End If

    If FileExists(strPath & strFriPnL) Then
        Workbooks.Open(Filename:=[strPath & strFriPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
        ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[FridayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
        ThisWorkbook.Sheets("Sheet1").[FridayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End If

End Sub

Function FileExists(sFilePath As String) As Boolean
    If Len(Dir(sFilePath)) = 0 Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function


Function DirExists(strPath As String)
    If Len(Dir(strPath)) = 0 Then
        DirExists = False
    Else
        DirExists = True
    End If
End Function
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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