Activeworkbook.close Problems ?
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Activeworkbook.close Problems ?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I write VBA Code. In during Code Command I use command to Activate another Sheet after finish this file Job. I want to go back to the old file and do some command more, but when I use Activeworkbook.Close !! It's will be End Sub Automatically (Even I have more Procedure to write ) How can I solve This question ?

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you explain in a little more detail? Maybe you could also show some of your code and let us know exactly where your problem lies...

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I use This command for set UndoPoint by put SetunoPoint before change the File and use UndoMacro sub to call the old File back but when the processing it will stop ,I mean the Process go to End sub Automatically when the process go to Activeworkbook.close Line. Do you have any Idea to run it for complete the Sub Process. Thanks.

    dim ww
    Sub SetUndoPoint()
    WW = ThisWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
    ActiveWorkbook.SaveCopyAs WW & ".xlu"
    End Sub
    Sub UndoMacro()
    Application.ScreenUpdating = False
    WW = ThisWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
    ActiveWorkbook.SaveAs WW & ".tmp" 'เพื่อเป็นการปิด File.xls
    Workbooks.Open WW & ".xlu" 'เรียก File ที่ก่อนเปลี่ยนแปลงกลับมา
    Application.DisplayAlerts = False
    Sheets(zz).Select
    ActiveWorkbook.SaveAs WW & ".xls" 'เปลี่ยนชื่อเป็นชื่อเดิม
    Kill WW & ".xlu"
    Workbooks.Open WW & ".tmp" 'เรียกไฟล์นี้มาเพื่อ Kill
    ActiveWorkbook.Close
    Kill WW & ".tmp"

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry the Last line is
    End Sub
    ....Thanks for the answers.

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I'm not following you.
    (and the garbage characters aren't helping either).

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You might want to try the following:

    Application.ScreenUpdating = False
    ww = ThisWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ww & ".tmp"
    Workbooks.Open ww & ".xlu"
    ActiveWorkbook.SaveAs ww & ".xls"
    Kill ww & ".xlu"
    Application.DisplayAlerts = True
    Workbooks(ww & ".tmp").Close (False)

    HTH

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks BabyTiger,But the Command Line
    Workbooks(ww & ".tmp").Close (False)
    Still error!! so I write the Whole Command for anybody can see again maybe have other idea to Help. First I call SubDeleteA1 after that I call Sub UndoMacro but it will error on
    Workbooks(ww & ".tmp").Close (False) ,So any idea ? Thanks.

    dim ww

    Sub DeleteA1()
    SetUndoPoint
    Range("A1").value = ""
    End Sub

    Sub SetUndoPoint()
    WW = ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
    ActiveWorkbook.SaveCopyAs WW & ".xlu"
    End Sub

    Sub UndoMacro()
    Application.ScreenUpdating = False
    WW = ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
    ActiveWorkbook.SaveAs WW & ".tmp" 'Close File.xls
    Workbooks.Open WW & ".xlu" 'Call old File before Change
    Application.DisplayAlerts = False
    Sheets(zz).Select
    ActiveWorkbook.SaveAs WW & ".xls" 'change name to the old one
    Kill WW & ".xlu"
    Application.DisplayAlerts = True
    Workbooks(ww & ".tmp").Close (False) ** Problem Line
    Kill WW & ".tmp"
    End sub

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry ,No Line Sheets(zz).Select

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com