Category: Visual Foxpro

Needing LDAP access to be able to create some sort of Single Sign-On (SSO), I googled and created this testcode that is able to query the LDAP and place the results in a cursor. Continue reading “LDAP Query from Foxpro”

It is very possible to automate spreadsheets from Visual Foxpro using VBA/ COM automation. Here I wil document the parts I need.

Connecting to Excel

oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
  * could not instantiate Excel object
  * show an error message here
  return .F.
endif

I use my own library (JLIB) containing a ReturnObject function that fetches errors. Using that function my code looks like this:

PUBLIC goX && global Excel object

IF InitiateExcel()
 * Code here
ELSE
 ? [Error initiating Excel automation]
ENDIF 


PROCEDURE InitiateExcel
 goX = ReturnObject([excel.application])
 RETURN (VARTYPE(goX)=[O])
ENDPROC 

Open an existing spreadsheet

oWorkbook = oExcel.Application.Workbooks.Open("C:\temp\test.xls")

Create a new blank spreadsheet

oWorkbook = oExcel.Application.Workbooks.Add()

Create a new spreadsheet from a template

oWorkbook = oExcel.Application.Workbooks.Add("C:\temp\template.xlt")

Sheets collection/ objects

oWorkbook.sheets.count && returns the number of tabs/ sheets 
oSheet = oWorkbook.sheets(1) && get an object to control the sheet
? oSheet.name && show the sheet name

Tables

A table is called a ListObject in Excel/ Microsoft terminology

oSheet.ListObjects.count && gives the number of tables on the sheet
oTable = oSheet.ListObjects(1) && get an object to control the table
? oTable.ListRows.count && displays the number of data rows in the table
oTable.DataBodyRange.delete && deletes al rows. Error (crash) if there are no rows!

Working with table rows

Adding a table row needs several steps: 1. Add the row and keep the reference to it. 2. Add the column data 1 by 1:

oRow = oTable.ListRows.Add && you have a new row and a reference to it
oRow.Range(1) = Date(2020,7,6) && Add a date in the first column

Using named cells

? oSheet.Range("ProductionDate").value && show value of cell named ProductionDate
oSheet.Range("TotalPages").value = 13

Saving and closing

The save method is in the workbook object. And you can quit from the Excel application object:

oSheet.SaveAs([x:\reports\report.xlsx]) && Save
oExcel.Quit && quit Excel

The Quit only works on this Excel application you’ve initiated. So existing opened Excel sheets are untouched (remain opened).

Sources used

• http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation
• https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables#insert
• https://www.bluepecantraining.com/portfolio/excel-vba-how-to-add-rows-and-columns-to-excel-table-with-vba-macro/

Header files (.H) contain declarations and sometimes code. Software uses codes for specific actions or settings, but these codes are illegible for most humans. The header file for Microsoft Word 2016 contains more than 4.500 lines. It starts like this:

* WdMailSystem
#DEFINE wdNoMailSystem 0
#DEFINE wdMAPI 1
#DEFINE wdPowerTalk 2
#DEFINE wdMAPIandPowerTalk 3

* WdTemplateType
#DEFINE wdNormalTemplate 0
#DEFINE wdGlobalTemplate 1
#DEFINE wdAttachedTemplate 2

The header files are not included in the distributions, so you have to search for them yourself or create them if possible.

This program originates from an old Microsoft article. It creates a dialog where you can locate an .OLB file (Object Library). It then extracts all constants and gives you the ability to save them to your header file.

* Bron: https://web.archive.org/web/20110808014156/http://support.microsoft.com/kb/285396

****************START CODE****************
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.SHOW
RETURN


****************FORM CODE****************
DEFINE CLASS form1 AS FORM

	HEIGHT = 445
	WIDTH = 567
	DOCREATE = .T.
	AUTOCENTER = .T.
	BORDERSTYLE = 1
	CAPTION = ".OLB Constants Extractor"
	MAXBUTTON = .F.
	MINBUTTON = .F.
	NAME = "Form1"

	ADD OBJECT txtolbfile AS TEXTBOX WITH ;
		HEIGHT = 27, ;
		LEFT = 65, ;
		READONLY = .T., ;
		TABINDEX = 2, ;
		TOP = 6, ;
		WIDTH = 458, ;
		NAME = "txtOLBFILE"

	ADD OBJECT label1 AS LABEL WITH ;
		AUTOSIZE = .T., ;
		CAPTION = ".\<OLB File:", ;
		HEIGHT = 17, ;
		LEFT = 4, ;
		TOP = 11, ;
		WIDTH = 55, ;
		TABINDEX = 1, ;
		NAME = "Label1"

	ADD OBJECT cmdsave AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 394, ;
		HEIGHT = 27, ;
		WIDTH = 84, ;
		CAPTION = "\<Save to .h", ;
		ENABLED = .F., ;
		TABINDEX = 6, ;
		NAME = "cmdSAVE"

	ADD OBJECT cmdquit AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 480, ;
		HEIGHT = 27, ;
		WIDTH = 84, ;
		CAPTION = "\<Quit", ;
		TABINDEX = 7, ;
		NAME = "cmdQUIT"

	ADD OBJECT edtconstants AS EDITBOX WITH ;
		HEIGHT = 347, ;
		LEFT = 6, ;
		READONLY = .T., ;
		TABINDEX = 4, ;
		TOP = 52, ;
		WIDTH = 558, ;
		NAME = "edtConstants"

	ADD OBJECT cmdgetfile AS COMMANDBUTTON WITH ;
		TOP = 6, ;
		LEFT = 533, ;
		HEIGHT = 27, ;
		WIDTH = 26, ;
		CAPTION = "...", ;
		TABINDEX = 3, ;
		NAME = "cmdGETFILE"

	ADD OBJECT cmdextract AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 280, ;
		HEIGHT = 27, ;
		WIDTH = 110, ;
		CAPTION = "\<Extract Constants", ;
		ENABLED = .F., ;
		TABINDEX = 5, ;
		NAME = "cmdEXTRACT"


	PROCEDURE cmdsave.CLICK
		STRTOFILE(THISFORM.edtconstants.VALUE,PUTFILE([Header File], ;
			JUSTSTEM(THISFORM.txtolbfile.VALUE) + [.h],[.h]))
	ENDPROC


	PROCEDURE cmdquit.CLICK
		THISFORM.RELEASE
	ENDPROC


	PROCEDURE cmdgetfile.CLICK
		LOCAL lcOLBFile

		lcOLBFile = GETFILE([OLB],[OLB File],[Open])
		IF EMPTY(lcOLBFile)
			RETURN .F.
		ENDIF
		
		IF UPPER(RIGHT(lcOLBFile,3)) # [OLB]
			MESSAGEBOX([Invalid File],0,[])
			RETURN .F.
		ENDIF

		THISFORM.txtolbfile.VALUE = lcOLBFile
		THISFORM.cmdextract.ENABLED= .T.
	ENDPROC


	PROCEDURE cmdextract.CLICK
		WAIT WINDOW [Processing...] NOCLEAR NOWAIT
		LOCAL oTLB_INFO, oConstants, lcConstantsStr, Obj, member
		#DEFINE CRLF CHR(13) + CHR(10)

		oTLB_INFO = CREATEOBJECT([tli.typelibinfo])
		oTLB_INFO.ContainingFile = (THISFORM.txtolbfile.VALUE)

		oConstants = oTLB_INFO.Constants

		lcConstantsStr = []
		FOR EACH Obj IN oTLB_INFO.Constants
                        lcConstantsStr = lcConstantsStr + CRLF + "* " + Obj.Name + CRLF   
                        FOR EACH member IN Obj.Members
                            lcConstantsStr = lcConstantsStr + [#DEFINE ] + ;
                            member.NAME + [ ] + ;
                            TRANSFORM(member.VALUE) + CRLF
                        NEXT member
		NEXT Obj

		THISFORM.edtconstants.VALUE=lcConstantsStr
		THISFORM.cmdsave.ENABLED= .T.
		WAIT CLEAR
		WAIT WINDOW [Complete!] TIMEOUT 2
	ENDPROC

ENDDEFINE
****************END CODE****************

Draait een programma al?

Door jasperdg op 16 Jul 2007

Het is soms niet handig als je een programma start terwijl dit al draait. Vooral bij programma’s die bewerkingen uitvoeren op databases is dit zeer ongewenst (indexeer programma’s etc). Hier een geschikte module die met Win32Api functies de actieve vensters uitleest en kijkt of een specifiek programma in de actieve titels voorkomt…

Met dank aan FoxStuff.

Programma IsRunning.prg :

 (0 als niet gevonden, anders het handle nummer)

* Generic routine to check if a given 
* application is running on the user's system.
* Parameter is all or part of the window's title.
LPARAMETERS tcTitle

DECLARE INTEGER GetActiveWindow IN Win32API
DECLARE INTEGER GetWindow IN Win32API ;
  INTEGER hWnd, INTEGER nType
DECLARE INTEGER GetWindowText IN Win32API ;
  INTEGER hWnd, STRING @cText, INTEGER nType

lhNext = GetActiveWindow() && current app's window
* iterate through the open windows
DO WHILE lhNext#0
 lcText = REPLICATE(CHR(0),80)
 GetWindowText(lhNext,@lcText,80) && get window title
 IF UPPER(ALLTRIM(tcTitle)) $ UPPER(lcText)
  * parameter text is present in window title
  RETURN lhNext
 ENDIF
 lhNext = GetWindow(lhNext,2) && next window
ENDDO
* required window not found
RETURN 0