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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
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?
 

mikebecker

Board Regular
Joined
Mar 28, 2004
Messages
227
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.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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
 

Neil54

New Member
Joined
Jan 24, 2005
Messages
17
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
 

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
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
 

Forum statistics

Threads
1,147,626
Messages
5,742,214
Members
423,714
Latest member
ftp2jz

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
Top