Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import XML data...Is there a size limit on the import? | Excel Discussion (Misc queries) | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
PLEASE!! How can I un-grey the 'Data-Import External Data-Import Data' option ??? | Excel Programming | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming | |||
Best Course to Import XML with DTD... | Excel Programming |