Overwriting prior formula

Mark · 08-15-2006, 09:30 AM

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:\count\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

CLR · 08-15-2006, 09:50 AM

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:\count\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
>
>
>
>

Mark · 08-15-2006, 10:00 AM

TYVM!
I knew it was something simple, but my simple mind couldn't think of it.

Thanks, again!

Mark

"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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:\count\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
> >
> >
> >
> >

CLR · 08-15-2006, 10:05 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:\count\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
> > >
> > >
> > >
> > >
>
>
>