Excel External Data into a Table

Question

I am working on an excel spreadsheet that takes data from a CSV file (produced automatically by an external system). I have used:

Data->Get External Data->From Text

And it works perfect !

However i am not able to format the imported data as a table :-(

It gives the following message :

Your Selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connections?

Is there a way to format the imported data as a table wthout breaking the connection ?

Thanks Martin

Answer 1

This should work for you - make sure you have a tab called Data and you change the public const to the path of the file. I assume you know what to do with this code, if not let me know.

Public Const feedDir = "C:\Program Files\Common Files\System\data.csv"  'change this to the path of the file

Sub loadDataWrapper()
'''check file is in directory before proceding
If Dir(feedDir) <> "" Then
    fileToLoad = feedDir
Else
    MsgBox "No file available to load. Please check the path and try again."
    Exit Sub
End If

Call loadData(fileToLoad)

End Sub

Sub loadData(ByVal fileToLoad As String)
Dim fso As Object, textFile As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim textFileStr As String
Dim textFileArr As Variant
Dim outputArr() As Variant
Dim oneRow As Variant
Dim numRows, numColumns As Long

'''open the text file and read into memory as is
Set textFile = fso.OpenTextFile(fileToLoad, 1)
textFileStr = textFile.ReadAll
textFile.Close

Set textFile = Nothing
Set fso = Nothing

'''find number of rows and columns of text file
textFileArr = Split(textFileStr, Chr(10))
numRows = UBound(textFileArr)
numColumns = UBound(Split(textFileArr(0), ","))
ReDim outputArr(numRows, numColumns)

'''go through every line and insert into array
For ii = 0 To (numRows - 1)
    oneRow = Split(textFileArr(ii), ",")
    For jj = 0 To numColumns
    outputArr(ii, jj) = oneRow(jj)

    Next jj
Next ii

'''output array to Worksheet
Worksheets("Data").Range("A2:Z1048576").ClearContents
Worksheets("Data").Range("A2").Resize(numRows + 1, numColumns + 1).Value = outputArr


End Sub