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    amp  Format Date   yyyymm    amp   DB   amp    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  amp   W   amp  wk fpath    C  Documents and Settings jammil Desktop AutoFinance ProjectControl Data  owb   Application Workbooks Open fpath  amp       amp  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

User · Accepted Answer

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  amp   W   amp  wk     fpath    C  Documents and Settings jammil Desktop AutoFinance ProjectControl Data       On Error GoTo ErrorHandler     Set owb   Application Workbooks Open fpath  amp       amp  fname        Do Some Stuff      With owb          SaveAs fpath  amp  Format Date   yyyymm    amp   DB   amp    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  amp       amp  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

User · Answer

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

[vba] Excel VBA Open workbook, perform actions, save as, close

Examples related to vba

Examples related to excel

Examples related to automation

Examples related to userform