LucasLondon
06-11-2007, 04:40 AM
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
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
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
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