Ask Experts Questions for FREE Help !
Ask
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #1

    Aug 23, 2012, 10:51 PM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Aug 24, 2012, 12:01 AM
    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.
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #3

    Aug 25, 2012, 09:54 AM
    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
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #4

    Aug 25, 2012, 01:07 PM
    Found my problem, called the active workbook instaed of the temp file, changed this lin and it works.
    Code:
    Thisbook = ("TempAdvise.xls")

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Create New Workbook Macro [ 3 Answers ]

I need to create a macro that creates a certain number of sheets in a workbook then creates a new workbook. What would you recommend be the maximum number of sheets in the workbook and what is the code to create a new workbook?

A-1 photography workbook [ 1 Answers ]

Hello, I have an assignment due that requires the A-1 photography work book. I received the a-1 package weeks after my books arrived without even knowing something was missing. I didn't realize something was missing until I received the assignment online. Is it a way I can view this online. ...

How do I update an entrey from one workbook to another [ 2 Answers ]

I need to enter an entry from one workbook to another and also update the entry when I put new information in it. e.g I have one main soure file inventory.xls and I have few other files with lot of worskheets(tabs). What I need to do is if I enter an entry in worksheet if should enter that entry...

Worksheets within the same workbook [ 21 Answers ]

Hi, I have many worksheets within the same file, what I really want is to have either a (link / go to option) from sheet1 to other sheets without using hyperlink because I want to send to others. Example: if I click on Apple from Sheet 1, I will go to Sheet2 Sheet1 Sheet2 ...


View more questions Search