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

Question

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

As requested here is module 13;

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

Also there are issues with the errorhandling, I know I've gone wrong with it but I'm more interested in fixing the close function at the moment before I get into 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 changing of the file path and name

Solution 1

After discussion posting 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 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

Solution 2

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 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 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 here

ErrorHandler:
'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

---

Comments

[JamesDev]

Thank you, I unfortunately am leaving work now but I will look at this tomorrow

[JamesDev]

I have used your error handling to make a message appear if the attempted file open fails. Unfortunately when the file open works I still get the message

[Alistair Weir]

Remove the line owb.close if it is no longer open anyway. Add another line to refer to the name of your new workbook if you want to close it after saving.

[Alistair Weir]

Yes the same method can apply such as: .SaveAs mypath & "\" & yr & mnth

[Alistair Weir]

If we backtrack a little bit, did you declare oWB as a workbook and Set it to the workbook that is opened based on the values derived from the comboboxes? If I understand you then perform some more actions on this workbook that was opened and Save it as a different filename (YYYYMMDB.xlsx)? If so then you should still be able to use the oWB variable to close the workbook.

[Alistair Weir]

Where have you placed my code with your additions? Can you edit your original post and add the contents of Module13 please.

[Alistair Weir]

Edited my question, you don't need to use a separate module for the Save, I've incorporated that into my code. Give it a try now.

[Alistair Weir]

See latest update, I Missed Set in front of owb = Application... That should fix it.

[JamesDev]

I have upvoted as your errorhandling provided me a platform on which to work with but for his depth of answer and ongoing help I am accepting the answer of @alistair weir

[JamesDev]

update: from what I can see it is opening the file and saving and closing before the error happens, it runs the open, close and save but then opens the error handling message anyway

[Alistair Weir]

Yes that is a problem when trying to catch a specific error, I've edited my post with an idea for stopping that but I am not an expert on error handling.

[Alistair Weir]

Change your error handler to display a message box with Err.Desc to find out what the problem is and step through the code with break points to identify the line causing the error.

[JamesDev]

unfortunately that has worked but nonetheless you earn the status of hero of the week. It is of no use but you will be from now until next friday my hero of the week, it's going on facebook. I'll get to work on a trophy or something

[Mike Kellogg]

@JamesDev you need to put Exit [Sub,If (whatever the code is in)] before the error handling section. This way the code will not naturally run the code unless it is explicitly told to do so because of the error. I'll post example in my answer above.

[Mike Kellogg]

@JamesDev I answered your question about the error code running everytime. It is listed at the bottom of my answer.

[Mike Kellogg]

@AlistairWeir I didn't look at your code in depth but I believe you want to have Exit Sub BEFORE the error handler. See the very bottom of my answer for reference.

[JamesDev]

Thank you both, excellent clear and concise answers that helped me get a platform to start from (and may have just saved my job for now). Top lads

[JamesDev]

Ok so I have a new question related to this, as it stands I have the program opening as above the workbook based on the entered month and year. What I need to do now is to now attempt to open based on the month and year and if there isn't one then open the previous months one and create a new one, how would I go about that?

[Mike Kellogg]

you could use a number for your month and if the file doesnt exist for that month youll get an error, then have an error handler that will subtract 1 from your month number and try to open the new file.

[Mike Kellogg]

@JamesDev left a comment above but forgot to tag you in it.