using a text file with an excel file

[amberH]

Hi All,
I'm having a problem with using data from a text file. I currently have my program set up to open a text file, copy the lines of text, and paste it into Excel.
My problem is this:
If my lines of text looks like this:

09/08/2002 1 0000.00 7662.24 0015.99 0501.46 0000.00 0000 0008.17
INVENTORY 1057.70 0115.28 0000.00 0328.74 0000.45 0000.00 0019.89 0000.00 0000.00 0000.00 -0022.79 0000.00 0000.00 0000.00

With the 3rd set of numbers from the end (0000.00), whether or not there is a value in there, it tries to split the number (so it will be 0) and then in the next cell, it will place (0 0000.00) so it isn't a proper number. Another example is that if the 3rd number set from the end was 5.4, it will paste it in my spreadsheet as 5, with the next cell 4 0000.00)

Originally when I created this code, I just recorded a macro of me cutting and pasting the data. I've gone back in and recreated the macro, and it will work for that file, but a few days letter the numbers no longer seem to allign.

The code for this section is:

Workbooks.OpenText Filename:=txtFile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(11, 1), Array(15, 1), Array(22, 1), Array(31, 1), Array(40, 1), _
Array(49, 1), Array(58, 1), Array(69, 1), Array(77, 1), Array(87, 1), Array(95, 1), Array( _
103, 1), Array(113, 1), Array(120, 1), Array(130, 1))

I've tried to play around with the numbers a bit, but I don't really understand them

Can anyone help me out with this??
Thanks in advance.
(Sorry it was so long!)

amber

 

[ETID]

You would not need to export/import the data to excel, (too many steps)...

Create a live link to the text file from within excel,

From excel click Data>Get External Data>New Database Query>....the choose text files* from the databases tab...follow the wizard from there to select your txt file,
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. it will reflect any changes in the text file (if you set the refresh on open option)

 

[BrainB]

Where does you text file come from. I get them from Oracle databases and they are often corrupted with bad data line-up. Regards
BrianB
** Let us know if you get something that works !
================================

 

[amberH]

Hello,
I have this set up to automatically get the text file every morning (a new one is generated every day).
I've used this method in the past, and it works great. I think the problem here is that sometimes there is a negative sign before the numbers, which skews everything. I don't really understand what I'm doing enough to correct it manually, which is why I just recorded the macro, then tried to customize it to work for me.
Thanks,
Amber