Adding Data from EXCEL to a TEXT file

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

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.
 
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
 
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,
 
Back
Top