×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - VBA disable query link in code after csv import

Excel - VBA disable query link in code after csv import

Excel - VBA disable query link in code after csv import

(OP)
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

CODE -->

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 

RE: Excel - VBA disable query link in code after csv import

Hi,

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - VBA disable query link in code after csv import

(OP)
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

RE: Excel - VBA disable query link in code after csv import

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

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

combo

RE: Excel - VBA disable query link in code after csv import

(OP)
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

RE: Excel - VBA disable query link in code after csv import

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

combo

RE: Excel - VBA disable query link in code after csv import

SELECT the entire table.
COPY
PASTE as Values.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - VBA disable query link in code after csv import

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" pc1

---- Andy

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

RE: Excel - VBA disable query link in code after csv import

(OP)
Thank you combo. That worked!

RE: Excel - VBA disable query link in code after csv import

@Andy,

I use the CurrentRegion icon.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close