Formatting an XLS file using VFP6... 2

[torturedmind]

can anyone give pointers on how to format an excel file using vfp6? after exporting the data, i want my users to see the exported file already formatted (ie. bold headings, different font, etc.)

a small code to start something will do. thnx in advance

kilroy

 

[monikai]

Hi KILROY !
This is my working code (from existing applicztion)
(i use my individual variables)
Ex = CreateObject("Excel.Application&quot
EX.Application.DisplayAlerts = .F.
Ex.WorkBooks.Open(M->RapPath + M->RapPlik)
Ex.Rows("2:3&quot.Select
Ex.Selection.Insert
Ex.Worksheets(1).Range("B1&quot.Select
Poz = ALLTRIM(upper(Ex.ActiveCell.Text))
IF Poz = 'TYP'
zm_t = .T.
Ex.Worksheets(1).Range("I1&quot.Select
Poz = alltrim(upper(Ex.ActiveCell.Text))
ENDIF
DO WHILE Poz <>''
SEEK M->Poz
IF FOUND()
Ex.ActiveCell.Offset(1, 0).Activate
Ex.ActiveCell.FormulaR1C1 = alltrim(DEF.opis)
Ex.ActiveCell.Offset(1, 0).Activate
Ex.ActiveCell.FormulaR1C1 = alltrim(DEF.kolumna)
Ex.ActiveCell.Offset(-2, 0).Activate
ENDIF
Ex.ActiveCell.Offset(0, 1).Activate
Poz = alltrim(upper(Ex.ActiveCell.Text))
ENDDO
Ex.ActiveWorkbook.Close (.T.)
Ex.WorkBooks.Open(M->Sf_LocalPath + 'XlsFormSk.xls')
*run excel macro (XlsSf), which have additional format
*macro is in XlsFormSk.xls (it's 'library' with others excel's macros)
*macro have parameters
Ex.Run('XlsFormSk.xls!ThisWorkBook.XlsSf_1', RapPlik, zm_t, RapDat, RapPath)
Ex.ActiveWorkbook.Close (.F.)
Ex.Quit
Release Ex

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[torturedmind]

thnx monika for ur quick response. am gonna try and play around with your code. i'll keep u posted soon...

kilroy

 

[torturedmind]

monika,

i appreciate your help but your code generated errors so many to count. am not really good at using CreateObject(). maybe explaining your code will help. thnx again so much...

kilroy

 

[Mike Gagnon]

torturedmind

I can see that if your conditions are not the same as Monikai, you would encounter some error, since the code assumes certain conditions. Try this simple test:

Local oExcel,oWorkbook, oSheet
oExcel = CREATEOBJECT("EXCEL.APPLICATION") && Create an instance of excel
oWorkbook = oExcel.workbooks.add() && Add a workbook
oSheet=oWorkbook.ActiveSheet && Make sheet 1 the active sheet

Now you are at the sheet 1 level and you can change or put data on it. Can you explain what you need to do?



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

Hi
Ex = CreateObject(&quot;Excel.Application&quot
EX.Application.DisplayAlerts = .F. &&excel didn't display errors
Ex.WorkBooks.Open(M->RapPath + M->RapPlik) && open existing workbook (M->RapPath + M->RapPlik=path and name this workbook)
Ex.Rows(&quot;2:3&quot.Select &&selct two rows
Ex.Selection.Insert &&insert two rows
Ex.Worksheets(1).Range(&quot;B1&quot.Select &&select range (one cell)
Poz = ALLTRIM(upper(Ex.ActiveCell.Text)) &&variable Poz=upper text from select cell
IF Poz = 'TYP'
zm_t = .T.
Ex.Worksheets(1).Range(&quot;I1&quot.Select && select another cell
Poz = alltrim(upper(Ex.ActiveCell.Text))
ENDIF
DO WHILE Poz <>''
SEEK M->Poz &&find value Poz in my indexed table (which opened earlier)
IF FOUND()
Ex.ActiveCell.Offset(1, 0).Activate && go to nexr row (column the same)
Ex.ActiveCell.FormulaR1C1 = alltrim(DEF.opis) &&write to cell value from table
Ex.ActiveCell.Offset(1, 0).Activate &&next row
Ex.ActiveCell.FormulaR1C1 = alltrim(DEF.kolumna) &&write to cell value from table
Ex.ActiveCell.Offset(-2, 0).Activate &&two row high (the same column)
ENDIF
Ex.ActiveCell.Offset(0, 1).Activate &&next column (the same row)
Poz = alltrim(upper(Ex.ActiveCell.Text)) &&variable Poz=upper text from select cell
ENDDO
Ex.ActiveWorkbook.Close (.T.) &&close workbook with writing changes
Ex.WorkBooks.Open(M->Sf_LocalPath + 'XlsFormSk.xls') &&open existing workbook with macros
*run excel macro (XlsSf), which have additional format
*macro is in XlsFormSk.xls (it's 'library' with others excel's macros)
*macro have parameters
Ex.Run('XlsFormSk.xls!ThisWorkBook.XlsSf_1', RapPlik, zm_t, RapDat, RapPath) &&run macro xlssf with parameters (from VFP)
Ex.ActiveWorkbook.Close (.F.) &&close formatting workbook without changes
Ex.Quit
Release Ex

It's piece of my code and you must have variables, workbooks and table (in my code DEF.dbf)

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[torturedmind]

thnx mike. what i really wanna accomplish is that after exporting a query result to an excel file, the user can view the xls version already formatted (ie. bold headings, different fonts, etc.) i was thinkin of using dynamic data exchange but i dunno where to start...

kilroy

 

[Mike Gagnon]

torteredmind

dynamic data exchange but i dunno where to start...

DDE is (was) used for FoxPro 2.6, now with the advent of COM servers, Excel can be automated. But if you need to achieve what you want, you have a few options.
1. Get VFP7.0 or VFP8.0 that will give you Intellisense to help you understand the properties and methods tou can access via VFP.
2. Use the Object Browser (In VFP7.0 or VFP8.0) to discover the same information as above.
3. Purchase a book on automation (

http://www.hentzenwerke.com/catalogpricelists/autofox.htm),

and read up on automation.
4. Hook up with a programmer that has done this before, and learn from him.
5. Get a thrid party to write the procedure for you.
6. Or change your requirements to something you can handle.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

Yes, Mike, you are all right, but i tried to give example OLE automation which working

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[Mike Gagnon]

monikai

Yes, Mike, you are all right, but i tried to give example OLE automation which working

Yes, I understand, but your example is working for you , because you are calling tables on your that may not be on torturedmind's system, that may cause errors that he may not understand, and cannot progess in his development, where if he would learn by dealing with his , you could manage the error more easily. I dond't want to imply that your example didn't work, but that example only reinforces the fact that torturedmind's nedds to understand the automation process.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[torturedmind]

thnx mike. i'll see what i can do abt it...

kilroy

 

[monikai]

SORRY .....

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[monikai]

My apology: (it's working if you have these fonts:Webdings and Wingdings)
Ex = CreateObject(&quot;Excel.Application&quot
Ex.Application.Visible = .T.
Ex.Application.DisplayAlerts = .F.
Ex.WorkBooks.Add
Ex.Range(&quot;B1&quot.Select
Ex.ActiveCell.FormulaR1C1 = '{'
Ex.ActiveCell.Font.Name=&quot;Wingdings&quot;
Ex.Range(&quot;D1&quot.Select
Ex.ActiveCell.FormulaR1C1 = '&quot;'
Ex.Range(&quot;F1&quot.Select
Ex.ActiveCell.FormulaR1C1 = 'Z'
Ex.Range(&quot;H1&quot.Select
Ex.ActiveCell.FormulaR1C1 = '!'
Ex.Range(&quot;B1:H1&quot.Select
Ex.Selection.Font.Bold=.T.
Ex.Range(&quot;D1:H1&quot.Select
Ex.Selection.Font.Name=&quot;Webdings&quot;
Ex.Columns(&quot;A:A&quot.ColumnWidth=1
Ex.Range(&quot;B1:B1&quot.Select
Ex.Selection.Font.ColorIndex=3
Ex.Selection.Font.Size=72
Ex.Columns(&quot;B:B&quot.ColumnWidth=20
Ex.Columns(&quot;C:C&quot.ColumnWidth=1
Ex.Range(&quot;D11&quot.Select
Ex.Selection.Font.ColorIndex=41
Ex.Selection.Font.Size=72
Ex.Columns(&quot;D&quot.ColumnWidth=20
Ex.Columns(&quot;E:E&quot.ColumnWidth=1
Ex.Range(&quot;F1:F1&quot.Select
Ex.Selection.Font.ColorIndex=6
Ex.Selection.Font.Size=72
Ex.Columns(&quot;F:F&quot.ColumnWidth=20
Ex.Columns(&quot;G:G&quot.ColumnWidth=1
Ex.Range(&quot;H1:H1&quot.Select
Ex.Selection.Font.ColorIndex=7
Ex.Selection.Font.Size=72
Ex.Columns(&quot;H:H&quot.ColumnWidth=20
Ex.Rows(&quot;1:2&quot.Select
Ex.Selection.Insert
Ex.Range(&quot;A1&quot.Select
Ex.ActiveCell.FormulaR1C1 = &quot;For Kilroy from Monika&quot;
Ex.Selection.Font.Bold=.T.
Ex.Selection.Font.Size=20
=messagebox('Close')
Ex.Quit
Release Ex

Kind regards

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[Mike Gagnon]

monikai

Now that is well done. Star for you.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

Hi Mike 1
I apreciate star from you !

Monika from Warszawa (Poland)
(monikai@yahoo.com)