CODEWITHSUNDEEP
excelexcel-2007vba
John
John

Reputation: 189

Excel External Data into a Table

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

Upvotes: 9

Views: 6327

Answers (2)

Tabias
Tabias

Reputation: 100

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

Upvotes: 2

cronos2546
cronos2546

Reputation: 1106

Would a Pivot Table satisfy your requirement? 

Insert>PivotTable>Use External Data Source Radio Button

Upvotes: 0

Related Questions