Save Excel as a Text Tab Delimited

[borsker]

I am looking to save an excel sheet to a tab delimited file with very little success. In fact the only thing I can do is save it as another excel file. any help would be appreciated.

 

[Mike Lewis]

Do you want to do this in Excel or in VFP? If the latter, are you using Automation? If not, the easiest way might be to use APPEND FROM ... TYPE XL5 to get the file into a table, and then COPY TO ... TYPE DELIM to get into a tab-separated text file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[MarciaAkins]

Hi Borsker.

I am looking to save an excel sheet to a tab delimited file

This code should give you some ideas about how to start:

oXl = CREATEOBJECT( [Excel.Application] )
lcFile = GETFILE()
owb = oXl.Workbooks.Open( lcFile )
oXl.Visible = .T.
oSheet = oWb.ActiveSheet
oRange = oSheet.Range( [B10], [E15] )
oRange.Copy()
STRTOFILE( _CLIPTEXT, [test.txt] )
MODIFY FILE test.txt

Marcia G. Akins

http://www.TightlineComputers.com

 

[borsker]

The problem actually is that the file originally starts out as an XML file. I am opening the file in excel and modifying some of the columns, then I was trying to save as a text tab delimited file and then append to a dbf structure. I am not sure of any other way to bring the xml file into a dbf format in the way I get it when I open it into excel 2003 first then manually manipulate the cells and then save it as a text tab delimited file.

Sorry if I am going on, but now I am having difficulties with that because excel is autoformating a column with a number that is 324233016893 but is showing up like this 3.24233E+11. Do you know how to fix this also?

Marcia, thank you for the help, But I guess since I am starting with an xml file that save is making the data look really strange. Any thoughts or should I just start from scratch.

 

[alvechurchdata]

I am not sure of any other way to bring the xml file into a dbf format

Have you looked at XMLToCursor()?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[borsker]

I tried that however, I have not gotten it done successfully

 

[MarciaAkins]

Hi Borsker.

The problem actually is that the file originally starts out as an XML file. I am opening the file in excel and modifying some of the columns, then I was trying to save as a text tab delimited file and then append to a dbf structure. I am not sure of any other way to bring the xml file into a dbf format in the way I get it when I open it into excel 2003 first then manually manipulate the cells and then save it as a text tab delimited file.

You did not say which version of VFP you are using. In version 7 and higher, you can use XMLTOCURSOR() to directly import an XML file into VFP provided that the XLM file is in the correct format.


Marcia G. Akins

http://www.TightlineComputers.com

 

[borsker]

then that is my problem i am using vpf6, i know i am ancient

 

[Mike Lewis]

Borsker,

As far as I can see, Marcia's code should work just the same, regardless of the fact that the file was originally XML. After all, once Excel has opened it, it is just another worksheet.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Dan Freeman]

For VFP6, you can download classes from

http://www.west-wind.com

that provide XMLtoCursor(). (Where do you think MS got the idea? <g>)