importing a text delimited file in excel

[borsker]

I am trying to automate an excel program. the problem is that the customer has switched to text delimited rather than plain excel. I run excel through vfp 6. But i do not know how to find the code to get excel to import a text delimited file and tell it that it is delimited with ",". Here is what I usually use.

store getfile('TXT') to cFilename
store justpath(cFilename) to cFilelocation
store len(cFilelocation) to nPositions
store len(cFilename)-nPositions-5 to cJustnamecount
store subs(alltrim(cFilename),subster+2,cJustnamecount) to cJustname
store subs(alltrim(cFilename),subster+2,cJustnamecount+4) to cJustnameext
store 'mcInFile = "'+alltrim(cFilelocation)+'\'+alltrim(cJustname)+'.TXT"' to cExcelfile



#DEFINE xlDiagonalDown 5
#DEFINE xlDiagonalUp 6
#DEFINE xlEdgeBottom 9
#DEFINE xlEdgeLeft 7
#DEFINE xlEdgeRight 10
#DEFINE xlEdgeTop 8
#DEFINE xlInsideHorizontal 12
#DEFINE xlInsideVertical 11
#DEFINE xlAutomatic -4105
#DEFINE xlLeft -4131
#DEFINE xlBottom -4107
#DEFINE xlPasteValues -4163
#Define xlNone    -4142
loExcel = Createobject([Excel.application])
With loExcel


    &cExcelfile
    loExcel.workbooks.Open(mcInFile)
    loSheet = .activesheet
    With loSheet
    	oCopyRange = .Range([A:ZZ])
    	oCopyRange.Select
        oCopyRange.Copy
        oCopyRange.Select 
        oCopyRange.PasteSpecial(xlPasteValues,xlNone,.F.,.F.)
        oCopyRange.Select
        oCopyRange.NumberFormat = "General"
        oCopyRange.HorizontalAlignment = xlLeft
        oCopyRange.VerticalAlignment = xlBottom
        oCopyRange.WrapText = .F.
        oCopyRange.Orientation = 0
        oCopyRange.AddIndent = .F.
        oCopyRange.IndentLevel = 0
        oCopyRange.ShrinkToFit = .F.
        oCopyRange.MergeCells = .F.
		oCopyRange.Font.Name = "Arial"
        oCopyRange.Font.FontStyle = "Regular"
        oCopyRange.Font.Size = 10
        oCopyRange.Font.Strikethrough = .F.
        oCopyRange.Font.Superscript = .F.
        oCopyRange.Font.Subscript = .F.
        oCopyRange.Font.OutlineFont = .F.
        oCopyRange.Font.Shadow = .F.
        oCopyRange.Font.Underline = .F.
        oCopyRange.Font.ColorIndex = xlAutomatic
    	oCopyRange.Borders(xlDiagonalDown).LineStyle = xlNone
    	oCopyRange.Borders(xlDiagonalUp).LineStyle = xlNone
    	oCopyRange.Borders(xlEdgeLeft).LineStyle = xlNone
    	oCopyRange.Borders(xlEdgeTop).LineStyle = xlNone
    	oCopyRange.Borders(xlEdgeBottom).LineStyle = xlNone
    	oCopyRange.Borders(xlEdgeRight).LineStyle = xlNone
    	oCopyRange.Borders(xlInsideVertical).LineStyle = xlNone
    	oCopyRange.Borders(xlInsideHorizontal).LineStyle = xlNone
    	oCopyRange.Interior.ColorIndex = xlNone
	    oCopyRange.EntireColumn.Hidden = .F.
    	oCopyRange.EntireColumn.AutoFit
    	oCopyRange.EntireColumn.AutoFit
        oCopyRange.EntireRow.AutoFit
	    .Range([A1]).Select
    Endwith
Endwith


store 'mcOutDBF = "'+alltrim(cFilelocation)+'\'+CHRTRAN(alltrim(cJustname),CHR(32),"")+'_NEW.xls"' to cExcelfile

&cExcelfile
loSheet.SAVEas (mcOutDBF)
loExcel.workbooks.CLOSE

loExcel.Quit
Release loExcel
Close All

 

[JRB-Bldr]

When you say - how to find the code to get Excel to import a text delimited file and tell it that it is delimited with ',', do you mean a Comma Separated Variable file (regardless of its file extension)?

To test this you might want to make a copy of the TXT file and give this copy the file extension of CSV.

Then double click on the filename and see if it will open correctly into Excel "as is".

If that works, then all you have to do is to change the Source filename's extension to CSV and have Excel Open it using:

* --- Create new copy of Original TXT File ---
mcTempDir = "C:\Temp"
mcTXTInFile = ADDBS(mcTempDir) + "ThisFile.txt"
mcCSVInFile = ADDBS(mcTempDir) + JUSTSTEM(mcInFile) + ".CSV"
COPY FILE (mcTXTInFile) TO (mcCSVInFile)

loExcel = Createobject([Excel.application])
loExcel.workbooks.Open(mcCSVInFile)
   <do whatever>

Good Luck,
JRB-Bldr