Copying data from a closed CSV on my desktop *without* opening the file?

[d0rian]

Here's what I currently use (which works, but I'd prefer getting the data without windows flickering / files opening and closing, etc). I just want to copy columns A:N from the closed CSV and paste them to columns A:N in the sheet named "CSV_paste" of the active workbook. (It would also be fine if the code copied a range like "A1:N500" if grabbing the entire columns is problematic...though I don't see why it would be.)

Sub Macro_1()
    Workbooks.Open Filename:="C:\Users\Desktop\port_export.csv"
    Columns("A:N").Copy
    ThisWorkbook.Activate
    With Sheets("CSV_paste")
        .Range("A:N").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
    Windows("port_export.csv").Close
End Sub

 

[John_w]

Try importing the CSV file via Data tab->From Text, and run the Text Import Wizard. It allows you to skip columns. Record the steps as a macro if you need to tweak the import.

 

[d0rian]

Thanks, I did this and got it working, more or less. But how / where can i specify what range I want to grab/copy from the CSV file? Let's say, for example, that I want it to copy A1:N500 from the source CSV and paste that to A1:N500 of the active workbook? Nowhere in the Import-Wizard did I see an option to specify a range to go grab...and looking at the VBA that my macro recorded (below) it's not clear I can specify that anywhere...

Sub Wizard_macro()    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Mike\Desktop\port_export.csv", Destination:=Range( _
        "$A$1"))
        .Name = "port_export"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[John_w]

You can't specify the range with a text query, only the starting row and the columns to import. You can clear all the imported cells after the 500th row by adding this code after the End With:

    Dim qt As QueryTable
    Set qt = ActiveSheet.QueryTables(1)
    qt.ResultRange.Item(501, 1).Resize(qt.ResultRange.Rows.Count - 501 + 1, qt.ResultRange.Columns.Count).ClearContents

 

[d0rian]

You can't specify the range with a text query, only the starting row and the columns to import. You can clear all the imported cells after the 500th row by adding this code after the End With:

    Dim qt As QueryTable
    Set qt = ActiveSheet.QueryTables(1)
    qt.ResultRange.Item(501, 1).Resize(qt.ResultRange.Rows.Count - 501 + 1, qt.ResultRange.Columns.Count).ClearContents

Replying to this thread a month later, b/c I just noticed in my Name Manager that my file has several hundred new Named Ranges titled:
port_export_MyWizMacro_1
port_export_MyWizMacro_2
port_export_MyWizMacro_3
etc etc (up to port_export_MyWizMacro_322)

Basically it's creating a new named range with an appended number every time I run it (so I've run it 322 times now...)

Why is it doing that, and is that necessary? Is the new named range it creates required for proper functioning (b/c I'd rather not have to scroll through hundreds of these new ranges when I'm working in Name Manager...)

 

[John_w]

It's doing that because the code adds a text query (QueryTable) to the sheet every time it's run. Normally this isn't necessary (and the named range isn't needed) because you would add a text query (or run the code which adds a text query) once and then manually refresh it to import the current data from the .csv file, overwriting the current data on the sheet. However, in your case you don't want to import all the rows, only the first 500, as I understand it, and this requires the three extra lines of code in my previous post, which means a simple manual refresh won't suffice.

First run this macro to delete all the port_export_MyWizMacro_* text queries on the active sheet (this doesn't delete the data and doesn't affect other named ranges):

Public Sub Delete_All_MyWiz_Queries()
    Dim i As Long
    With ActiveSheet
        For i = .QueryTables.Count To 1 Step -1
            If .QueryTables(i).Name Like "port_export_MyWizMacro_*" Then .QueryTables(i).Delete
        Next
    End With
End Sub

Then add the following line at the bottom of the code in my previous post to delete the text query added by the macro:

        qt.Delete

With this, the complete macro will import the current .csv data and delete the newly added query/named range.

 

[d0rian]

It's doing that because the code adds a text query (QueryTable) to the sheet every time it's run. Normally this isn't necessary (and the named range isn't needed) because you would add a text query (or run the code which adds a text query) once and then manually refresh it to import the current data from the .csv file, overwriting the current data on the sheet. However, in your case you don't want to import all the rows, only the first 500, as I understand it, and this requires the three extra lines of code in my previous post, which means a simple manual refresh won't suffice.

First run this macro to delete all the port_export_MyWizMacro_* text queries on the active sheet (this doesn't delete the data and doesn't affect other named ranges):

Public Sub Delete_All_MyWiz_Queries()
    Dim i As Long
    With ActiveSheet
        For i = .QueryTables.Count To 1 Step -1
            If .QueryTables(i).Name Like "port_export_MyWizMacro_*" Then .QueryTables(i).Delete
        Next
    End With
End Sub

Then add the following line at the bottom of the code in my previous post to delete the text query added by the macro:

        qt.Delete

With this, the complete macro will import the current .csv data and delete the newly added query/named range.

Thanks, I'll give it a go. BTW, I also just identified this code as the source of a slowdown that I couldn't figure out for several weeks, but just solved with some help in this thread. It seems that in addition to creating a new Named Range each time I ran the import code, it was also creating a new Data Connection (such that my file had ~320 different such connections listed in the Data Connections window.) Will the fix you suggested above for the multiple-named-range thing also address this multiple-data-connections-being-created thing that was slowing my file open/save down to a crawl? I guess I don't really understand why:
i) the code needs to create a new Data Connection each time it runs, but more to the point I guess:
ii) why having hundreds of these Data Connections would slow my file down to the extent they were...like they're not active connections, right? I thought they were just executing a one-off import of the data in the closed CSV file each time I ran it...but seems like whatever Connections were being created were bogging down the file too...

 

[John_w]

Thanks, I'll give it a go. BTW, I also just identified this code as the source of a slowdown that I couldn't figure out for several weeks, but just solved with some help in this thread. It seems that in addition to creating a new Named Range each time I ran the import code, it was also creating a new Data Connection (such that my file had ~320 different such connections listed in the Data Connections window.) Will the fix you suggested above for the multiple-named-range thing also address this multiple-data-connections-being-created thing that was slowing my file open/save down to a crawl?

Yes, the Delete_All_MyWiz_Queries macro will also delete the data connections on the active sheet whose name matches "port_export_MyWizMacro_*".

I guess I don't really understand why:
i) the code needs to create a new Data Connection each time it runs, but more to the point I guess:
ii) why having hundreds of these Data Connections would slow my file down to the extent they were...like they're not active connections, right? I thought they were just executing a one-off import of the data in the closed CSV file each time I ran it...but seems like whatever Connections were being created were bogging down the file too...

i) Each new Data Connection is a consequence of creating the text query in your Wizard_macro code. My revision (with the qt.Delete) to your Wizard_macro macro deletes the unwanted rows and deletes the text query/data connection, meaning that no text queries/data connections (created by that macro) are left in the workbook. The alternative is two separate macros: one which creates a text query to the .csv file, which you run only once; and another macro which refreshes the single text query and deletes the unwanted rows, which you run each time you want to import the .csv data.

ii) I don't know why it is slow, but the workbook file size would certainly increase because there are many properties (i.e. data bytes) associated with a text query.