VBA Copy data from an unopened CSV file to worksheet without opening closed CSV

Question

I believe I have a unique problem as I have not seen anything like it anywhere on the Internet.

I am a business analyst/application developer and I want to automatically gather the data from other user's Excel CSV file on their personal computer without opening the file and disrupting them. Is there a way?

Here is the code I have so far:

Option Explicit

Dim MyDocuments As String, strFileName, myToday, origWorkbook, origWorksheet, strConnection
Dim row As Integer

Private Sub btnStart_Click()
    MyDocuments = Environ$("USERPROFILE") & "\My Documents"
    myToday = Format(Date, "mmddyy")
    strFileName = "DataFile" & myToday & ".csv"
    strConnection = "TEXT;" & MyDocuments & "\DataFolder\" & strFileName
    origWorksheet = "DataFile" & myToday

    row = 1
    On Error Resume Next
    row = Range("A1").End(xlDown).row + 1

    With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=Range("$A$" & row))
        .Name = "temp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Like I said, I don't want the CSV file to open while they are working. I would like this behind the scenes so they can keep working while we gather the data.

I'm guessing my biggest hang up is that it's a CSV file, that or that the file is not open. If there's a way this can be done, please let me know. Currently, I am getting an out of range error.

Could it be a setting of some sorts? Maybe in Excel? Every time I run the program, it puts the data in the next column. — Lou, Jul 15 '13 at 18:31
is there data in `Column A` of your CSV file. I just realized that if your first column is empty then no data will be copied into excel and the statement `row = Range("A1").End(xlDown).row + 1` will not work and the row variable will stay equal to 1. Change the `Range("A1")` to a column you know that data will be copied into so it actually counts the rows down to the next row — chancea, Jul 15 '13 at 20:03
I updated my example to add logic to first look for the first data towards the right of "A1" and then look to the end. — chancea, Jul 15 '13 at 20:08

chancea · Accepted Answer · 2013-07-15T20:07:42.663

Assuming that you want to just grab the data and put it in your current workbook. I recorded a macro using the Data -> Import Data method and in VBA and it seems to work with the CSV file closed:

Print to consecutive column:

Sub Macro1()

    Dim MyDocuments, strFileName, myToday, file, strConnection As String

    MyDocuments = Environ$("USERPROFILE") & "\My Documents"
    myToday = Format(Date, "mmddyy")
    strFileName = "DataFile" & myToday & ".csv"

    strConnection = "TEXT;" & MyDocuments & "\DataFolder\" & strFileName
    With ActiveSheet.QueryTables.Add(Connection:= _
         strConnection, Destination:=Range("$A$1"))
        .Name = "temp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Print to consecutive row:

Here we have to add

Dim row As Integer
    row = 1
    On Error Resume Next

    row = Range("A1").End(xlToRight).End(xlDown).row + 1

and then instead of: Destination:=Range("$A$1") we use the row variable: Destination:=Range($A$" & row)

Sub Macro1()

    Dim MyDocuments, strFileName, myToday, file, strConnection As String

    MyDocuments = Environ$("USERPROFILE") & "\My Documents"
    myToday = Format(Date, "mmddyy")
    strFileName = "DataFile" & myToday & ".csv"

    Dim row As Integer
    row = 1
    On Error Resume Next
    row = Range("A1").End(xlDown).row + 1

    strConnection = "TEXT;" & MyDocuments & "\DataFolder\" & strFileName
    With ActiveSheet.QueryTables.Add(Connection:= _
         strConnection, Destination:=Range("$A$" & row))
        .Name = "temp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

This will grab all of the CSV data and put it in A1 you can change the $A$1 to whatever location you want. Of course you can change all of the other variables also, I just recorded the macro and edited the strConnection variable to match the location you described in your question.

Hopefully this is what you are looking for, if not let me know.

Is the "TEXT;" a constant variable representing anything that I have within the Range of the closed CSV? — Lou, Jul 12 '13 at 14:45
TEXT is the format you are importing if you look at `Get External Data` you have different options like `From Access`, `From Web`, `From Text`, etc — chancea, Jul 12 '13 at 14:49
Ok...next question. It's printing any consecutive calls to the closed CSV in the columns to the right. How do I get it to print on the next empty row down? I believe I can use Offset(1, 0), correct? — Lou, Jul 12 '13 at 15:01
It's still printing in the consecutive column. I looked what you wrote over a couple times and I have everything...not sure what I missed. — Lou, Jul 12 '13 at 15:19
I don't know how to enter my code without starting a new question. I have everything you have and it still puts the data in the next column, not the next row. — Lou, Jul 12 '13 at 16:37
Can anyone help me? I have my code looking exactly like the code above and it constantly puts the data in the next empty column instead of the next empty row. — Lou, Jul 12 '13 at 18:18
@user2572769 I would need to see your code, perhaps edit your question and post it there — chancea, Jul 12 '13 at 18:20
@user2572769 I re-tested both the macros I posted and they are working fine for me as I described so I would need to see exactly what you have. — chancea, Jul 12 '13 at 18:34

VBA Copy data from an unopened CSV file to worksheet without opening closed CSV

1 Answers1

Linked