Adding on to existing VBA code...

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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? :confused:

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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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? /board/images/smiles/icon_confused.gif

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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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! :LOL:

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top