Foxpro Word Automation Replace

The word Macro generates following code to do search and "REPLACE"
With Selection.Find
.Text = "findword"
.Replacement.Text = "replaceword"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

The above will not work in Foxpro, Foxpro use positional parameters
Function Execute([FindText], [MatchCase], [MatchWholeWord], [MatchWildcards], [MatchSoundsLike], [MatchAllWordForms], [Forward], [Wrap], [Format], [ReplaceWith], [Replace], [MatchKashida], [MatchDiacritics], [MatchAlefHamza], [MatchControl]) As Boolean

To use in Foxpro, we have to provide all paramters in right sequence till "ReplaceWith". We can omit rest of the parameters
Const wdFindContinue = 1

oword.Selection.Find.Execute(tcOriginal, .f., .f., .f., .f., .f., .t., wdFindContinue, .f., tcReplace)

SYS(987) Foxpro

Foxpro SYS(987) can be used to return remote Varchar data as ANSI for use with Memo fields.

Remote data with varchar fields in foxpro displays square box between each characters




By setting SYS(987, .T.) the data will be in ANSI format.

The above data now displayed as
Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.16) Gecko/20080702 Firefox/2.0.0.16

Open cash register from DOS

type "copy con: open.txt" and press ENTER
press ALT key and hold it
type "27" on your numeric keypad (Numeric Keypad not one above letters )
release ALT key
type "p07y" (small "p" zero seven and small "y"! without quotes)
type Ctrl-z and then Enter

To open the cash register
copy open.txt LPT1:

XP SP3 install - Access Denied Error

Windows Update keeps failing on SP3 update with "Access Denied" error. Searching online found this blog.

http://fastest963windows.blogspot.com/2008/05/before-installing-windows-xp-sp3-
access.html

Generate Create Script from existing table in Foxpro

Generate Create Script from existing table in Foxpro

Following code generates sql create table script for table or cursor in current workspace.

FUNCTION GenerateCreateScript
LPARAMETERS tcCursor
LOCAL ARRAY aa[1]
LOCAL lcAlias, lcCursor, lnFields, lcsql, lcfield, lcType, lnlen, lnDecimal, lnI
lcAlias = ALIAS()
IF EMPTY(lcAlias)
RETURN ''
ENDIF

IF EMPTY(tcCursor) OR VARTYPE(tcCursor) # 'C'
lcCursor = SYS(2015)
ELSE
lcCursor = tcCursor
ENDIF
lnFields = AFIELDS(aa)

lcsql = [ create cursor ] + lcCursor + [ (]
FOR lnI = 1 TO lnFields
lcfield = aa[lni, 1]
lcType = aa[lni, 2]
lnlen = aa[lni, 3]
lnDecimal = aa[lni, 4]
lcsql = lcsql + lcfield + ' ' + lcType + [(] + TRANSFORM(lnlen)
IF lnDecimal > 0
lcsql = lcsql + [,] + TRANSFORM(lnDecimal)
ENDIF
lcsql = lcsql + [)]
IF lnI = lnFields
lcsql = lcsql +[ ;] + CHR(13) + CHR(10) + [)]
ELSE
lcsql = lcsql + [ ;] + CHR(13) + CHR(10) + [, ]
ENDIF
ENDFOR

RETURN lcsql
ENDFUNC

Save attachment from Outlook using Foxpro

I need to reprint cards which I receive as Excel file in email. I use the following code to save the attachment in my folders

Local loOutlook, loMapi, loFolder, loInbox, loMailItems, loMail
Local lcBadCardsDir, lcSubject, lnAttachments, loAttach, lcAttachFile, lcSaveFile, lnI

lcBadCardsDir = "c:\badcards\"
Clear
loOutlook = Createobject("outlook.application")
loMapi = loOutlook.GetNamespace("MAPI")
loFolder = loMapi.Folders("Imap.gmail.com")
loInbox =loFolder.Folders("Inbox")
loMailItems = loInbox.Items
For Each loMail In loMailItems
lcSubject = loMail.subject
If 'bad card' $ Lower(lcSubject)
lnAttachments = loMail.Attachments.Count
If lnAttachments > 0
For lnI = 1 To lnAttachments
loAttach = loMail.Attachments.Item[lnI]
lcAttachFile =loAttach.filename
?Justext( lcAttachFile)
If Lower(Justext( lcAttachFile)) = 'xls'
lcSaveFile = lcBadCardsDir + lcAttachFile
loAttach.saveasfile(lcSaveFile )
Endif
Endfor
Endif
Endif
Endfor
loMail = Null
loMailItems = Null
loInbox = Null
loFolder =Null
loMapi = Null
loOutlook = Null

Building ASP.NET 2.0 Web Sites Using Web Standards

Building ASP.NET 2.0 Web Sites Using Web Standards


http://msdn.microsoft.com/en-us/library/aa479043.aspx

Save Excel to Excel 5

Foxpro gives error "Invalid Excel file format", when appending from Excel file which was created in mac version. To append this file, we need to save it in older version (Excel 5).
I use the following code to save file in Excel 5.

lcx = Getfile('xls')

lcfname = JUSTFNAME(lcx)

lcsave = FULLPATH(CURDIR()) +lcfname + '_5.xls'
loExcel = CREATEOBJECT("Excel.Application")
lowb = loExcel.Workbooks.Open(lcx)
lowb.SaveAs(lcsave, 39)
loexcel.Quit()
loexcel = null

Trace messages in IE

To view ASP.net Ajax , Sys.Debug.trace messages in IE, put this markup near the end of the page.

<textarea id="TraceConsole" rows="20" cols="60"></textarea>

Create desktop shortcut to a Visual FoxPro application

Microsoft KB -

creates desktop shortcut to a Visual FoxPro application

http://support.microsoft.com/kb/q238553/

Update Excel sheet from Foxpro

My client wants to get tracking number on the spreadsheet they supplied. Tracking numbers are in foxpro cursor created by exporting from Fedex software.


lca = ALIAS() && table created from Fedex Export


lcxls = GETFILE('xls')

ConnectionString = [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=]+lcxls +[;Extended Properties="Excel 8.0;HDR=Yes;"]
oConn = CREATEOBJECT('adodb.Connection')
oConn.OPEN(ConnectionString)
ors = CREATEOBJECT('adodb.recordset')
ors.LockType = 3

lcsql = "select * from [Sheet1$] where track is null"
ors.OPEN(lcsql, oConn)

DO WHILE NOT ors.EOF
lcPid = TRANSFORM(ors.Fields(0).value)
SELECT (lca)
LOCATE FOR pid = lcpid
IF FOUND()
ors.FIELDS("Track").VALUE = ALLTRIM(track)
ors.FIELDS("Shipdate").VALUE = shipdate
ENDIF
ors.UPDATE()
ors.movenext()
ENDDO
ors.CLOSE()
oConn.CLOSE()

RETURN

Remove line numbers from code listing

Sometime code listed on web page may contain line numbers. I have used regular expression to remove the line numbers from the code.

LPARAMETERS tcString

LOCAL loRegex as "vbscript.regexp"
LOCAL ARRAY laLines[1]
LOCAL lnLines, lcLine, lcStr, lni

loRegex = CREATEOBJECT('vbscript.regexp')
loRegex.GLOBAL = .T.
loRegex.IgnoreCase = .F.
loRegex.Multiline = .T.

IF EMPTY(tcString)
tcString = _CLIPTEXT
ENDIF

lnLines = ALINES(laLines, tcString)

lcStr = ""
loRegex.PATTERN = "^\s*(\d+:?)(.*)"
FOR lni = 1 TO lnLines
lcLine = laLines[lnI]
IF EMPTY(lcLine)
*!* lcStr = lcStr + lcLine
ELSE
lcStr = lcStr + loRegex.REPLACE(lcLine, "$2") + CHR(13) + CHR(10)
ENDIF
ENDFOR


WAIT CLEAR
CREATE CURSOR curtmpfile (CODE m)
INSERT INTO curtmpfile VALUES (lcStr)

MODIFY MEMO CODE

RETURN

Missing Index in Sql Server

The following query displays required index on sql database.

use databse
go

select *
from sys.dm_db_missing_index_details
where database_id = db_id()

Open windows explorer from Foxpro

Open windows explorer from Foxpro in Current Directory

LOCAL loshell as Shell32.application
loshell = CREATEOBJECT('shell.application')
loShell.Explore(FULLPATH(CURDIR()))
loShell = null

Foxpro Regular Expression

Below code converts Sql code generated for Insert from Management studio into Foxpro code.
Right click on a table in SSMS -> Script table As -> Insert To -> New Query Editor Window

Below was generated from Publishers table from Pubs database

INSERT INTO [pubs].[dbo].[publishers]
([pub_id]
,[pub_name]
,[city]
,[state]
,[country])
VALUES
(<pub_id, char(4),>
,<pub_name, varchar(40),>
,<city, varchar(20),>
,<state, char(2),>
,<country, varchar(30),>)


Select all & Copy

In foxpro command window
lcStr = _cliptext
This will put the generated code in clipboard.



loR = CREATEOBJECT('vbscript.regexp')
lor.Global = .t.
lor.Multiline = .t.
lor.IgnoreCase = .t.
lor.Pattern = "(,\s\w+\(?\d*\)?,\>)"
lcstr = lor.Replace(lcSTr, '')
lcstr = STRTRAN(lcStr, '<', '?')

This is what will be the value of lcStr after executing above code

INSERT INTO [pubs].[dbo].[publishers]
([pub_id] ,[pub_name] ,[city] ,[state] ,[country])
VALUES
(?pub_id ,?pub_name ,?city ,?state ,?country)