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

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

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

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