Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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 "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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" wrote: 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 "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automation autosave .xls as .txt
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/de...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. "jim sturtz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert .txt files into .xls in order to be able to sum. | New Users to Excel | |||
how do i password protect an .xls file? how do i unlock it for automation. e.g. want to unlock and access a .xls from another .xls macro. | Excel Worksheet Functions | |||
Importing .txt data files increases .xls file size | Excel Discussion (Misc queries) | |||
converting .PDF file to .xls or .txt file | Excel Discussion (Misc queries) | |||
i want to open a .PRN file as .txt or .xls | Excel Discussion (Misc queries) |