automation autosave .xls as .txt

[jim sturtz]

i have the code below for foxpro that will load an .xls and show it on the
desktop.

i would like it to merely load the .xls and do an autosave as a .txt file
type, as the file i am starting with has lots of dross in it that can easily
be removed by saving as a .txt

i think if i change the loExcel.Visible=.t. to .f. then i wouldnt see the
file, so what commands would do the autosave and then autoexit?

thanks.

jim

*****
Local loExcel As Excel.Application
Local loWorkBook As Excel.Workbook
Local loActiveSheet As Excel.Sheets
Local loRange As Excel.Range
Local lError, lcFileName
lError = .F.

Try

loExcel = Createobject("Excel.Application")

F2L=Fullpath(F2L)
lcFileName=F2L && this is the file opened


loWorkBook = loExcel.Workbooks.Open(lcFileName)
loActiveSheet = loExcel.ActiveSheet
loExcel.Visible=.T.
*****


microsoft.public.excel.worksheet.functions

 

[Guest]

I'll take a stab at this. First how it would be done in Excel in a way so as
to not nag the user with messages about overwriting existin file of same name

Application.DisplayAlerts=False
ActiveWorkbook.SaveAs Filename:="excelAsText.txt", Filetype:=xlText
ActiveWorkbook.Close
Application.Quit ' shut down excel

I'm thinking you should be able to substitute your IoExcel application
object and the IoWorkbook objects where needed, although I'm not sure how
doing it from FoxPro is going to deal with the named parameters in the SaveAs
action.

Also, be advised that when saved as text, only the currently active sheet is
saved - not all sheets in the workbook.

 

[jim sturtz]

thanks for the quick reply.

you were pretty much right on.

i puzzled this out after doing a little more searching on my own. couldnt
find a way to suppress the message about the test.txt being there so just
erased it first. the -4158 i found in another post, apparently it is the
equivalent of the xlText statement.

****
lstrfilepath='E:\LEGACY\test.txt'
ERASE TEST.TXT
loWorkBook.SaveAs(LSTRFILEPATH, -4158 )
LOWorkbook.Close(.F.)
loexcel.quit
loexcel=''
****
jim

 

[Guest]

Glad to have been at least a little help. Yes, -4158 is the value returned
for xlText in Excel 2003. I just verified that since I was already working
in Excel VB Editor and had easy access to the info. Just keep in mind that
one of the reasons that xlText exists is just so that MSFT can change that
value later without affecting code using the name vs the value. So future
releases of Excel might not honor the -4158. Just something to keep in mind
or comment on in the code?

 

[jim sturtz]

good point.

i tried looking around for a source on the -4158 (or even a list of all the
properties/events/methods) of the excel object and didnt find a concise
source.

could you point me to some references you are using.

thanks.

jim

 

[Guest]

The reference I used was Excel's Help from within the Visual Basic Editor.
There is a topic "Microsoft Excel Constants" it lists groups, or families of
constants that can be expanded to see all the members. Turns out it looks
like what we used is technically "xlCurrentPlatformText" (value -4158) and
part of the xlFileFormat family. I was actually unable to find the specific
"xlText" in any of the lists I looked through. But what I did was use the
Immediate window in the VB Editor to simply tell it to
Print xlText
and it returned -4158
Or bookmark this page:
http://msdn.microsoft.com/library/d...us/vbaxl11/html/xlhowConstants_HV01049962.asp

If you don't have Excel available to use as a reference source like that,
you can always ask here in the forums, just tell people what you're trying to
do and that you need the numeric value for a parameter rather than just the
parameter name and I'm sure someone will help you out.