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

Question

This question has been edited due to lengthy comments and updates to suggested answers.

According to the request, module 13 is located here;

Sub SaveInFormat() Application.DisplayAlerts = False Workbooks.Application.ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data\" & Format(Date, "yyyymm") & "DB" & ".xlsx", leFormat:=51 Application.DisplayAlerts = True End Sub 

There are also problems with error handling, I know that I made a mistake with this, but I'm more interested in fixing the close function at the moment, before I enter it. Here is the error handling code that needs some work

 Sub test() Dim wk As String, yr As String, fname As String, fpath As String Dim owb As Workbook wk = ComboBox1.Value yr = ComboBox2.Value fname = yr & "W" & wk fpath = "C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data" owb = Application.Workbooks.Open(fpath & "\" & fname) On Error GoTo ErrorHandler: ErrorHandler: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then Exit Sub Else Call Clear 'Do Some Stuff Call Module13.SaveInFormat owb.Close 

this is your test code plus my path and file name change

Answer 1

After discussing the publication of the updated answer:

 Option Explicit Sub 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 With Exit Sub ErrorHandler: If MsgBox("This File Does Not Exist!", vbRetryCancel) = vbCancel Then Else: Call Clear End Sub 

Error processing:

You can 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 

Answer 2

I will try to answer a few different questions, but my contribution may not cover all your questions. Perhaps some of us may take different pieces from this. However, this information should be useful to you. Here we go..

Opening a single file:

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

---

Opening a file with the specified date, if it exists:

I am not sure how to search in your directory to see if a file exists, but in my case I would not search for it, I would just try to open it and make some error checking so that if it does not exist, display this message or do xyz.

Some common error checking operators:

 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 (best option):

if this does not exist, then either display a message box or dialogue to say: "The file does not exist, do you want to create a new one?

you will 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 here ErrorHandler: 'Display error message or any code you want to run on error here 

---

Much more error handling information is here: http://www.cpearson.com/excel/errorhandling.htm

---

Also, if you want to know more or know more in VBA, I would recommend the Siddharth Rout website, it has many tutorials and sample code here: http://www.siddharthrout.com/vb-dot-net-and-excel /

Hope this helps!

---

An example of how to ensure that the error code does not run EVERY:

if you are debugging code without Exit Sub BEFORE the error handler, you will soon realize that the error handler will run every time if an error occurs or not. The link below the sample code shows the 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, take a look at this other question, in which you need more links to how this works: switch fail VBA

---