Importing CSV Files Without Preserving External Links

[LucasLondon]

Hi, I have the recorded excel macro below that imports a CSV file into a excel worksheet with an external link.

Does anyone know how I can import the file without maintaining a constant link to the source CSV file, once I've imported it, I can turn off this link manually by unclicking save query definition on the data range properties tool on external data toolbar. But I need to do it using VBA as I have several files to import on a regular basis.

I've checked the import code to see if I there is an option to disable the link but one does not seem to exist.

Do you know if this is possible?

Basically, I use the below procedure to update data in various sheets in a workbook. The raw CSV data file contains the latest data which I import and than append (using another macro) to my existing data.

Many Thanks

Lucas

[VBA]Sub import_File7()
Sheets("File7").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\Trading\File7.csv", _
Destination:=Sheets("File7").Range("A1").End(xlDown).Offset(1, 0)) 'Goes down to the end of the row and imports there
.Name = "^File7"
.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 = 2 'tells to import from from row two only, leaves out field names
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 9) '9 denotes don't import this coloum (Adj Close)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
Call Sortbydate
Call CreateUniqueList
End With
End Sub[/VBA]

[Ebrow]

Here is a different way to look at importing your data.

This will work for any type of file that is in a text format. (.csv,.dat,.txt,...)

I created a csv file with 5 fields with numerous records. I have saved the file as myTest.csv and placed it into my C:\

This test sub calls the function and return the number of records imported. This will not leave any connections to your csv file once imported.

The first parameter is the range to place the data.
The second parameter is the range of the source file.

[vba]

Sub test()
MsgBox "sucessfully imported: " & importFile("A1","C:\myTest.csv") & " records"
End Sub

[/vba]
here is the function. Just place it in a module. The sub and the function don't need to be located in the same place. You can add more variables if you have more fields.

[vba]

Function importFile(myRange as range, myFilePath As String) As Long

Dim myValue1 As String, myValue2 As String, myValue3 As String
Dim myValue4 As String, myValue5 As String

Dim myRecordCount As Long: myRecordCount = 0

Open myFilePath For Input As #1 ' Open file for input.

Range(myRange).Select

Do While Not EOF(1) ' Loop until end of file.
Input #1, myValue1, myValue2, myValue3, myValue4, myValue5 'variable for each data component.

Selection.Offset(myRecordCount, 0).Value = myValue1 'you can change the offset to suit the field order you want
Selection.Offset(myRecordCount, 1).Value = myValue2
Selection.Offset(myRecordCount, 2).Value = myValue3
Selection.Offset(myRecordCount, 3).Value = myValue4
Selection.Offset(myRecordCount, 4).Value = myValue5

myRecordCount = myRecordCount + 1
Loop

importFile = myRecordCount
Close #1 ' Close file

End Function

[/vba]

[Ebrow]

sorry typo in the code:,

change this

[VBA]Function importFile(myRange as range, myFilepath as string) as long[/VBA]

with:

[VBA]Function importFile(myRange as string, myFilepath as string) as long[/VBA]

[geekgirlau]

After the data import,

[vba]Selection.QueryTable.Delete[/vba]

[lucas]

I like ebrow's method...it's fast too