Removing all Connections via VBA giving error messages when opening new ws

[ron2k_1]

Hi guys,

Sorry to be a real nag lately. But it's addictive once you find out that amateur code starts to do in secs what took you minutes or hours to do in the past...

So, I was able to device the following piece of code by scratching info all over the net:

Please Login or Register  to view this content.

It sure removes all connection on the current wb. However, now every time I open excel (fresh when there are no other WBooks open) it gives me an error saying that the connection the code just removed is not available.

In reality the code always remove only one connection at any given time, because it runs along other code that only adds 1 connection at that instance. The above code will remove it.

The connection being created by the code depends on a selection on a combobox (called cboRptname on my form).

Has anybody come across something similar?

[Leith Ross]

Hello Ron2k_1,

Try adding this to your Workbook_Open event.

Please Login or Register  to view this content.

This disables the external link when the workbook is opened.

[ron2k_1]

Hello Ron2k_1,

Try adding this to your Workbook_Open event.

Please Login or Register  to view this content.

This disables the external link when the workbook is opened.

Remember, that when I use this file I need to create connections to import some data, and then terminate them after the code is run. Would your instruction when opening the wb interfere with my code?

And as well, the message appear when I open any excel file (not necessarily the one that create and then deletes the connections).

What about adding this to the Workbook_Close event?

[ron2k_1]

I tried it on the beforeclose event and its giving an "Invalid use of property"?

What did I do wrong?

[Leith Ross]

Hello Ron2K_1,

Did you disable the connections before deleting them?

[ron2k_1]

Nope just the above code to delete it.

Can I just add .ConnectionsDisabled to my code above before deleting them.

Actually, I think they are disabled once deleted because I cannot add no dataconnection manually (by going to Data Ribbon and add connection from Text option) on the worksheets to which the connections are deleted. Why is this?

Thanks for your help this far

[Leith Ross]

Hello ron2K_1

Disabling before the connections before deleting them is what I meant. Did you try that? I would not rely on them being automatically disabled as you delete them.

[ron2k_1]

I tried disabling them before deleting but it seems that I can't get the right code to do it. I've tried:

Please Login or Register  to view this content.

And:

Please Login or Register  to view this content.

And:

Please Login or Register  to view this content.

All of them give me "Invalid use of Property" error message

[snb]

It sure removes all connection on the current wb. However, now every time I open excel (fresh when there are no other WBooks open) it gives me an error saying that the connection the code just removed is not available.

Where is this macro situated ? ( in personal.xls, in an addin ?)

[ron2k_1]

It is part of a form routine (part of form) in an xlsm file

The form:
1. Import the data via data connection - Checked
2. Removes unwanted rows that meet a criteria - Checked
3. Makes the imported data readable. Removes Special char from values, converts to proper date - Checked
4. Sorts and places subtotals - Checked
5. Removes Data Connections - Checked **
6. Saves the worksheets in separate workbooks - checked
7. Closes the new workbooks, and clears the worksheets in which the form exists - Checked

** Step # 6 actually happens, because my data connection ribbon is disabled and under connections there are none. However, it seems that they are not really deleted, as every instance when I open excel app (where there are no other workbooks open), it tells me that the connection (the most recently deleted) is not available.

???

[snb]

Excel can't give an error message if the file that contains the macro has not been loaded yet. So probably some code is elsewhere: personal.xls or an add-in.

[ron2k_1]

Yep, strange isn't it?

And that's exactly what I thought, that I placed the code in the wrong section. But I've checked and nope, there is nothing in the VBE Project Manager. And the only Addins I have loaded are the Analysis Tool Pack. So its really strange. I want to try the disabling of Connections prior to deleting. Any suggestion for that part?

This is just so weird! I open excel application, it gives this error (as you see there are no other workbooks open):
\1

I have no connections, as you can see:
\1

And there is nothing in my VBE Project Window. What you see there is default for all 2007 new sheets:
\1

It is as if everytime I open excel, the application interprets the shortcut to the application as trying to open the file to which I made a connection to the last time I ran the file that contains the form. Does that make sense??

[ron2k_1]

This is the code I use to import the .999 report:

Please Login or Register  to view this content.

Do you see any issues there?

[snb]

As I can see:

You loaded 2 addins:

ATPVBAEN.xlam
FuncRes.xlam

Unload these.

[ron2k_1]

As I can see:

You loaded 2 addins:

ATPVBAEN.xlam
FuncRes.xlam

Unload these.

I think those come by default to xl2007. And if you see my third pic, maybe you'll be able to see in its related vbewindow that it doesn't do anything concerning data connections. I'm not even sure how to disable those; or whether I should... As I say, as long as I remember, my xl comes along with them by default.

This is becoming really tedious and nagging. I really don't know what else to do... And this message started to pop up as soon as I added the connections.delete code to my form routine.

[ron2k_1]

Hey guess what?! snb was right! There was an addin called PSAV, how did that get there???

Anyways, I unloaded the addin and tweaked my code to this. So far the addin hasn't been recreated. Have no idea how that got there:

Please Login or Register  to view this content.

I've thanked everybody in this thread, so far everything its good...