Every day I have to add a couple of hundred lines of data to the
bottom of a list.I then need to bring the complete list back to excel
to analyse it.
Keeping the data and manipulating it purely in excel is no use,
because of severe date formatting problems.

It has been suggested(in order to overcome date formatting
problems) to put the data each day into a TXT file.How do I move a
list of data in excel to the bottom of a list that exists in a text
file? The subject data may be a couple of hundred rows long and upto
16 columns wide.

Any useful code would be greatly appreciated.

Jason

Thanks for the reply Tim,

Once the data has been written to the text file I want to be able to
use an excel pivot table that runs a query on the data in the text
file.(I've been told this will be good as the text file will be able
to hold hundreds of thousands of lines of data, rather than the 65
thousand in excel)
Rather than using a reference to the Microsoft
Scripting Runtime I was going to try to adapt some code like the
below:

Option Explicit
Dim i, j, k, l, r, c, vdata
Sub exportRange()

Dim MyRange As Range
Set MyRange = ActiveCell.CurrentRegion

i = MyRange.Columns(1).Column
j = i + MyRange.Columns.Count - 1
k = MyRange.Rows(1).Row
l = k + MyRange.Rows.Count - 1

Open "C:\My Documents\TextFile.txt" For Append As #1
For r = i To j
For c = k To l
vdata = MyRange.Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub

I've tried running this on a simple table of two columns and 5
rows(with a couple of headers) and when I look in the text file NOT
all the original data is present - any ideas why this isn't working?

Thanks in advance

Jason.


"Tim" wrote in message ...
Jason,

You should be able to make a reference to the Microsoft
Scripting Runtime. Then use the FileSystemObject to
manipulate files (for instance appending a file or reading
from it). I've included a small example of using the FSO
below.

-Tim

Sub test()
Dim oFSO As FileSystemObject
Dim oTxt As TextStream

Set oFSO = New FileSystemObject
Set oTxt = oFSO.CreateTextFile("c:\test.txt", False)
oTxt.WriteLine "my text1"
oTxt.WriteLine "my text2"
oTxt.Close
Set oTxt = Nothing

If oFSO.FileExists("c:\test.txt") Then
Set oTxt = oFSO.OpenTextFile("c:\test.txt",
ForReading, False)
Do While Not oTxt.AtEndOfStream
MsgBox oTxt.ReadLine
DoEvents
Loop
End If
End Sub


-----Original Message-----
Every day I have to add a couple of hundred lines of data
to the
bottom of a list.I then need to bring the complete list
back to excel
to analyse it.
Keeping the data and manipulating it purely in excel is
no use,
because of severe date formatting problems.

It has been suggested(in order to overcome date
formatting
problems) to put the data each day into a TXT file.How do
I move a
list of data in excel to the bottom of a list that exists
in a text
file? The subject data may be a couple of hundred rows
long and upto
16 columns wide.

Any useful code would be greatly appreciated.

Jason
.

For r = i To j
For c = k To l

But you used i and j to represent columns. But you use i & j as a row.

Same with k & l and as rows/columns.

And depending on your current region, this line might not be what you want:

MyRange.Cells(r, c).Value

I put some random data in: C7:L29.
so my first cell is C7

This can be addressed as: activesheet.cells(7,3)
but not myrange.cells(7,3)
That says to take the top left cell (C7) and come down 6 and over 2 which gives
me: E13.

You could fix it by either going through the worksheet:

Option Explicit
Sub exportRange()

Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim r As Long
Dim c As Long
Dim vdata As Variant
Dim MyRange As Range

Set MyRange = ActiveCell.CurrentRegion
MsgBox MyRange.Cells(7, 3).Address

i = MyRange.Columns(1).Column
j = i + MyRange.Columns.Count - 1
k = MyRange.Rows(1).Row
l = k + MyRange.Rows.Count - 1

Open "C:\My Documents\excel\TextFile.txt" For Append As #1
For r = k To l
For c = i To j
vdata = Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub

or by changing you loop to treat the first column/row as 1 and go to the total
number of columns/rows:

Option Explicit
Sub exportRange()

Dim j As Long
Dim l As Long

Dim r As Long
Dim c As Long
Dim vdata As Variant
Dim MyRange As Range

Set MyRange = ActiveCell.CurrentRegion

l = MyRange.Rows.Count
j = MyRange.Columns.Count

Open "C:\My Documents\excel\TextFile.txt" For Append As #1
For r = 1 To l
For c = 1 To j
vdata = MyRange.Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub


jason wrote:

Thanks for the reply Tim,

Once the data has been written to the text file I want to be able to
use an excel pivot table that runs a query on the data in the text
file.(I've been told this will be good as the text file will be able
to hold hundreds of thousands of lines of data, rather than the 65
thousand in excel)
Rather than using a reference to the Microsoft
Scripting Runtime I was going to try to adapt some code like the
below:

Option Explicit
Dim i, j, k, l, r, c, vdata
Sub exportRange()

Dim MyRange As Range
Set MyRange = ActiveCell.CurrentRegion

i = MyRange.Columns(1).Column
j = i + MyRange.Columns.Count - 1
k = MyRange.Rows(1).Row
l = k + MyRange.Rows.Count - 1

Open "C:\My Documents\TextFile.txt" For Append As #1
For r = i To j
For c = k To l
vdata = MyRange.Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub

I've tried running this on a simple table of two columns and 5
rows(with a couple of headers) and when I look in the text file NOT
all the original data is present - any ideas why this isn't working?

Thanks in advance

Jason.

"Tim" wrote in message ...
Jason,

You should be able to make a reference to the Microsoft
Scripting Runtime. Then use the FileSystemObject to
manipulate files (for instance appending a file or reading
from it). I've included a small example of using the FSO
below.

-Tim

Sub test()
Dim oFSO As FileSystemObject
Dim oTxt As TextStream

Set oFSO = New FileSystemObject
Set oTxt = oFSO.CreateTextFile("c:\test.txt", False)
oTxt.WriteLine "my text1"
oTxt.WriteLine "my text2"
oTxt.Close
Set oTxt = Nothing

If oFSO.FileExists("c:\test.txt") Then
Set oTxt = oFSO.OpenTextFile("c:\test.txt",
ForReading, False)
Do While Not oTxt.AtEndOfStream
MsgBox oTxt.ReadLine
DoEvents
Loop
End If
End Sub


-----Original Message-----
Every day I have to add a couple of hundred lines of data
to the
bottom of a list.I then need to bring the complete list
back to excel
to analyse it.
Keeping the data and manipulating it purely in excel is
no use,
because of severe date formatting problems.

It has been suggested(in order to overcome date
formatting
problems) to put the data each day into a TXT file.How do
I move a
list of data in excel to the bottom of a list that exists
in a text
file? The subject data may be a couple of hundred rows
long and upto
16 columns wide.

Any useful code would be greatly appreciated.

Jason
.


--

Dave Peterson

I use xl2002 and I tried it yesterday.

I did
Data|Pivottable|External data source
(Next)
I clicked on Get Data
Databases tab
<new data source
Gave it a nice name (TextFiles)
Chose "microsoft text driver (*.txt, *.csv)
(Make sure you have headers in your text file)
Connect
Ok

and continued to follow the wizard (until I was confused!)

This was the first time I ever used it. So I don't think I could give you the
best way of doing it.

You may want to post back with specific questions to get much better answers
than I can give.

(And there are some Access users who hang around here, but there are dedicated
access newsgroups, too.)

Good luck,



jason wrote:

Cheers Dave - didn't realise the loops were wrong, but they work fine
now.

Ultimately this data could be huge e.g 150,000 lines, and I'd like to
use MS query to point an Excel pivot table at the data.Unfortunately
I've been told that the txt format will not support this, someone has
suggested writing it to an Access file.I know nothing about Access,
not even the file extension !! Once I've learnt how to create an
Access file can I use the same code below ("For Append As #1") to
write data to it (or will I need to change it again) , or is it
actually possible to point an excel pivot at a txt file after all?

TIA

Jason

Dave Peterson wrote in message ...
For r = i To j
For c = k To l

But you used i and j to represent columns. But you use i & j as a row.

Same with k & l and as rows/columns.

And depending on your current region, this line might not be what you want:

MyRange.Cells(r, c).Value

I put some random data in: C7:L29.
so my first cell is C7

This can be addressed as: activesheet.cells(7,3)
but not myrange.cells(7,3)
That says to take the top left cell (C7) and come down 6 and over 2 which gives
me: E13.

You could fix it by either going through the worksheet:

Option Explicit
Sub exportRange()

Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim r As Long
Dim c As Long
Dim vdata As Variant
Dim MyRange As Range

Set MyRange = ActiveCell.CurrentRegion
MsgBox MyRange.Cells(7, 3).Address

i = MyRange.Columns(1).Column
j = i + MyRange.Columns.Count - 1
k = MyRange.Rows(1).Row
l = k + MyRange.Rows.Count - 1

Open "C:\My Documents\excel\TextFile.txt" For Append As #1
For r = k To l
For c = i To j
vdata = Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub

or by changing you loop to treat the first column/row as 1 and go to the total
number of columns/rows:

Option Explicit
Sub exportRange()

Dim j As Long
Dim l As Long

Dim r As Long
Dim c As Long
Dim vdata As Variant
Dim MyRange As Range

Set MyRange = ActiveCell.CurrentRegion

l = MyRange.Rows.Count
j = MyRange.Columns.Count

Open "C:\My Documents\excel\TextFile.txt" For Append As #1
For r = 1 To l
For c = 1 To j
vdata = MyRange.Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub


jason wrote:

Thanks for the reply Tim,

Once the data has been written to the text file I want to be able to
use an excel pivot table that runs a query on the data in the text
file.(I've been told this will be good as the text file will be able
to hold hundreds of thousands of lines of data, rather than the 65
thousand in excel)
Rather than using a reference to the Microsoft
Scripting Runtime I was going to try to adapt some code like the
below:

Option Explicit
Dim i, j, k, l, r, c, vdata
Sub exportRange()

Dim MyRange As Range
Set MyRange = ActiveCell.CurrentRegion

i = MyRange.Columns(1).Column
j = i + MyRange.Columns.Count - 1
k = MyRange.Rows(1).Row
l = k + MyRange.Rows.Count - 1

Open "C:\My Documents\TextFile.txt" For Append As #1
For r = i To j
For c = k To l
vdata = MyRange.Cells(r, c).Value
'If IsNumeric(vdata) Then vdata = Val(vdata)
If c < j Then
Write #1, vdata;
Else
Write #1, vdata
End If
Next
Next
Close #1
End Sub

I've tried running this on a simple table of two columns and 5
rows(with a couple of headers) and when I look in the text file NOT
all the original data is present - any ideas why this isn't working?

Thanks in advance

Jason.

"Tim" wrote in message ...
Jason,

You should be able to make a reference to the Microsoft
Scripting Runtime. Then use the FileSystemObject to
manipulate files (for instance appending a file or reading
from it). I've included a small example of using the FSO
below.

-Tim

Sub test()
Dim oFSO As FileSystemObject
Dim oTxt As TextStream

Set oFSO = New FileSystemObject
Set oTxt = oFSO.CreateTextFile("c:\test.txt", False)
oTxt.WriteLine "my text1"
oTxt.WriteLine "my text2"
oTxt.Close
Set oTxt = Nothing

If oFSO.FileExists("c:\test.txt") Then
Set oTxt = oFSO.OpenTextFile("c:\test.txt",
ForReading, False)
Do While Not oTxt.AtEndOfStream
MsgBox oTxt.ReadLine
DoEvents
Loop
End If
End Sub


-----Original Message-----
Every day I have to add a couple of hundred lines of data
to the
bottom of a list.I then need to bring the complete list
back to excel
to analyse it.
Keeping the data and manipulating it purely in excel is
no use,
because of severe date formatting problems.

It has been suggested(in order to overcome date
formatting
problems) to put the data each day into a TXT file.How do
I move a
list of data in excel to the bottom of a list that exists
in a text
file? The subject data may be a couple of hundred rows
long and upto
16 columns wide.

Any useful code would be greatly appreciated.

Jason
.


--

Dave Peterson