Need help on placing a Method, it seem that could not be mixed with events

[titoneon]

Hi,
i added to my form a Command Button and i named "Send to Excel" and in the click event for this command button, i put the code you you will see below , when i trying to save that code in the command button click , i am getting
this error " Methods and events cannot contain nested or class definitions"

i am not able to put that code into the command button, can you please explain how can i resolve it please ?

SET EXCLUSIVE OFF
SET SAFETY OFF
SET CPDIALOG OFF
lcjob_no= thisform.txtTextBox.Value
path_1="S:\PRO50\APEX04\POTRAN04" &&VFP 5.0 TABLE TYPE
path_2="F:\MFG\ENG_JOBS" && FOXPRO DOS 2.0 TABLE
path_3="F:\MFG\INS_LOG"

Select INT(VAL(t1.dept)) as ball_no, sht as Sheet, INT(t1.QtyOrd) as QTYORD, INT(t1.Qtyrec) as QTYREC, t2.part_type as PRC, t2.draw_no, t1.item as ItemNo, t1.vpartno as vendorpartno, ;
t1.descrip, t1.Recdate, t1.purno;
From &path_1 t1;
INNER Join &path_2 t2;
ON INT(VAL(t2.ball_no)) = INT(VAL(t1.dept)) ;
WHERE t1.reqno= lcJob_no AND t2.job_no=lcJob_no;
ORDER BY 1 INTO Cursor RESULTS1 READWRITE

Local lnValue, lcSuffix, lcSheet

Scan
lnValue = ball_No
lcSuffix = Iif(Between(Asc(Right(Alltrim(draw_no),1)),48,57),'',Right(Alltrim(draw_no),1))
Do Case
Case Between(m.lnValue,1,999)
lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),1,1)+m.lcSuffix

* Since converting character to integer will loose zeroes on
* the > left, then override those that are less than 200
If m.lnValue < 200
lcSheet = '1'+m.lcSuffix
Endif
Case Between(m.lnValue,8000,8999)
lcSheet = ''
Case Between(m.lnValue,7000,7999)
lcSheet = '7'+m.lcSuffix
Case Between(m.lnValue,9000,9999)
lcSheet = '9'+m.lcSuffix
Otherwise

lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),2,1)+m.lcSuffix
Endcase
Replace sheet With m.lcSheet
ENDSCAN
GO top

** this is the result i want to send to sheet#1, but i need the headers and a title
Select ball_no, Sheet, QTYORD, QTYREC, PRC, ItemNo, vendorpartno, ;
descrip, Recdate, purno;
From RESULTS1 ORDER BY 1 INTO Cursor crsToExcel1


** second select, needs to be displayed on the same excel file but in sheet#2
SELECT INT(VAL(t3.BALL_NO)) as ball_no, sht as Sheet,t3.PART_NO AS ItemNo, t2.draw_no, t3.date as Insp_Date,;
t3.time as Finish_Time, t3.stage as Stage FROM &path_3 t3;
INNER JOIN &path_2 t2;
ON INT(VAL(t3.ball_no)) = INT(VAL(t2.BALL_NO)) ;
WHERE t3.job_no=lcJob_no AND t2.job_no=lcJob_no AND t3.stage="Final";
ORDER BY 1 INTO CURSOR RESULTS2 READWRITE

Scan
lnValue = ball_No
lcSuffix = Iif(Between(Asc(Right(Alltrim(draw_no),1)),48,57),'',Right(Alltrim(draw_no),1))
Do Case
Case Between(m.lnValue,1,999)
lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),1,1)+m.lcSuffix

* Since converting character to integer will loose zeroes on
* the > left, then override those that are less than 200
If m.lnValue < 200
lcSheet = '1'+m.lcSuffix
Endif
Case Between(m.lnValue,8000,8999)
lcSheet = ''
Case Between(m.lnValue,7000,7999)
lcSheet = '7'+m.lcSuffix
Case Between(m.lnValue,9000,9999)
lcSheet = '9'+m.lcSuffix
Otherwise

lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),2,1)+m.lcSuffix
Endcase
Replace sheet With m.lcSheet
ENDSCAN
GO top

** FINAL CURSOR TO BE USED FOR SECOND SHEET IN THE EXCEL FILE
SELECT BALL_NO, sheet, ItemNo, Insp_Date, Finish_Time, Stage FROM RESULTS2 ORDER BY 1 INTO CURSOR crsToExcel2
*** We need real tables on disk to get them via VFPOLEDB
*** Assuming that there may be LFN in cursor data we create a temp DBC too
Local Array laTableNames[2]
laTableNames[1] = Forcepath( Forceext(Sys(2015), 'dbf'), Sys(2023))
laTableNames[2] = Forcepath( Forceext(Sys(2015), 'dbf'), Sys(2023))

Local lcDbc
lcDbc = Forcepath( Forceext( Sys(2015), 'dbc'), Sys(2023))

** Create the temp dbc
Create Database (m.lcDbc)
** and set it as the default database
Set Database To (m.lcDbc)
** and create tables from cursors as part of this new dbc
Select * From crsToExcel1 Into Table (m.laTableNames[1]) Database (m.lcDbc)
Select * From crsToExcel2 Into Table (m.laTableNames[2]) Database (m.lcDbc)

ALTER table (m.laTableNames[1]) ALTER COLUMN RECDATE D NULL
UPDATE (m.laTableNames[1]) SET RECDATE = NULL WHERE EMPTY(RECDATE)

Use In (Select(Juststem(m.laTableNames[1])))
Use In (Select(Juststem(m.laTableNames[2])))
Close Database

** Ready for sending the data to excel
** We also assume that the Excel on this machine could be a 64 bit version
** thus we don't do a direct VFPOLEDB transfer but wrap it in a ADODB.Stream
** We could as well use an ADODB.RecordSet

Local Array laStream[2]
Local ix
For ix = 1 To 2
laStream[m.ix] = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.lcDbc, Textmerge("select * from ('<< m.laTableNames[m.ix] >>')"))
Endfor

*** Now that we have the data in streams, we can get rid of the temp database and tables
Local lcSafety
lcSafety = Set("Safety")
Set Safety Off
Delete Database (m.lcDbc) Deletetables
Set Safety &lcSafety

*** Some more preparation for our sheet names and headers
Local Array sheetNames[2]
sheetNames[1] = "Purchasing Job Number "+lcjob_no
sheetNames[2] = "Manufacturing Job Number "+lcjob_no

*** We want to use custom headers on columns, rather than fieldnames
*** Our list should match the order of columns in our select
*** We could simply omit passing headers, then field names would be used as column names
Local laColumnHeaders[2]
laColumnHeaders[1] = ""
laColumnHeaders[2] = ""

*** Main Excel automation part now
LOCAL oExcel
oExcel = Createobject("Excel.Application")
With oExcel
.DisplayAlerts = .F.
.Workbooks.Add
.Visible = .T.
With .ActiveWorkBook
For ix = 1 To 2
If .sheets.Count < m.ix
.sheets.Add(,.sheets(.sheets.Count)) && Add new sheet
Endif
.WorkSheets(m.ix).Name = m.sheetNames[m.ix]

* Send the data - copy to replacement
VFP2ExcelVariation(m.laStream[m.ix], .WorkSheets[m.ix].Range("A1"), m.laColumnHeaders[m.ix])
.WorkSheets(m.ix).Columns.AutoFit()
ENDFOR
.WorkSheets(1).Range('C:C').NumberFormat = "0" && qtyord
.WorkSheets(1).Range('D').NumberFormat = "0" && qtyREC
.WorkSheets(2).Range('D').NumberFormat = "m/d/yyyy" && inspected_date

.WorkSheets(1).Activate
Endwith
Endwith


Function VFP2ExcelVariation(toStream, toRange, tcHeaders)
Local loRS As AdoDb.Recordset,ix
loRS = Createobject('Adodb.Recordset')
m.loRS.Open( m.toStream )
* Use first row for headers
Local Array aHeader[1]
m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS ) && Copy data starting from headerrow + 1
For ix=1 To Iif( !Empty(m.tcHeaders), ;
ALINES(aHeader, m.tcHeaders,1,','), ;
m.loRS.Fields.Count )
m.toRange.Offset(0,m.ix-1).Value = ;
Iif( !Empty(m.tcHeaders), ;
aHeader[m.ix], ;
Proper(m.loRS.Fields(m.ix-1).Name) )
m.toRange.Offset(0,m.ix-1).Font.Bold = .T.
Endfor
m.loRS.Close()
Endfunc

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
Local loStream As 'AdoDb.Stream', ;
loConn As 'AdoDb.Connection', ;
loRS As 'AdoDb.Recordset'
loStream = Createobject('AdoDb.Stream')
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = m.tcConnection
m.loConn.Open()
loRS = loConn.Execute(m.tcSQL)
m.loRS.Save( loStream )
m.loRS.Close
m.loConn.Close
Return m.lo

Thanks a lot in advance

 

[Mike Lewis]

The message means just what it says. The code contians the definition of two functions / procedures:
[tt]
Function VFP2ExcelVariation(toStream, toRange, tcHeaders)[/tt]

and

[tt]Procedure GetDataAsAdoStream(tcConnection, tcSQL)[/tt]

You need to remove these functions from your method code. If you only ever use them in the form in question, make them methods of that form. Then, when you reference them from anywhere in the form, precede their names with [tt]THISFORM.[/tt] For example: [tt]THISFORM.VFP2ExcelVariation( ... etc.)[/tt]

If you are likely to want to call the functions from other parts of your application, place them in their own PRGs, or in your main function library if you have one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[DSummZZZ]

You need to move your function (VFP2ExcelVariation() ) and your procedure (GetDataAsAdoStream() ) move them to a .prg, or better yet form methods.
You can't have functions or procedures built into other form methods.



-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[titoneon]

Thanks Mike and Dave, i will make them methods and called them from the command button then

 

[titoneon]

Hello guys,
After I have this down and working, as you suggested me above and by the way I already have, my exe file in a server drive and I created a shortcut in another workstation to run, from there the exe file(by the way it is a win xp pro 32bit client machine) when the program is trying to process the code to open Excel, I got the below error, it does not happen when I run the exe file from my workstation but it happens from another workstation, I am missing something in that workstation related with "ADODB" correct ?

Error "Ole Idispatch exception code 0 from ADODB connection operation is not allowed"
I was googlin but I did not find exactly this error

can you please indicate if it is possible ?
Thanks

 

[Mike Lewis]

This has got nothing to do with your original question. Please post this new question in a separate thread, and give it a sensible title that will tell people what it is about. That way, anyone here who can answer the question will become aware of it, as will anyone who might have a similar problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads