Best way to import .csv
 

I need to import a .csv file to a specific destination in an existing
spreadsheet. I have code that works that I've used before, but I end up with
incrementing named ranges such as bake_1, bake_2, etc. that I then need to
delete. I also have some code that will search for those names and delete
them, but I wondered if there was a better way to do the import. The code
I'm using to import is just recorded from the Data - Get External data
commands. I'm developing this in Excel 2000 to be used on PCs using Excel
2003. I've included the import code below. Thanks for your help!
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lotus\work\123\BAKE.csv", Destination:=Range("AC1"))
.Name = "BAKE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
End Sub

Best way to import .csv
 

for each nm in Activesheet.parent.Names
if instr(1,nm.name,"bake",vbTextcompare) then
nm.Delete
end if
Next
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lotus\work\123\BAKE.csv", Destination:=Range("AC1"))
.Name = "BAKE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
End Sub

--
Regards,
Tom Ogilvy


"cottage6" wrote:

I need to import a .csv file to a specific destination in an existing
spreadsheet. I have code that works that I've used before, but I end up with
incrementing named ranges such as bake_1, bake_2, etc. that I then need to
delete. I also have some code that will search for those names and delete
them, but I wondered if there was a better way to do the import. The code
I'm using to import is just recorded from the Data - Get External data
commands. I'm developing this in Excel 2000 to be used on PCs using Excel
2003. I've included the import code below. Thanks for your help!
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lotus\work\123\BAKE.csv", Destination:=Range("AC1"))
.Name = "BAKE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
End Sub

Best way to import .csv
 

Thanks very much Tom. Your code to delete the names is much shorter than
mine, and works great. Have a good day!

"Tom Ogilvy" wrote:

for each nm in Activesheet.parent.Names
if instr(1,nm.name,"bake",vbTextcompare) then
nm.Delete
end if
Next
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lotus\work\123\BAKE.csv", Destination:=Range("AC1"))
.Name = "BAKE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
End Sub

--
Regards,
Tom Ogilvy


"cottage6" wrote:

I need to import a .csv file to a specific destination in an existing
spreadsheet. I have code that works that I've used before, but I end up with
incrementing named ranges such as bake_1, bake_2, etc. that I then need to
delete. I also have some code that will search for those names and delete
them, but I wondered if there was a better way to do the import. The code
I'm using to import is just recorded from the Data - Get External data
commands. I'm developing this in Excel 2000 to be used on PCs using Excel
2003. I've included the import code below. Thanks for your help!
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lotus\work\123\BAKE.csv", Destination:=Range("AC1"))
.Name = "BAKE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
End Sub