Adding on to existing VBA code...
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Adding on to existing VBA code...

  1. #1
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I currently have a blank workbook with nothing but the code, so that when I open it, it will run the macro and open certain sheets.

    Can I add on to this so that after it is finished running, it will close the original worksheet?

    This is what I currently have:

    Code:
    Private Sub Workbook_Open()
    On Error GoTo myErr
    ChDir "directory"
    Workbooks.Open "workbook1.xls"
    Workbooks.Open "workbook2.xls"
    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(Which)
       Workbooks.Open Which(I)
    Next
    
    End
    myErr:
    End Sub
    Thankee much.
    Kristy

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 08:34, Von Pookie wrote:
    I currently have a blank workbook with nothing but the code, so that when I open it, it will run the macro and open certain sheets.

    Can I add on to this so that after it is finished running, it will close the original worksheet? [img]/board/images/smiles/icon_confused.gif[/img]

    This is what I currently have:

    Code:
    Private Sub Workbook_Open()
    On Error GoTo myErr
    ChDir "directory"
    Workbooks.Open "workbook1.xls"
    Workbooks.Open "workbook2.xls"
    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(Which)
       Workbooks.Open Which(I)
    Next
    
    End
    myErr:
    End Sub
    Thankee much.
    Sure, you can add to your existing code. What is it you want to do/add?



    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 08:41, Barrie Davidson wrote:
    Sure, you can add to your existing code. What is it you want to do/add?
    Quoted from the original message: "Can I add on to this so that after it is finished running, it will close the original worksheet?"





    [ This Message was edited by: Von Pookie on 2002-03-08 08:46 ]

  4. #4
    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

    At the end of your code, you can put a line like this:

    ThisWorkbook.Close False

    The false is so that it won't save changes (and won't prompt you). If you do want to save changes to this workbook, then change false to true.

    HTH,

    Russell

    [ This Message was edited by: Russell Hauf on 2002-03-08 08:49 ]

  5. #5
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Looks like it worked!

    Thanks!

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 08:45, Von Pookie wrote:
    On 2002-03-08 08:41, Barrie Davidson wrote:
    Sure, you can add to your existing code. What is it you want to do/add?
    Quoted from the original message: "Can I add on to this so that after it is finished running, it will close the original worksheet?"





    [ This Message was edited by: Von Pookie on 2002-03-08 08:46 ]
    The reason I didn't understand is because the code, as posted, is only opening files. So, if you add code to close the files you have opened, it will end up doing nothing. Assuming you want to run another macro and then save the file with changes, you can change your code to:

    Code:
    Private Sub Workbook_Open()
    Dim CurrentWorkbook As String
    On Error GoTo myErr
    ChDir "directory"
    Workbooks.Open "workbook1.xls"
    Workbooks.Open "workbook2.xls"
    which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(which)
       Workbooks.Open which(I)
       CurrentWorkbook = ActiveWorkbook.Name
       'whatever macro you want to run.
       'this is where I was getting confused
       Workbooks(CurrentWorkbook).Close (True)
    Next
    
    End
    myErr:
    End Sub
    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 08:53, Barrie Davidson wrote:


    The reason I didn't understand is because the code, as posted, is only opening files. So, if you add code to close the files you have opened, it will end up doing nothing.
    I guess I thought it was self-explanatory. That showed me!

    I have a blank workbook with the code to open 3 other workbooks. After those 3 were open, I simply wanted to close the blank one with the code, but leave the ones I told it to open.

    Sorry about that!

    _________________
    "What am I, a magnet for these idiots?"
    --Pearl Forrester, MST3K

    [ This Message was edited by: Von Pookie on 2002-03-08 09:12 ]

  8. #8
    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

      
    I thought you described it fine with this sentence from your original post:

    "Can I add on to this so that after it is finished running, it will close the original worksheet?"

    Glad I could help,

    Russell

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