Adding on to existing VBA code...

[Von Pookie]

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:

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.

[Barrie Davidson]

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:

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?

[Von Pookie]

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 ]

[Russell Hauf]

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 ]

[Von Pookie]

Looks like it worked!

Thanks!

[Barrie Davidson]

On 2002-03-08 08:45, Von Pookie wrote:

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:

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,

[Von Pookie]

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 ]

[Russell Hauf]

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