Duplicate Projects in VBA Project Explorer

S

strorg

Board Regular
Joined
Mar 27, 2002
Messages
112
I have a series of connected workbooks that work something like this:

BookA.xls remains open and once per hour it opens BookB.xls
BookB.xls opens BookC.xls and copies a range of data into BookB.xls.
BookB.xls closes BookC.xls.
BookB.xls does a SaveCopyAs BookD.xls.
BookB.xls closes BookC.xls.

My problem it that every time this series of events occurs, I end up with an additional copy of BookB and BookC in the VBA project explorer. And if I look in Tools | References I see several instances of UNSAVED BookB.

Any ideas?

Regards...Tom

Below is the routine from BookB:

Sub RetrieveSave()
'On Error GoTo BugOut

Dim wbPMO, wbPlots, myPath, myWebPath As String
Dim myDateRange As String
Dim myValSource, myValDestination As String

myPath = "O:\Process Engineering\PMO\ConsolePMO\EII\"
myWebPath = "O:\Process Engineering\PMO\ConsolePMO\EII\EII_Hourly_Plots.xls"
wbPMO = "EII_Console_PMO.xls"
wbPlots = Application.ThisWorkbook.Name
myDateRange = "A11:A515"
myValSource = "BB6:DS515"
myValDestination = "B6"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Open the PMO workbook
Application.StatusBar = "Opening file " & myPath & wbPMO
Workbooks.Open Filename:=myPath & wbPMO, UpdateLinks:=0

'Copy the EII dates from the EII Console PMO workbook
Application.StatusBar = "Copying dates from " & wbPMO
Application.Workbooks(wbPMO).Activate
Sheets("Hourly Calcs EII").Activate
Range(myDateRange).Select
Selection.Copy

'Paste the EII data into EII Charts workbook
Application.StatusBar = "Pasting dates to " & wbPlots
Application.Workbooks(wbPlots).Activate
Sheets("Data").Activate
Range(myDateRange).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Copy the EII values from the EII Console PMO workbook
Application.StatusBar = "Copying values from " & wbPMO
Application.Workbooks(wbPMO).Activate
Sheets("Hourly Calcs EII").Activate
Range(myValSource).Select
Selection.Copy

'Paste the EII values into EII Charts workbook
Application.StatusBar = "Pasting values to " & wbPlots
Application.Workbooks(wbPlots).Activate
Sheets("Data").Activate
Range(myValDestination).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Close the PMO workbook
Application.StatusBar = "Closing " & myPath & wbPMO
Workbooks(wbPMO).Close SaveChanges:=False

'Call the chart formatting routine
Application.StatusBar = "Adjusting format of charts..."
Call AlignCharts

'Save a copy of the workbook to the web folder
Application.StatusBar = "Saving a copy to the web as " & myWebPath
Sheets("Data").Visible = False
ActiveWorkbook.SaveCopyAs myWebPath _

'Save the workbook
Application.StatusBar = "Saving " & ActiveWorkbook.Name
Sheets("Data").Visible = True
ActiveWorkbook.Save

BugOut:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
[/list]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
This has not been tested, seeing as I don't have your workbooks, but this might help. I've replaced Object variable for a few of your actions. See if this helps with the additional references that are being created.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RetrieveSave()
<SPAN style="color:#007F00">'On Error GoTo BugOut</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> wbPMO, wbPlots, myWebPath

<SPAN style="color:#00007F">Dim</SPAN> myPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> myDateRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> myValSource <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, myValDestination <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Set</SPAN> wbPlots = ThisWorkbook

myPath = "O:\Process Engineering\PMO\ConsolePMO\EII\"
myWebPath = "O:\Process Engineering\PMO\ConsolePMO\EII\EII_Hourly_Plots.xls"
wbPMO = "EII_Console_PMO.xls"
myDateRange = "A11:A515"
myValSource = "BB6:DS515"
myValDestination = "B6"

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#007F00">'Open the PMO workbook</SPAN>
Application.StatusBar = "Opening file " & myPath & wbPMO
<SPAN style="color:#00007F">Set</SPAN> wbPMO = Workbooks.Open(FileName:=myPath & wbPMO, UpdateLinks:=0)

<SPAN style="color:#007F00">'Copy the EII dates from the EII Console PMO workbook</SPAN>
Application.StatusBar = "Copying dates from " & wbPMO.Name
wbPMO.Sheets("Hourly Calcs EII").Range(myDateRange).Copy

<SPAN style="color:#007F00">'Paste the EII data into EII Charts workbook</SPAN>
Application.StatusBar = "Pasting dates to " & wbPlots.Name
wbPlots.Sheets("Data").Range(myDateRange).PasteSpecial Paste:=xlPasteValues

<SPAN style="color:#007F00">'Copy the EII values from the EII Console PMO workbook</SPAN>
Application.StatusBar = "Copying values from " & wbPMO.Name
wbPMO.Sheets("Hourly Calcs EII").Range(myValSource).Copy

<SPAN style="color:#007F00">'Paste the EII values into EII Charts workbook</SPAN>
Application.StatusBar = "Pasting values to " & wbPlots.Name
wbPlots.Sheets("Data").Range(myValDestination).PasteSpecial Paste:=xlPasteValues

<SPAN style="color:#007F00">'Close the PMO workbook</SPAN>
Application.StatusBar = "Closing " & wbPMO.FullName
wbPMO.<SPAN style="color:#00007F">Close</SPAN> SaveChanges:=<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wbPMO = <SPAN style="color:#00007F">Nothing</SPAN>

<SPAN style="color:#007F00">'Call the chart formatting routine</SPAN>
Application.StatusBar = "Adjusting format of charts..."
<SPAN style="color:#00007F">Call</SPAN> AlignCharts

<SPAN style="color:#007F00">'Save a copy of the workbook to the web folder</SPAN>
Application.StatusBar = "Saving a copy to the web as " & myWebPath
wbPlots.Sheets("Data").Visible = <SPAN style="color:#00007F">False</SPAN>
wbPlots.SaveCopyAs FileName:=myWebPath

<SPAN style="color:#007F00">'Save the workbook</SPAN>
Application.StatusBar = "Saving " & wbPlots.Name
wbPlots.Sheets("Data").Visible = <SPAN style="color:#00007F">True</SPAN>
wbPlots.Save

<SPAN style="color:#00007F">Set</SPAN> wbPlots = <SPAN style="color:#00007F">Nothing</SPAN>

BugOut:
Application.StatusBar = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA is there a way to speed up?
Replies
4
Views
228
RoryA
RoryA
J
  • Question
Run time error with VBA to copy Chart objects from closed workbook
Replies
4
Views
132
Domenic
D
C
  • Solved
VBA to open file, clear formats and copy paste
Replies
9
Views
296
Alex Blakenburg
A
K
  • Question
VBA Copy, Paste to Different Workbook, Save as then loop the action through slicer
Replies
1
Views
694
krisoey
K
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
618
Peter Davison
P

Forum statistics

Threads
1,215,381
Messages
6,124,615
Members
449,175
Latest member
Anniewonder

Share this page

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