"Run-time error '1004' when creating a table from a range in VBA

Excel 2019

I am having an issue whereby Excel will not allow me to convert a range to a table and provides the "Run-time error '1004': A Table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table caused by code line "Set tbl = ws.ListObjects.Add(xlSrcRange, selectedRange, , xlYes)"

Quick background.
I have VBA that imports a delimited text file.
The imported data is presented vertically my VBA selects it as a range which is then passed to a Power Query to remove unwanted lines of data and pivot the data horizontally.

On previous use this all worked fine until today for some reason Excel now gives the above error.

Nothing in the code was changed and I have been using the same test delimited text file so I have no clue what is going on.

I have written some code that is meant to clear all connections, tables and querys before the text file is imported which is below but the error still happens.
As the below images show, there are no connections, querys or tables in the workbook.

The wierd thing is that once I have ran the remove connections Sub and close and reopen the workbook my VBA to change the selected rang to Table works.
I really do not want to have to close and reopen the workbook each time I wish to create a new table.

If anyone can throw some light on this I would be very grateful as I am stumped.


This sub removes any data connections, querys and so on once the text file has been imported.

Please Login or Register  to view this content.

This code selects the range from the imported delimited text file.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Excel error msg.jpg
Connections.jpg
Querys.jpg

For now, as a test, delete the "Import" sheet BEFORE importing data. Code scheme:

Please Login or Register  to view this content.

Does such a code execution scheme still cause an error?

Artik

Hello

Sorry for not replying but I have been unexpectedly working away from home.

Thank you for both of your replies which I appreciate.

Artik thank you for your suggestion and if I am not mistaken, the idea behind creating a blank new sheet, deleting the existing import sheet
and the renaming the new sheet to "Import" is to get rid of any lingering connections / querys / pivot tables and so on that are not showing
within Excel?

As its 10pm here now I won't get a chance to try the code until tomorrow but I will certainly try it and report back if I may.

Thanks again both.

Originally Posted by Belinea2010

if I am not mistaken, the idea behind creating a blank new sheet, deleting the existing import sheet and the renaming the new sheet to "Import" is to get rid of any lingering connections / querys / pivot tables and so on that are not showing
within Excel?

You are almost not wrong. Simply deleting the sheet will not remove the associated queries and connections from the cache.
As for my suggestion, remember to delete the queries/connections/pivots before deleting the sheet. I do not guarantee that this is the right solution, although I hope it is.
In reference to the topic, I recently had a similar problem with tables linked by PowerQuery queries. I.e. the problem was the opposite of yours. Despite deleting the queries and the tables linked to them, the Queries and Connections side panel still showed the Queries I had just deleted.
I've been noticing more and more topics lately about Excel's strange behavior.Codes that previously worked are starting to report runtime errors. Something MS seems to be messing up.

Artik

Originally Posted by Artik

For now, as a test, delete the "Import" sheet BEFORE importing data. Code scheme:
Does such a code execution scheme still cause an error?
Artik

Thank you again for your suggestion and as I have this afternnon free I have been putting your code through some testing.

I have prepared 3 test delimited text files, real data but cut down to 500 lines in each.

If I open the workbook with only the import page, no others, then run your code (with the text file import code inserted where you indicated), The text file imports, and the rest of my subs prepare and convert it to a table then the 2nd query perfectly pivots it from vertical to horizontal, removes the data I do not need and saves it to a new worksheet.

I then run the RemoveConnections sub again before renaming the sheet.

That all works fine, however, when I try and import a 2nd different text (running your code as well as "RemoveConnections" sub, some times it imports fine, and so will a third, but if I
try a forth Excel will throw the Run-time error '1004'.

If I then close and resopen the workbook without saving (so it has no data), and import a test text file as described above, that will import fine but then the 2nd one produces the Run-time error '1004.

The sequence for when these errors happend is not repeatable as they appear to happen randomly.

Thanks anyway and it was good of you to make the suggestion.

Originally Posted by Artik

You are almost not wrong.
Artik

haha I am happy to be almost not wrong.

Bearing in mind what you have said about your similar problem I think that is what is happening in my case.
It is a pain in the backside but I can always close the workbook each time before importing the next file but I do wonder how users who are using Excel all day in a professional context can get by with its foibles and quirks.

It would drive me nuts.

Originally Posted by TMS

The code was incomplete and some variables were undefined. The code below seems to work consistently.

The code, as it stood, would fail if the Import sheet was not Selected/Activated

Hi TMS and greetings from the sunny East Midlands.

Thank you for your suggestion.

When I ran your augmented "RemoveConnections" it did nothing which is strange.
I checked all of the connections and querys were still in place.

May I enquire what you meant by "Dim QueryTable ' <<< not defined" as that is not part of my original code?

I only ask so that I may learn somethin new

Thanks again

Thank you for pointing that out, code adjustment made.

Also, not sure why my location was shown as London, I have adjusted that too.

Belinea2010, before you go crazy please attach a set of files to try to analyze your case. It seems that you need an xlsm file and two-three text files. Make sure you don't make sensitive data public before sending. Zip the whole thing into a ZIP file and attach it to the post. Follow the yellow banner at the top of the page to attach the file.

Artik

Originally Posted by Artik

Belinea2010, before you go crazy please attach a set of files to try to analyze your case. It seems that you need an xlsm file and two-three text files. Make sure you don't make sensitive data public before sending. Zip the whole thing into a ZIP file and attach it to the post. Follow the yellow banner at the top of the page to attach the file.

Artik

I have spent the evening testing and it still leads back to that Run-time error.

One thought I have is that I use two querys, one to import the text file, and the second to pivot the data so maybe that is the issue in some way.

Thank you for your kind offer and I will prepare the test xlsm and test data.

It may take me an evening or two to do it but I will get it done and write up a post here of everything I have tried since my last post.

Thanks again as it is very much appreciated.

Originally Posted by TMS

Thanks for the rep.

You are more than welcome.

Originally Posted by Artik

Belinea2010, before you go crazy please attach a set of files to try to analyze your case. It seems that you need an xlsm file and two-three text files. Make sure you don't make sensitive data public before sending. Zip the whole thing into a ZIP file and attach it to the post. Follow the yellow banner at the top of the page to attach the file.

Artik

It has taken me longer then I thought it would but I have created and uploaded an Example.xlsm file and three small text files.
The test data in the delimited text files came from an online "test data" generator so although it looks real, it is completely fake.

If you look in the either the Modules "Testing_Import_File or Testing_Atiks_Code or in the macro list there are two dummy subs called "A_read_Me" and "B_Read_Me" which I use to make/keep my notes on what each sub does.

"A_read_Me"
This is my code without your code suggestion and so I can tell them apart I use the prefix "A_" in the Sub names.
In here are my comments and explanation together with the order that the subs should be run.

"B_Read_Me"
This is your code conbined with the import code in-between the

Please Login or Register  to view this content.

as you stipulated and the other subs that are called after the import code.
In here are my comments and explanation together with the order that the subs should be run.

I have made as many comments as I can in each sub which I hope explains what each is supposed to do.

The same issue always happens with both sets of code on the same line of code in the "A_RangeToTable" and "B_RangeToTable" subs.

Code line

Please Login or Register  to view this content.

The error "Run-time error '1004': A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table"

Thank you again and if I can provide any further information just ask.

Originally Posted by Keebellah

Hi, I have been following this post and tested it just now using Excel 2021, I ran the modules in Testing_Artiks_Code and no problems or errors
When prompted I entered a sheet name in this case PQ_Import and it works

Hi Keebellah

That is good of you, thank you.

My I ask if you only imported 1 of the test sheets or did you try and import a 2nd after the first?

In the Example workbook, both sets of code will always import 1 text file without issue but if you
try and import a 2nd it will give the error.

Later on today I am going to make a screen capture video so I can show exactly what happens at my side.

Originally Posted by Keebellah

What is it you're trying to achieve? Import all three and have the result in one table?

Hi Keebellah.

I have the need to import the delimited text files on a regular basis so I wanted to automate it as much as I can in Excel/

My aim was to be able to import the text files exactly how the first one works but any number of text files with out any limits
other then those imposed by excel its self.

One day I may need to import 1 file, then next day 2 or 3, the next day none, the next day 4, and so on.


I understand that its the table1 name thats causing the issue but wont table1 still remain after the workbook has
been saved, closed and reopened?

Originally Posted by Keebellah

A table remains a table as long as it's there. You cannot have two tables with the same name, that's why I asked if you append data to an existing table or do you want to create a new table for each import?
Your code will have to be adapted accordingly.
By the way, I cannot open the vdieo since it's private

Sorry about that, I forgot to change it from private.

Updated the link so it should work now.

I must admit that I am confused and I am trying to understand what is going on.

I know that you can only have 1 table with each name, you can not have 2 with the same name.

But what happens to a table when you change it from a table to a range?

If you check in the Connections/tables or the querys list Excel shows that there are none
so shouldn't that make the table name available to be used again?

As you say, a table remains as long as it is there, so if the table is removed then that must mean it is gone along with its name.

Artiks code + my remove connections deletes any tables in the workbook but does Excel keep the table
name despite this?

I have tried deleting all files in

Please Login or Register  to view this content.

and in my Excel cach location but that does nothing.

It seems wrong that the only way a user can remove a table name is to close and then reopen a workbook.

Originally Posted by TMS

Maybe take a different approach.

1. Put all the text files in one folder.
2. Use a dialogue box to locate the folder.
3. Loop through all the text files in the folder:
3a. Import each file
3b. Transfer each file to its own sheet
4. When all the files have been imported, loop through each sheet:
4a. Prepare the sheet
4b. Convert to a Table
4c. Process with Power Query
4d. Output to a separate workbook/file
5. Post process:
5a. Remove all links, connections, queries, tables, Named Ranges, whatever
5b. Delete all worksheets except the Import sheet

Hopefully, at this point, you will have a set of processed files.

I would suggest you ask for the output folder and "base" file name once, and increment the output file names.

Just a thought, given the process so far seems to be in a rut.

I may just have to try a different approach which is brutal considering I have been working on this issue for 3+ weeks now.

I am already doing your suggested steps 1 to 4a and that works fine, it is when I try to use Power Query via VBA that it all goes wrong.

I may go on to the MS Tech community forum and ask if it is possible to completely remove a table, links, names everything using VBA
without having to close and reopen the workbook.

It won't be a cross post as I will ask the specific question if that is Okay.

Belinea2010, sorry, but I've been very busy.
I have analyzed your project. It turned out that the problem was caused by the fact that you imported a text file by creating a query without creating a data table but with the creation of a connection. In such a case, the RemoveConnections procedure removes all connections and queries, but for a reason unknown to me, such a connection and query deactivates not at the time of removal with the macro, but only after the file is closed. After executing the RemoveConnections procedure, I still saw the TestData_1 connection in the Queries and Connections panel.
One of the tests that guided me to the source of the problem looked like this:
1. I ran the Rev4_Import_TextFile procedure, but without calling the RemoveConnections procedure.
2. I manually removed the TestData_1 connection.
3. I ran further procedures until the ConvertRangeToTable procedure ran. It was, of course, interrupted with the error "A Table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table" because there is a query result in the range into which we wanted to insert the table.
4 Now I ran the RemoveConnections procedure, which removed the query.
5. I ran ConvertRangeToTable again and now the table was created without problems.

Conclusion from this test. In this specific case of creating a query and connection when importing data from a text file without creating a data table, the code execution does not behave as expected. Despite programmatically removing the query and connection, they remain active until the workbook is closed. This problem does not occur if you create a table during the import. Programmatically removing the query and connection works as expected.

Here is a new version that should no longer cause problems. I am almost sure that the whole task can be done in PowerQuery. But I don't feel comfortable using this tool. I made an attempt to create the code in PQ, but it was an unsuccessful attempt.

Please Login or Register  to view this content.

The procedure requires the presence of RemoveConnections and PowerQuery.

Artik

Originally Posted by Artik

Belinea2010, sorry, but I've been very busy.

Hi Artik.

No need to apoligise for anything as all of the specialists and contributers give thier time, share thier knowledge and help free of charge and life must come first.

I am grateful to everyone because with out all of you guys n girls forums like this would not exist.

Originally Posted by Artik

I have analyzed your project. It turned out that the problem was caused by the fact that you imported a text file by creating a query without creating a data table but with the creation of a connection. In such a case, the RemoveConnections procedure removes all connections and queries, but for a reason unknown to me, such a connection and query deactivates not at the time of removal with the macro, but only after the file is closed. After executing the RemoveConnections procedure, I still saw the TestData_1 connection in the Queries and Connections panel.
One of the tests that guided me to the source of the problem looked like this:
1. I ran the Rev4_Import_TextFile procedure, but without calling the RemoveConnections procedure.
2. I manually removed the TestData_1 connection.
3. I ran further procedures until the ConvertRangeToTable procedure ran. It was, of course, interrupted with the error "A Table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table" because there is a query result in the range into which we wanted to insert the table.
4 Now I ran the RemoveConnections procedure, which removed the query.
5. I ran ConvertRangeToTable again and now the table was created without problems.

Conclusion from this test. In this specific case of creating a query and connection when importing data from a text file without creating a data table, the code execution does not behave as expected. Despite programmatically removing the query and connection, they remain active until the workbook is closed. This problem does not occur if you create a table during the import. Programmatically removing the query and connection works as expected.

Thank you for doing this and I must admit I was frustrated as I could not work out why it was happening and I hate having to ask for help.

I am exactly the same with PQ as I am still trying to learn VBA but PQ is a whole new level of black magic.

Originally Posted by Artik

Here is a new version that should no longer cause problems. I am almost sure that the whole task can be done in PowerQuery. But I don't feel comfortable using this tool. I made an attempt to create the code in PQ, but it was an unsuccessful attempt.

Please Login or Register  to view this content.

The procedure requires the presence of RemoveConnections and PowerQuery.

Artik

Wow, you must have spent a lot of time on this and I can not thank you enough and the same goes for everyone.
This code is well above anything I would have thought of.

I have spent this evening trying your code and I did get 3 errors which sadly I did not note down but I worked through them and with a couple of little changes (I hope yo9u do not mind) your code runs faultlessly.

The error might have been caused by my OS, or version of Excel or even something in the real data files but I have now been able to import 10 full text files of the real data (they are very large) and all 10 imported one after the other quickly and without any errors.

I can not under state how happy this has made me or my gratitude for what you kindly have done.
I have been working on this code most evenings for the last 3 weeks, I went to bed with it on my mind, I woke up thinking about it and it was on my mind all day at work.

For no other reason other than so you can see the small changes I made, I have put the code that works for me below and I have commented on what I changed / added or removed.

Please Login or Register  to view this content.

Artik, thank you again for doing this.

Originally Posted by Keebellah

Got the video now

Thank you

I would like to say thank you to everyone who has responded to my post.

It never fails to amaze me that you all kindly give your time to help people and I genuinely thank you all.

This line of code is a safeguard in case Table1 is filtered.

Please Login or Register  to view this content.

Once a table has a criterion selected, it cannot be deleted. Unless you delete the "Import" sheet beforehand, then it doesn't matter in that case.

Something is missing from this fragment:

Please Login or Register  to view this content.

I think it was supposed to be:

Please Login or Register  to view this content.

And now it's time for my mistake.
I wrote:

Please Login or Register  to view this content.

what you corrected to:

Please Login or Register  to view this content.

In my case, the line: Set rngVis = LO.DataBodyRange.... is redundant. But you rightly pointed out that in the lack of filtering results we will get a runtime error. In your code, the highlighted loop declaration notation is accepted by the VBA language, but I do not recommend using it. When analyzing the code, this notation is confusing. Or repeat "... in LO.DataBodyRange...", or use another variable.
You could also write this larger passage this way:

Please Login or Register  to view this content.

We simply ignore all errors.

Artik