Import CSV file and problems with breaking external data link

moradisndat

New Member
Joined
Feb 19, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Using Excel 2007. Having a few problems. I need to make a workbook that will automatically import a log file and present a few different charts to choose from. A user form will present options to view charts, print, email and save as pdf. I'm using FileDialog(msoFileDialogFilePicker) and QueryTables.Add to import CSV file. I noticed a growing list of entries in workbook connections and ExternalData in Name Manager. It was adding one each time I opened the workbook. I thought it had something to do with the parameters in the QueryTables.Add statement. So I experimented with changing them around a bit but that didn't help.

The other problem is when the file is imported and I try to convert it to a table, I get a pop-up msg about "selection overlaps one or more external data ranges." So I tried copying the imported data and paste special "values only" to a different area of the worksheet and deleting the original. Didn't work.

Found some code snippets online to remove connections and break external links. One of them works to remove connection but "External Data" is still showing in Name Manager. I've been reading the help files and still unable to figure it out. And here's the thing that has me totally confused. I just read something in the application help menu that says the "Edit Links" command on the "Data" tab is unavailable if my file does not contain linked information. Edit Links is grayed out in my application, yet I see something in workbook connections and name manager. (please see attached image)

How do I break the links to external data so that I can convert the imported data to a table?


externaldata.gif

Also, does the query table.add statement look ok? (attached image)

Capture.GIF

Any help is greatly appreciated. Thank you for your time.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rahulismyname

New Member
Joined
Mar 23, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
For breaking the links, just delete any name ranges that has been created and are being linked to other data sources. You can do that by going into the Name manager and selecting and deleting all. Let me know if that works and solves the issue.
 

moradisndat

New Member
Joined
Feb 19, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
For breaking the links, just delete any name ranges that has been created and are being linked to other data sources. You can do that by going into the Name manager and selecting and deleting all. Let me know if that works and solves the issue.
Thanks for your reply. I know about deleting the links in name manager and connections tabs and have been doing so as I continue to test each time I modify the code. I need to know how to do it programmatically with VBA in the module. This is supposed to be automated so that it runs smoothly for the user who will be importing the log files.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,326
Messages
5,641,539
Members
417,217
Latest member
nextlove

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