vba PROJECTS DUPLICATES

[Joke]

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("a22").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

 

[parry]

Hi Im not sure I understand but maybe you need to release the object from memory.

Whenever you set an object such as ...

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

Set OpenedWb = Nothing

Also these lines here...

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]

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]

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]

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]

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]

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]

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]

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]

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.