excel and foxpro  
Author Message
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Dear friends,

when exporting datas to excel from foxpro using the copy to command, there after trying to do some calculations and formating the excel file using automatation macros how could I integrate this in foxpro, I don't know where to start.

thanks for explaining me the functionality

satish



Visual FoxPro1  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, excel and foxpro Top

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=948945&SiteID=1

You can use macros created in Excel but first you should understand and edit the code generated there.

1) Excel macros use VBA and have named parameters ( parameters can have names). ie:

ActiveCell.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=True

VFP however doesn't use named parameters, you need to use positional parameters (that's parameter positions must be in correct order). ie:

Replace syntax is:

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

Within VFP above code would translate to:

ActiveCell.Replace("", "", xlPart, xlByRows, .f., , .t., .t.)

xlPart, xlByRows are numeric constants. You get all constants from ecel once and save in a header file for using later. To get it:

-Open a new code window ( modify command excelconstants.h )

-Invoke Object browser from tools menu

-Click leftmost icon to load COM library. Select COM libraries tab and locate Excel type library there.

-Expand "Excel" node.

-Click and drag "Constants" node on to code window you opened.

-Save that file -

Now you have all those constant in ExcelConstants.h file when you're writing Excel automation code you'd have:

#include ExcelConstants.h

in your code and simply can use xlPart etc.

If you look closely at that sample:

ActiveCell.Replace("", "", xlPart, xlByRows, .f., , .t., .t.)

you would notice that one of the parameters is skipped/omitted (MatchByte parameter). That means "use default" for that parameter. This code still needs some editing to be able to work. ActiveCell.Replace woudln't work directly. You need to specify its parent. If your automation excel object name is oExcel :

oExcel.ActiveCell.Replace

because ActiveCell applies to Application and Window objects and here we decided we mean Application.ActiveCell.

To write Excel code you need Excel VBA documentation and if you select "VBA help" it's installed during installation. If not then rerun install to install VBA help. After installation you'd find chm or hlp file (exact filename and location changes by version - why do they do that I never understood, they like to make life harder for developers). ie:

"C:\Program Files\Microsoft Office\OFFICE11\1033\VBAXL10.CHM"

is default location and name for Excel2003.

This would help you as a starter.

PS: While recording macro helps to find out corresponding automation code, the generated code is not always the best and sometimes it might be the worst.


 
 
Lightening





PostPosted: Visual FoxPro General, excel and foxpro Top

Why don't you do the calculation before converting to the Excel file
 
 
CetinBasoz





PostPosted: Visual FoxPro General, excel and foxpro Top

For simple calculations might be. However in general, Excel knows how to 'calculate'.
 
 
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Dear Friend,

To explain more about the programme my main cursor is a result of a package of queries and also there are about 45 columns already when I am exporting my datas to excel using select and copy to command.

Now using certain columns I am doing some calculation in excel for this I have utilised macros.

Now my problem is how to inject these macros from foxpro

I am trying to understand the concepts as cetin has directed

More information is welcomed in this subject

thanks


 
 
CetinBasoz





PostPosted: Visual FoxPro General, excel and foxpro Top

Satish,

If your calculations do not really need macros then you might create a "template" excel file with calculations on it ready and name the region that data would go into (ie: myDataIsland). Then using automation you could create a new workbook based on that template and simply place the new data into "myDataIsland".


 
 
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Dear Cetin,

Thanks for your reply but I need to create macros for the calculations I have to do so noway of escaping it.

when we use createobject function how to inject datas from a cursor, with copy to command it was very easy but............

thnks for your help


 
 
CetinBasoz





PostPosted: Visual FoxPro General, excel and foxpro Top

No with copy to command it is NOT easy. You see that it's not when you try to copy a lot of records (namely over 16384 records pre VFP9) and/or your data has memo in it. Also copying with type xls,xl5 etc have different results that they create different excel versions compatible files. Type csv,fox2x were better on general. IMHO ideal copy is with using automation and ADO. I think I sent this sample or similar as a reply to one of your previous posts, anyway here it is (this code selects the data you want using plain Select-SQL, then puts the data on specified sheet starting at cell specified - here all starts at A1 but might be say C9. It's possible you could instead push existing data on an existing workbook to right/left/top/bottom to make space for insertion first):

LOCAL oExcel,ix,lcCursorName
LOCAL ARRAY sheetNames[5]
Select emp_id,First_Name,Last_Name,;
Title,Notes ;
from (_samples+'\data\employee') ;
into Cursor crsToExcel1 ;
nofilter

Select cust_id,company,contact,Title,country ;
from (_samples+'\data\customer') ;
into Cursor crsToExcel2 ;
nofilter

Select * ;
from (_samples+'\data\orders') ;
into Cursor crsToExcel3 ;
nofilter

Select * ;
from (_samples+'\data\orditems') ;
into Cursor crsToExcel4 ;
nofilter

Select * ;
from (_samples+'\data\products') ;
into Cursor crsToExcel5 ;
nofilter

sheetNames[1] = "Employees"
sheetNames[2] = "Customers"
sheetNames[3] = "Orders"
sheetNames[4] = "Order Details"
sheetNames[5] = "Products"


oExcel = Createobject("Excel.Application")
With oExcel
.DisplayAlerts = .F.
.Workbooks.Add
.Visible = .T.
With .ActiveWorkBook
For ix = 1 To 5
lcCursorName = 'crsToExcel'+Ltrim(Str(m.ix))
If .sheets.Count < m.ix
.sheets.Add(,.sheets(.sheets.Count)) && Add new sheet
ENDIF
.WorkSheets(m.ix).Name = sheetNames[m.ix]
VFP2Excel(m.lcCursorName, .WorkSheets(m.ix),"A1" )
ENDFOR
.WorkSheets(1).Activate
Endwith
Endwith

Function VFP2Excel
Lparameters tcCursorName, toSheet, tcTargetRange
tcCursorName = Iif(Empty(m.tcCursorName),Alias(),m.tcCursorName)
tcTargetRange = Iif(Empty(m.tcTargetRange),'A1',m.tcTargetRange)
Local loConn As AdoDB.Connection, loRS As AdoDB.Recordset,;
lcTempRs, lcTemp, oExcel
lcTemp = Forcepath(Sys(2015)+'.dbf',Sys(2023))
lcTempRs = Forcepath(Sys(2015)+'.rst',Sys(2023))
Select (m.tcCursorName)
Copy To (m.lcTemp)
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+Sys(2023)
loConn.Open()
loRS = loConn.Execute("select * from "+m.lcTemp)
loRS.Save(m.lcTempRs)
loRS.Close
loConn.Close
Erase (m.lcTemp)
loRS.Open(m.lcTempRs)

With toSheet
.QueryTables.Add( loRS, .Range(m.tcTargetRange)).Refresh()
Endwith
loRS.Close
Erase (m.lcTempRs)


 
 
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Dear Cetin,

Finally I succeeded.

my code looks like this

LOCAL oExcel

oExcel = CREATEOBJECT("Excel.Application")

SELECT *FROM cTariff_CompareRep;

into cursor crstoexcel ;

nofilter

With oExcel

.Workbooks.Add

.Visible = .T.

ncurrent_row=0

scan

ncurrent_row = ncurrent_row + 1

With .ActiveWorkbook.ActiveSheet

FOR i=1 TO FCOUNT()

cfield=FIELD(i)

oExcel.cells(ncurrent_row,i).value=&cfield

oExcel.cells(ncurrent_row,40).formula="="+columnl(30)+columnl(35)

ENDFOR

Endwith

endscan

Endwith

When I am using columnl() I am getting an error message saying columnl.prg does not exist.

Can you help me where can I find this file in foxpro

thanks

satish


 
 
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Dear friends,

I found out the great prg file and my project works as I wanted.

nice weekend, thanks for all your kind suggestions especially for cetin

satish


 
 
CetinBasoz





PostPosted: Visual FoxPro General, excel and foxpro Top

Satish,

Whatever I write you don't seem to be reading them at all. You found a way that would work but slow.

cfield=FIELD(i)

oExcel.cells(ncurrent_row,i).value=&cfield

oExcel.cells(ncurrent_row,40).formula="="+columnl(30)+columnl(35)

ENDFOR

This part was already in codes that I have sent you and were optimized codes. ie:

is just 2 commands sent to Excel once compared to your reccount() times sending it.

&cfield part is completely unnecessary. The sample I've sent already does that via ADO. Look at it closely, you're filling it cell-by-cell. That means reccount()*fcount() VBA commands. VBA is slow so avoid unnecessary VBA calls.

Same holds true for last formula. I have sent you a formula sample that sets the formula for the whole column at once. You again doing it reccount() times. In short when working with excel think "scan ... endscan" style only if you can't do it otherwise.

About ColumnL(): I don't know what that is (neither in VFP nor in excel I haven't heard of such a function.


 
 
Satish33





PostPosted: Visual FoxPro General, excel and foxpro Top

Cetin,

I found out this method while working with your sample code, yes as you said it works slow as it fills the excel sheet row by row.

I continued to work with your sample for a better result finally I succeeded.

thanks for all your comments I learned a lot here, again I hold on thanking you cetin for your valuable teachings.

satish