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
> >
|
Similar Threads
