We can create an Excel object in VFP and use all the methods and properties of Excel class to manipulate Excel file.
In the main program, create an ExcelPlus object:
SET PROCEDURE TO ExcelPlus
oExcel = CREATEOBJECT("ExcelPlus")
oExcel.StartExcel
Then, include file ExcelPlus.prg in the project. ExcelPlus.prg defines a class, ExcelPlus, which is an inheritance of class Excel.Application in VBA, so class ExcelPlus can access all the methods and properties of class Excel.Application in VBA. New customized methods can also be easily created in ExcelPlus.prg.
This is part of ExcelPlus.prg:
DEFINE CLASS ExcelPlus AS SESSION
#DEFINE OKAY 0
#DEFINE EXCELSTART 1
#DEFINE EXCELSTOP 2
#DEFINE NULLDATA 3
#DEFINE UNKNOWNALIGNMENT 4
#DEFINE UNKNOWNORIENTATION 5
#DEFINE BADPAGEMARGIN 6
#DEFINE ITEMNOTFOUND 7
#DEFINE COLORNOTDEFINED 8
#DEFINE TOOFARRIGHT 1001
#DEFINE TOOFARLEFT 1002
#DEFINE TOOFARUP 1003
#DEFINE TOOFARDOWN 1004
#DEFINE COLOR_BLACK 1
#DEFINE COLOR_DARKRED 9
PROTECTED AddressLo, AddressHi, MaxCol, MaxRow, oExcel, oWorkbook, DATASESSION
HIDDEN CurrentColLo, CurrentColHi
* exposed properties
CurrentCell = [A1]
CurrentCol = [A]
CurrentRow = 1
CurrentSheet = [Sheet1]
ExcelVisible = .F.
NewSheets = 1
ErrorCode = 0
ErrorMsg = [Okay]
* protected properties
AddressLo = [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
AddressHi = [ ABCDEFGHI]
MaxCol = [IV]
MaxRow = 65536
oExcel = .NULL.
oWorkbook = .NULL.
DATASESSION = 1
* hidden properties
CurrentColHi = 1
CurrentColLo = 1
**********************************************
PROCEDURE ErrorStatus(tnErrornumber)
DO CASE
* informational messages
CASE tnErrornumber = OKAY
THIS.ErrorMsg = [Okay]
CASE tnErrornumber = EXCELSTART
THIS.ErrorMsg = [Excel object instanciated.]
CASE tnErrornumber = EXCELSTOP
THIS.ErrorMsg = [Excel object destroyed.]
CASE tnErrornumber = NULLDATA
THIS.ErrorMsg = [Excel returned a NULL. Converted to space.]
CASE tnErrornumber = UNKNOWNALIGNMENT
THIS.ErrorMsg = [Unknown cell alignment. No cell alignment set.]
CASE tnErrornumber = UNKNOWNORIENTATION
THIS.ErrorMsg = [Unknown page orientation. No orientation set.]
CASE tnErrornumber = BADPAGEMARGIN
THIS.ErrorMsg = [Page margin less than 0. No margins set.]
CASE tnErrornumber = ITEMNOTFOUND
THIS.ErrorMsg = [Item not found prior to set limit.]
CASE tnErrornumber = COLORNOTDEFINED
THIS.ErrorMsg = [Color not defined in object.]
* error messages
CASE tnErrornumber = TOOFARRIGHT
THIS.ErrorMsg = [Attempt to go right of column ] + THIS.MaxCol + [.]
CASE tnErrornumber = TOOFARLEFT
THIS.ErrorMsg = [Attempt to go left of column A.]
CASE tnErrornumber = TOOFARUP
THIS.ErrorMsg = [Attempt to go above row 1.]
CASE tnErrornumber = TOOFARDOWN
THIS.ErrorMsg = [Attempt to go below row ] + ALLTRIM(STR(THIS.MaxRow)) + [.]
OTHERWISE
THIS.ErrorMsg = [Unknown]
ENDCASE
THIS.ErrorCode = tnErrornumber
* give the programmer a heads-up.
IF tnErrornumber > 1000
WAIT WINDOW THIS.ErrorMsg
ENDIF
ENDPROC
**********************************************
PROCEDURE StartExcel
THIS.oExcel = CREATEOBJECT("Excel.Application")
THIS.ErrorStatus(EXCELSTART)
ENDPROC
**********************************************
PROCEDURE StopExcel
THIS.oExcel.QUIT()
THIS.oExcel = .NULL.
THIS.ErrorStatus(EXCELSTOP)
ENDPROC
**********************************************
PROCEDURE OpenSpreadSheet(tcPathName)
THIS.oExcel.Workbooks.OPEN(tcPathName,.F.)
THIS.oWorkbook = THIS.oExcel.ActiveWorkbook
ENDPROC
**********************************************
PROCEDURE SaveSpreadSheet(tcFileName,tcPassWord)
IF PARAMETERS() = 1
tcPassWord = []
ENDIF
This.oExcel.DisplayAlerts = .F.
DO CASE
CASE VAL(This.oExcel.Version) > 11 AND EMPTY(tcPassWord)
THIS.oWorkbook.SaveAs(tcFileName+[.xls], 56)
CASE VAL(This.oExcel.Version) > 11
THIS.oWorkbook.SaveAs(tcFileName+[.xls], 56, tcPassWord)
CASE EMPTY(tcPassWord)
THIS.oWorkbook.SaveAs(tcFileName+[.xls])
OTHERWISE
THIS.oWorkbook.SaveAs(tcFileName+[.xls], tcPassWord)
ENDCASE
This.oExcel.DisplayAlerts = .T.
RETURN
ENDPROC
**********************************************
PROCEDURE CloseSpreadSheet
This.oExcel.DisplayAlerts = .F.
This.oWorkbook.Close() && Unsaved changes will be discarded
This.oExcel.DisplayAlerts = .T.
*!* This.oWorkbook.Close
RETURN
ENDPROC
**********************************************
PROCEDURE NewWorkBook
WITH THIS.oExcel
.SheetsInNewWorkbook = THIS.NewSheets
THIS.oWorkbook = .Workbooks.ADD()
ENDWITH
ENDPROC
**********************************************
PROTECTED PROCEDURE ExcelVisible_assign
* automatically sets the visible
* property of excel
LPARAMETERS tlView
THIS.ExcelVisible = tlView
THIS.oExcel.VISIBLE = THIS.ExcelVisible
ENDPROC
**********************************************
FUNCTION GoToCell(tcCell)
LOCAL lcCell
lcCell = UPPER(tcCell)
* check to see if we passed the limits
* these limits are for hand entered
* addresses programmatic addresses
* are checked with the move methods
IF THIS.Limits(lcCell)
* navigate to a particular cell,
* but if already there do nothing
THIS.oExcel.RANGE(lcCell).SELECT
IF THIS.CurrentCell # lcCell
THIS.CurrentCell = lcCell
ENDIF
ENDIF
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
**********************************************
FUNCTION GoToCol(tcCol)
THIS.GoToCell(tcCol + ALLTRIM(STR(THIS.CurrentRow)))
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
**********************************************
FUNCTION GoToRow(tnRow)
THIS.GoToCell(THIS.CurrentCol + ALLTRIM(STR(tnRow)))
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
Reference:
http://www.tomorrowssolutionsllc.com/Conference%20Sessions/Driving%20Word%20and%20Excel%20from%20Visual%20FoxPro.pdf
Tuesday, September 1, 2015
Thursday, July 23, 2015
VFP small tricks
1. Control text format in Textbox and other input controls
InputMask Property
Specifies how users enter data and how to display data in a control. Available at design time and run time.
Format Property
Specifies the input and output formatting of a control's Value property. Available at design time and run time.
2. Move cursor to the right in a textbox
This.SelStart = LEN(ALLTRIM(This.Value))
This.SelLength = 0
3. Multiple forms - Modal vs Modeless
Modal forms require user input. A modal form has exclusive focus until it is dismissed. When showing a modal form, the controls outside the modal form will not react until the modal form is closed.
http://fox.wikis.com/wc.dll?Wiki~ModalvsModeless
The property "Desktop" of child form specifies whether a form can appear anywhere on the Windows desktop (if the value is .T.) or is contained in the parent form ( if the value is .F.). The default value is .F.
4. Prevents Visual FoxPro from inserting a key press into the keyboard buffer
Including NODEFAULT in the KeyPress event procedure or function prevents Visual FoxPro from inserting the key press into the Visual FoxPro keyboard buffer. Therefore, you can create a KeyPress procedure so that you can test which key is pressed before the key is sent to the keyboard buffer.
https://msdn.microsoft.com/en-US/library/e525z4k3(v=vs.80).aspx
5. Print immediately
Put these two lines at the end of program:
SET PRINTER TO
SET DEVICE TO SCREEN
InputMask Property
Specifies how users enter data and how to display data in a control. Available at design time and run time.
Format Property
Specifies the input and output formatting of a control's Value property. Available at design time and run time.
2. Move cursor to the right in a textbox
This.SelStart = LEN(ALLTRIM(This.Value))
This.SelLength = 0
3. Multiple forms - Modal vs Modeless
Modal forms require user input. A modal form has exclusive focus until it is dismissed. When showing a modal form, the controls outside the modal form will not react until the modal form is closed.
http://fox.wikis.com/wc.dll?Wiki~ModalvsModeless
The property "Desktop" of child form specifies whether a form can appear anywhere on the Windows desktop (if the value is .T.) or is contained in the parent form ( if the value is .F.). The default value is .F.
4. Prevents Visual FoxPro from inserting a key press into the keyboard buffer
Including NODEFAULT in the KeyPress event procedure or function prevents Visual FoxPro from inserting the key press into the Visual FoxPro keyboard buffer. Therefore, you can create a KeyPress procedure so that you can test which key is pressed before the key is sent to the keyboard buffer.
https://msdn.microsoft.com/en-US/library/e525z4k3(v=vs.80).aspx
5. Print immediately
Put these two lines at the end of program:
SET PRINTER TO
SET DEVICE TO SCREEN
Monday, July 13, 2015
VFP: Cancel validation on exit button click
Question:
In a Form, I have several textboxes that are validated against a table. The only logical way to set up the screen is for one of them to have the focus when you enter the form or after a save and ready for the next input (this textbox determines what all the rest have in them [e.g., the order number]). I also have a standard EXIT button which has Thisform.Release().
When I click the EXIT, the Valid() on the order field fires first and I must validate it before I can exit. My current work-around is to set the form's Key Preview on and check for ESCAPE and release.
How can I know in the Valid for order what event caused the valid to fire (i.e., the exit click) so I can conditionally skip the valid and let the form release?
I know I have seen this somewhere but can't remember.
Thanks,
==========================================
Answer:
There are a number of advantages of using an Exit button with the Cancel property set to .T. and checking for LastKey() = 27 in the Valid events of all of your control classes. One is that with Cancel set to .T. clicking the button is the same as pressing Escape so checking for 27 (Escape) as the LastKey() will tell you if the Exit button has been clicked. Secondly with Cancel set to .T. pressing Escape will click the button and fire its Click event thusly allowing you to give the user an expected functionality for the escape key.
In my control classes the beginning of the Valid event is;
IF LastKey() = 27
Keyboard "{CTRL+A}"
RETURN .T.
ENDIF
The keyboard of CTRL+A is to change the value of LastKey() from 27. This is because if the user clicks the exit button and then only uses the mouse, lastkey() will remain 27 until some other key is pressed. Doing the keyboard changes lastkey from 27 so the only way it can be 27 again is by pressing escape or clicking a button that has Cancel set to .T.
http://fox.wikis.com/wc.dll?Wiki~SkipValidOnExit
Wednesday, July 8, 2015
VFP Form Event Sequence
-When A Form Getting Loaded:
1. DataEnvironmet.openTables()
2. DataEnvironmet.beforeOpenTables()
3. Form.load()
4. [cursurs].Init() - for each cursor in DataEnvironment
5. DataEnvironment.init()
6. [controls].init() - for all controls in a form
7. Form.init()
8. Form.show()
9. Form.activate()
10. Form.refresh()
11. [object1].when() – for the first object in tab order
12. [object1].gotFocus() – for the first object in tab order
But In MSDN There Is A Slight Difference:
1. Form.init()
2. Form.activate()
3. [object1].when() – for the first object in tab order
4. Form.gotFocus()
5. [object1].gotFocus()
6. [object1].message()
-If We Leave An Object And The Next Object Gets The Focus:
Whenever An Object Gets The Focus The Sequence Of The Events Is As Below:
1. [object(i)].when()
2. [object(i)].gotFocus()
3. [object(i)].message()
And Whenever An Object Loses The Focus The Sequence Would Be:
1. [object(j)].valid()
2. [object(j)].lostFocus()
-If We Move To The Next Text Box From Currently Focused Text Box And Type Something In The Next Text Box:
1. Text(i).keyPress()
2. Text(i).valid
3. Text(i).lostFocus
4. Text(i+1).when()
5. Text(i+1).gotFocus()
-When We Type In A Text Box:
1. Text(i).keyPress()
2. Text(i).interactiveChange()
-When We Leave A Form By Calling Realease() Method (Closing The Form):
1. Form.queryUnload()
2. Form.destroy()
3. Form.[command buttons].destroy()
4. Form.[objects].destroy()
5. Form.unload()
6. DataEnvironment.afterCloseTables()
7. DataEnvironment.destroy()
-When We A Form Loses Focus (Like When Another Form Get The Focused):
1. Form.lostFocus()
2. Form.deactivate()
Che - 10/APR/2007
http://fox.wikis.com/wc.dll?Wiki~FormEventSequence
1. DataEnvironmet.openTables()
2. DataEnvironmet.beforeOpenTables()
3. Form.load()
4. [cursurs].Init() - for each cursor in DataEnvironment
5. DataEnvironment.init()
6. [controls].init() - for all controls in a form
7. Form.init()
8. Form.show()
9. Form.activate()
10. Form.refresh()
11. [object1].when() – for the first object in tab order
12. [object1].gotFocus() – for the first object in tab order
But In MSDN There Is A Slight Difference:
1. Form.init()
2. Form.activate()
3. [object1].when() – for the first object in tab order
4. Form.gotFocus()
5. [object1].gotFocus()
6. [object1].message()
-If We Leave An Object And The Next Object Gets The Focus:
Whenever An Object Gets The Focus The Sequence Of The Events Is As Below:
1. [object(i)].when()
2. [object(i)].gotFocus()
3. [object(i)].message()
And Whenever An Object Loses The Focus The Sequence Would Be:
1. [object(j)].valid()
2. [object(j)].lostFocus()
-If We Move To The Next Text Box From Currently Focused Text Box And Type Something In The Next Text Box:
1. Text(i).keyPress()
2. Text(i).valid
3. Text(i).lostFocus
4. Text(i+1).when()
5. Text(i+1).gotFocus()
-When We Type In A Text Box:
1. Text(i).keyPress()
2. Text(i).interactiveChange()
-When We Leave A Form By Calling Realease() Method (Closing The Form):
1. Form.queryUnload()
2. Form.destroy()
3. Form.[command buttons].destroy()
4. Form.[objects].destroy()
5. Form.unload()
6. DataEnvironment.afterCloseTables()
7. DataEnvironment.destroy()
-When We A Form Loses Focus (Like When Another Form Get The Focused):
1. Form.lostFocus()
2. Form.deactivate()
Che - 10/APR/2007
http://fox.wikis.com/wc.dll?Wiki~FormEventSequence
Thursday, June 4, 2015
How to give focus to a FoxPro top-level form at start-up
When I tried to create a form that comes up without the surrounding FoxPro Form/workspace and menu structure, I used these code:
In the form properties:
ThisForm.ShowWindow = 2 && Set the window to As Top-Level Form
In the form Init method:
_Screen.visible = .F.
ThisForm.ShowWindow = 2 && Set the window to As Top-Level Form
In the form Init method:
_Screen.visible = .F.
The problem is the form lost focus when it runs. Users have to click the icon on the taskbar to bring the form to the top.
On MSDN LostFocus Event page, there is explanation:
A form loses the focus when the form has no controls, all its controls
have their Enabled and Visible properties set to false (.F.), or another
form gets the focus.
So the second command caused the problem. I tried to solve this issue. I did some search and finally found this article:
===================================================
How to give focus to a FoxPro top-level form at start-up
By Mike Lewis
This is a question I often see in Visual FoxPro forums. An application needs to show a top-level form (typically a log-in screen) at start-up. But when you launch the form, it
appears behind other windows on the desktop. Even if it is at the front, it's not necessarily the active form. So how do you give the form focus programmatically?
None of the obvious techniques seem to work. These include toggling the form's AlwaysOnTop property, and calling the SetFocus method for one of its controls. However, the following code will always do the trick:
DECLARE INTEGER SetForegroundWindow IN WIN32API INTEGER
SetForegroundWindow(thisform.HWnd)
CLEAR DLLS "SetForegroundWindow"
All you have to do is place that code in the form's Init method, and the
problem's solved. (The code works in VFP 7.0 and above.)
Friday, May 8, 2015
VFP function that combines individual xls files
VFP function that combines individual xls files
I need to put get data from VFP and put it into multiple sheets in an Excel file. I found this code on Internet. I have not tested it so far.
--------- Original link and post: ---------
https://social.msdn.microsoft.com/Forums/en-US/a9b81c52-2969-4403-81e2-b5ce891f1a2f/copy-to-excel-and-adding-worksheets?forum=visualfoxprogeneral
I have this function that we use to combine individual xls files:
Naomi Nosonovsky, Sr. Programmer-Analyst
I need to put get data from VFP and put it into multiple sheets in an Excel file. I found this code on Internet. I have not tested it so far.
--------- Original link and post: ---------
https://social.msdn.microsoft.com/Forums/en-US/a9b81c52-2969-4403-81e2-b5ce891f1a2f/copy-to-excel-and-adding-worksheets?forum=visualfoxprogeneral
I have this function that we use to combine individual xls files:
************************************************************
* Function CombineExcelFiles
************************************************************
* Created...........: Craig Boyd 3/6/2006 23:55:50
*) Description.......:
* Calling Samples...: DIMENSION aXLSFiles(3)
*!* aXLSFiles(1) = "C:\temp1.xls"
*!* aXLSFiles(2) = "C:\temp2.xls"
*!* aXLSFiles(3) = "C:\temp3.xls"
*!* CombineExcelFiles(@aXLSFiles, "C:\XLSCombined.xls")
* Parameter List....:
* Major change list.:
function CombineExcelFiles (taXLSFiles, tcDestination, tlDeleteOriginal)
external array taXLSFiles
local loExcel as Excel.application, ;
loWorkBook as Excel.Worksbook, ;
loWorkSheet , ;
lnCounter, lcWorkSheetCaption, lcError, ;
lcValidChars
lcError = ""
try
lcValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 "
loExcel = newobject("Excel.Application")
with loExcel
.ScreenUpdating = .f.
.DisplayAlerts = .f.
.WorkBooks.add()
lnCounter = 0
** Delete all existing worksheets except 1
for each loWorkSheet in .WorkBooks(1).WorkSheets
lnCounter = m.lnCounter + 1
if m.lnCounter > 1
loWorkSheet.delete
endif
endfor
for lnCounter = 1 to alen(taXLSFiles,1)
if file(taXLSFiles[m.lnCounter])
lcWorkSheetCaption = juststem(taXLSFiles[m.lnCounter])
loWorkBook = .WorkBooks.open(taXLSFiles[m.lnCounter])
loWorkBook.WorkSheets(1).copy(null, ;
.WorkBooks(1).WorkSheets(.WorkBooks(1).WorkSheets.count))
.WorkBooks(1).ActiveSheet.name = ;
right(alltrim(chrtran(m.lcWorkSheetCaption, ;
chrtran(m.lcWorkSheetCaption,m.lcValidChars,"")," ")), 31) &&loWorkBook.Name
loWorkBook.close(.f.) && Do not save changes
if m.tlDeleteOriginal
erase (taXLSFiles[m.lnCounter])
endif
endif
endfor
** Remove the first original sheet from (Sheet1)
.WorkBooks(1).WorkSheets(1).delete
.WorkBooks(1).saveas(m.tcDestination)
.ScreenUpdating = .t.
.DisplayAlerts = .t.
endwith
catch to loError
lcError = Log_Error(m.loError)
finally
if vartype(m.loExcel) = 'O'
with loExcel
.ScreenUpdating = .t.
.DisplayAlerts = .t.
.quit()
endwith
endif
endtry
return m.lcError
endfunc
Naomi Nosonovsky, Sr. Programmer-Analyst
Useful Excel Automation examples
|
Useful Excel Automation examples
Posted: 5 Oct 03 (Edited 8 Oct 03)
|
Here is an on-going compilation of Excel automation samples.
Mike Gagnon |
Thursday, February 26, 2015
A useful search tool, Filer, in Virtual FoxPro
This is a good search tool in VFP.
Filer
Years ago, Filer was what most FoxPro developers used to search and find files. After VFP 3.0, this disappeared and many developers screamed. Well, the Fox Team has listened and decided to bring back a little nostalgia. Enter the following in the Command window to see an example of how it runs:
DO FORM (HOME(1) + 'Tools\Filer\Filer.scx')
Search through the VFP Help file and read up on the new Filer, because this one is object-oriented and you can interact with it and implement it in your own applications. I guess you could call this FilerX!
The original article is here:
https://msdn.microsoft.com/en-us/library/ms947597.aspx
Subscribe to:
Comments (Atom)