Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Run macro save workbook under new name then open original workbook again. (https://www.askmehelpdesk.com/showthread.php?t=696500)

  • Aug 23, 2012, 10:51 PM
    rsdjimbie
    Run macro save workbook under new name then open original workbook again.
    Run macro save workbook under new name then open original workbook again to continue other code to run.I have the following code that runs x3 "Application.Run" from the original, but is not effective.
    Code:

    Sub SaveWorkbookAsNewFile()
        Dim ActSheet As Worksheet
        Dim ActBook As Workbook
        Dim CurrentFile As String
        Dim NewFileType As String
        Dim NewFile As String
     
        Application.ScreenUpdating = False    ' Prevents screen refreshing.

        CurrentFile = ThisWorkbook.FullName
     
        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
                  "Excel Files 2007 (*.xlsx), *.xlsx," & _
                  "All files (*.*), *.*"
     
        NewFile = Application.GetSaveAsFilename( _
            InitialFileName:=NewFileName, _
            fileFilter:=NewFileType)
     
        If NewFile <> "" And NewFile <> "False" Then
            ActiveWorkbook.SaveAs Filename:=NewFile, _
                FileFormat:=xlNormal, _
                Password:="", _
                WriteResPassword:="", _
                ReadOnlyRecommended:=False, _
                CreateBackup:=False
               
     Application.Run "Advise_Manhood_PAYE_Advise"
           
            Set ActBook = ActiveWorkbook
            Workbooks.Open CurrentFile
            ActBook.Close
        End If
       
     
     CurrentFile = ThisWorkbook.FullName
     
        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
                  "Excel Files 2007 (*.xlsx), *.xlsx," & _
                  "All files (*.*), *.*"
     
        NewFile = Application.GetSaveAsFilename( _
            InitialFileName:=NewFileName, _
            fileFilter:=NewFileType)
     
        If NewFile <> "" And NewFile <> "False" Then
            ActiveWorkbook.SaveAs Filename:=NewFile, _
                FileFormat:=xlNormal, _
                Password:="", _
                WriteResPassword:="", _
                ReadOnlyRecommended:=False, _
                CreateBackup:=False
       
       
     Application.Run "Advise_Manhood_Wage_Advise"
     
     Set ActBook = ActiveWorkbook
            Workbooks.Open CurrentFile
            ActBook.Close
        End If
       
    CurrentFile = ThisWorkbook.FullName
     
        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
                  "Excel Files 2007 (*.xlsx), *.xlsx," & _
                  "All files (*.*), *.*"
     
        NewFile = Application.GetSaveAsFilename( _
            InitialFileName:=NewFileName, _
            fileFilter:=NewFileType)
     
        If NewFile <> "" And NewFile <> "False" Then
            ActiveWorkbook.SaveAs Filename:=NewFile, _
                FileFormat:=xlNormal, _
                Password:="", _
                WriteResPassword:="", _
                ReadOnlyRecommended:=False, _
                CreateBackup:=False
      End If
     
      Application.Run "BankAdviceToDesktop"
     
        Application.ScreenUpdating = True
    End Sub

    Thanks in advance.
  • Aug 24, 2012, 12:01 AM
    JBeaucaire
    Perhaps it would be less confusing to simply save the active workbook using the SaveCopyAs method to save a copy without affecting the current workbook itself, then it's child's play to continue on with the next macro.
  • Aug 25, 2012, 09:54 AM
    rsdjimbie
    Thanks this is a much shoter version and it all runs well, thanks JB. Only problem I have is when the ActiveWorkbook's name change is the code giving a file not found error.
    Code:

    Option Explicit
    Sub All_Three_Advise_Macro_Runs()
    Dim Thisbook As String

            ActiveWorkbook.SaveCopyAs ("TempAdvise.xls")
     
     Thisbook = ActiveWorkbook.Name

    Application.Run "Advise_Manhood_Wage_Advise" ' mod 17

    Workbooks.Open Filename:=(Thisbook)

    Application.Run "Advise_Manhood_PAYE_Advise" 'mod 20

    Workbooks.Open Filename:=(Thisbook)

    Application.Run "BankAdviceToDesktop" ' mod 10

    Application.Quit
    End Sub

  • Aug 25, 2012, 01:07 PM
    rsdjimbie
    Found my problem, called the active workbook instaed of the temp file, changed this lin and it works.
    Code:

    Thisbook = ("TempAdvise.xls")

  • All times are GMT -7. The time now is 11:06 PM.