Excel VBA Open workbook, perform actions, save as, close

After discussion posting updated answer:

Option ExplicitSub test()    Dim wk As String, yr As String    Dim fname As String, fpath As String    Dim owb As Workbook    With Application        .DisplayAlerts = False        .ScreenUpdating = False        .EnableEvents = False    End With    wk = ComboBox1.Value    yr = ComboBox2.Value    fname = yr & "W" & wk    fpath = "C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data"    On Error GoTo ErrorHandler    Set owb = Application.Workbooks.Open(fpath & "\" & fname)    'Do Some Stuff    With owb        .SaveAs fpath & Format(Date, "yyyymm") & "DB" & ".xlsx", 51        .Close    End With    With Application        .DisplayAlerts = True        .ScreenUpdating = True        .EnableEvents = True    End WithExit SubErrorHandler: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel ThenElse: Call ClearEnd Sub

Error Handling:

You could try something like this to catch a specific error:

    On Error Resume Next    Set owb = Application.Workbooks.Open(fpath & "\" & fname)    If Err.Number = 1004 Then    GoTo FileNotFound    Else    End If    ...    Exit Sub    FileNotFound: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then    Else: Call Clear

I'll try and answer several different things, however my contribution may not cover all of your questions. Maybe several of us can take different chunks out of this. However, this info should be helpful for you. Here we go..

Opening A Seperate File:

ChDir "[Path here]"                          'get into the right folder hereWorkbooks.Open Filename:= "[Path here]"      'include the filename in this path'copy data into current workbook or whatever you want hereActiveWindow.Close                          'closes out the file

Opening A File With Specified Date If It Exists:

I'm not sure how to search your directory to see if a file exists, but in my case I wouldn't bother to search for it, I'd just try to open it and put in some error checking so that if it doesn't exist then display this message or do xyz.

Some common error checking statements:

On Error Resume Next   'if error occurs continues on to the next line (ignores it)ChDir "[Path here]"                         Workbooks.Open Filename:= "[Path here]"      'try to open file here

Or (better option):

if one doesn't exist then bring up either a message box or dialogue box to say "the file does not exist, would you like to create a new one?

you would most likely want to use the GoTo ErrorHandler shown below to achieve this

On Error GoTo ErrorHandler:ChDir "[Path here]"                         Workbooks.Open Filename:= "[Path here]"      'try to open file hereErrorHandler:'Display error message or any code you want to run on error here

Much more info on Error handling here: http://www.cpearson.com/excel/errorhandling.htm

Also if you want to learn more or need to know more generally in VBA I would recommend Siddharth Rout's site, he has lots of tutorials and example code here: http://www.siddharthrout.com/vb-dot-net-and-excel/

Hope this helps!

Example on how to ensure error code doesn't run EVERYtime:

if you debug through the code without the Exit Sub BEFORE the error handler you'll soon realize the error handler will be run everytime regarldess of if there is an error or not. The link below the code example shows a previous answer to this question.

  Sub Macro    On Error GoTo ErrorHandler:    ChDir "[Path here]"                             Workbooks.Open Filename:= "[Path here]"      'try to open file here    Exit Sub      'Code will exit BEFORE ErrorHandler if everything goes smoothly                  'Otherwise, on error, ErrorHandler will be run    ErrorHandler:    'Display error message or any code you want to run on error here  End Sub

Also, look at this other question in you need more reference to how this works:goto block not working VBA