vba PROJECTS DUPLICATES

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
I{m using a macro to open another xl save data and close it again.

After every use of hte macro the VBAproject of the other XL file remains open and I end up with many VBAprojects of the same file.

Seems the file is not totaly closed with the OpenedWB.close.........

Any idea how I can close also the VBA project everytime the file is opened en close in Excel?????


****************************************
Dim ActiveSh As Worksheet
Dim OpenedWb As Workbook
Dim strFname As String
Application.ScreenUpdating = False
If WorksheetFunction.CountA(Range("control1"), Range("cedula1")) = 6 Then
strFname = Range("ubicacion").Value
Set ActiveSh = ActiveSheet()
Set OpenedWb = Workbooks.Open(Filename:=strFname,
writeResPassword:=Sheet5.Range("password").Value)
With OpenedWb
.ActiveSheet.Range("A2").EntireRow.Insert
.ActiveSheet.Range("a2:eek:2").Value = ActiveSh.Range
("guardar1").Value
OpenedWb.Close
Application.Goto Reference:="comienzo2"
End With
Else
If WorksheetFunction.CountA(Range("control1")) < 5 Then
Application.Goto Reference:="control1"
MsgBox "Llena todos los datos "
Else
Application.Goto Reference:="cedula1"
MsgBox "llena el numero de cedula "
End If
End If
Application.ScreenUpdating = True
End Sub
**************************************

Thanks for helping me out on this one!

Joke
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Im not sure I understand but maybe you need to release the object from memory.

Whenever you set an object such as ...
Code:
Set OpenedWb = Workbooks.Open(Filename:=strFname, 
writeResPassword:=Sheet5.Range("password").Value)
then you should release it from memory when youve finished with it such as
Code:
Set OpenedWb = Nothing

Also these lines here...
Code:
OpenedWb.Close 
Application.Goto Reference:="comienzo2" 
End With

Im not entirely sure if the middle line is refering to the book where you run the code from or the OpenedWb book. I suspect its the book with the code & it may not matter but I would move this middle line after End With. Otherwise it looks like your going to a reference in a book thats been closed.

hth
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
Thanks for the advice. I put the set-nothing at several places but still the problem with the open Database'VBA project continues.

Doesnt seem to matter whether i put it before ELSE or after the first or second END If

Also removed the reference to Comiezo2 all together because seems i actually didnt need it.

any other ideas what could be the problem?

Joke
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Im not sure what you mean by open VBA project. Could you also describe where you were in Excel (a sheet, the VBE?) when you ran the macro. I suspect its nothing to worry about.
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171

ADVERTISEMENT

Im talking about the VBA editor the database file remains as unsaved VBA projects with as many duplicates as I used the macro button.
So the excel file is close but the VBA project of the files remains unsaved in the editor

Joke
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
OK, just wanted to check as its very odd. Save & Close the workbook with the code then reopen it and see if the other projects are still there - they shouldnt be.

Your line OpenedWb.Close should cause Excel to pop up a box asking if you want to save or not. Does it? If it has and you havent clicked Yes or No then the code should stop until you press a button. The Close property has an option to save changes or not such as this so try indicating whether its closed or not.

OpenedWb.Close SaveChanges:=False 'or true

See if that makes any difference. It didnt to me, the book just closes and the VBE cleared of the project.
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171

ADVERTISEMENT

I have include the TRUE in the close statement but still keeps the databse files duplicates.........

Don{t know how serious the problem is though? I do get once and a while in funny momente the message no HALT or RETURN function found

Is it possible the message and the non closing problem are related?

search the board for this error message but couldn{t find any solution.

so Im still continuing a bit with this frustration thanks for further help,

Joke
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Dunno, Ive never heard of this before. What happened when you closed and reopened the book - did it reset itself? I wonder if theres a Workbook_Open or Workbook_Close event on the book your opening thats causing problems.
 

pablray

New Member
Joined
May 10, 2005
Messages
16
Same Here!

I have been looking around for a resolution to this problem and haven't found one either. Even though the excel file has been closed and released from memory. the VBA Editor still has a reference of that file in the projects window. The last file on the list has been closed on the excel side but remains in the VBA Editor. This slows down any macros. Very strange!!
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
I have been having the same problem for over a month now. I even had MS Office completely removed and re-loaded and it didn't help. The IT people ran a few other programs that didn't help either. Hopefully someone out there has a fix.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,164
Messages
5,768,564
Members
425,482
Latest member
blazingdiva1

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
Top