Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overwriting prior formula
I'm trying to setup a code to import a .txt file into Excel. I have the
import code programmed into a button on Sheet 1 that imports the file into Sheet 2 starting in cell A1, using four columns through D1. My problem is that when I try to update the .txt file and re-import it, it moves to E1 through H1, then I1 through L1, etc., and screws up my other formulas that rely on this information. How can I set this formula to overwrite the previous information and not move to the next available space? Here's a copy of the code: Sub importlist() Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;P:\c ount\count.txt", _ Destination:=Range("A1")) .Name = "count" .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 = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileFixedColumnWidths = Array(7, 25, 2) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub TIA.... Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overwriting prior formula
Try erasing the old information first, right after you select Sheet2.......
Range("A1:D1").ClearContents hth Vaya con Dios, Chuck, CABGx3 "Mark" wrote: I'm trying to setup a code to import a .txt file into Excel. I have the import code programmed into a button on Sheet 1 that imports the file into Sheet 2 starting in cell A1, using four columns through D1. My problem is that when I try to update the .txt file and re-import it, it moves to E1 through H1, then I1 through L1, etc., and screws up my other formulas that rely on this information. How can I set this formula to overwrite the previous information and not move to the next available space? Here's a copy of the code: Sub importlist() Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;P:\c ount\count.txt", _ Destination:=Range("A1")) .Name = "count" .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 = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileFixedColumnWidths = Array(7, 25, 2) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub TIA.... Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overwriting prior formula
TYVM!
I knew it was something simple, but my simple mind couldn't think of it. Thanks, again! Mark "CLR" wrote in message ... Try erasing the old information first, right after you select Sheet2....... Range("A1:D1").ClearContents hth Vaya con Dios, Chuck, CABGx3 "Mark" wrote: I'm trying to setup a code to import a .txt file into Excel. I have the import code programmed into a button on Sheet 1 that imports the file into Sheet 2 starting in cell A1, using four columns through D1. My problem is that when I try to update the .txt file and re-import it, it moves to E1 through H1, then I1 through L1, etc., and screws up my other formulas that rely on this information. How can I set this formula to overwrite the previous information and not move to the next available space? Here's a copy of the code: Sub importlist() Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;P:\c ount\count.txt", _ Destination:=Range("A1")) .Name = "count" .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 = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileFixedColumnWidths = Array(7, 25, 2) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub TIA.... Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overwriting prior formula
You're quite welcome..........I've been automating a bunch of Queries lately
and have become just as frustrated until I fell upon the answer.........thanks for the feedback. Vaya con Dios, Chuck, CABGx3 "Mark" wrote: TYVM! I knew it was something simple, but my simple mind couldn't think of it. Thanks, again! Mark "CLR" wrote in message ... Try erasing the old information first, right after you select Sheet2....... Range("A1:D1").ClearContents hth Vaya con Dios, Chuck, CABGx3 "Mark" wrote: I'm trying to setup a code to import a .txt file into Excel. I have the import code programmed into a button on Sheet 1 that imports the file into Sheet 2 starting in cell A1, using four columns through D1. My problem is that when I try to update the .txt file and re-import it, it moves to E1 through H1, then I1 through L1, etc., and screws up my other formulas that rely on this information. How can I set this formula to overwrite the previous information and not move to the next available space? Here's a copy of the code: Sub importlist() Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;P:\c ount\count.txt", _ Destination:=Range("A1")) .Name = "count" .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 = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileFixedColumnWidths = Array(7, 25, 2) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub TIA.... Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overwriting Duplicates in certain cases | New Users to Excel | |||
Overwriting data | Excel Discussion (Misc queries) | |||
Copying format to a new cell, w/o overwriting destination cell contents | Excel Discussion (Misc queries) | |||
Overwriting error message #NUM! with meaningful text | Excel Discussion (Misc queries) | |||
Overwriting Cells | Excel Discussion (Misc queries) |