Excel - VBA disable query link in code after csv import

[remeng]

Hi All;

I found the following code online and it does what I generally need. There is a minor feature that I'd like to disable though. If you delete all of the cells in "data" I get a warning message:

"The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition to the range? If you click No, the query will retrieve new data to the worksheet the next time the query is refreshed."​

Instead of linking the data in the spreadsheet to the .csv file permanently, is there a way to just import the csv file data and break the link?

Also, if the user cancels the file selection, the code errors out.

Thanks,

Mike

Sub load_csv()

MsgBox "Please Select the .CSV File to be Analysed"

Dim ws As Worksheet, strFile As String

    Set ws = ActiveWorkbook.Sheets("data") 'set to data sheet

    strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
         .TextFileParseType = xlDelimited
         .TextFileCommaDelimiter = True
         .Refresh
    
    End With

MsgBox "The CSV file has loaded sucessfully."


End Sub

 

[SkipVought]

Hi,

If you delete all of the cells in "data" I get a warning message:

???
What do you want to delete the QueryTable? The beauty of it is that it can be Refreshed.

Even if you need to get data from another file, that can be done.

Regarding the cancel part, yes, this code needs some flexibility. But lets address the main complaint first. What are you trying to accomplish?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[remeng]

Hi Skip,

Thank you again for the help. The user needs to select the CSV file at every use. Basically the user is going to take the imported csv file, edit it, then export it as a different file.

The file brought into the WS needs to be stand alone.

Should I just have the macro copy the worksheet and past as value or is there some other way to do it?

Thanks,

Mike

 

[combo]

You can delete QT finally, imported data will be preserved:

[pre] With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
.Delete
End With[/pre]

combo

 

[remeng]

Hi Combo,

Thanks for the correction to the macro. I am still having the issue when someone hits cancel, there is an error or a 400 error which crashes the macro. Any way to fix that?

Mike

 

[combo]

Dim strFile as Variant, if the user cancels dialog, strFile=False. Test this before processing proper file name.

combo

 

[SkipVought]

SELECT the entire table.
COPY
PASTE as Values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Skip,
Did you know:
>SELECT the entire table
Right-Click anywhere on the edge of selected region (when you get this cursor)

Drag the selection to the right one cell, then drag it back
Release the mouse's right button
You can now select "Copy Here as Values Only"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Thank you combo. That worked!

 

[SkipVought]

@Andy,

I use the CurrentRegion icon.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein