Opening an Excell file in VFP6.0 2

[MikeL91]

I have a program that will execute a report to a txt file to count states and age, then I import the txt file into a table called statecounts.dbf. I have a excell pivot table that is connected to statecounts.DBF via ODBC.

within the form in the project, I want to now open the Exell file in Excell, I'm not sure how to do this. Also, if I am doing this the hard way (which I am, I'm sure) please advise. The net outcome I'm tring to come to is a cross tab count by 2 fields (file.state & file.age)

Thanks in advance
-Mike

 

[MikeL91]

I've got it to open Excell, but not with my file state.xls

I used

oleapp=createobject("Excel.Application&quot
oleapp.visible=.T.

That opens it, but how do I open a specific spreadsheet?

Thanks in advance

 

[jonscott8]

Try:

oleApp.Workbooks.Open("C:\my.xls&quot Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[Walid]

Hi my friend
To open the Excel WorkBook from you VFP code use..

oX=CREATEOBJECT("EXCEL.APPLICATION")
oX.WORKBOOKS.OPEN("D:\BOOK1.XLS")

Then you can start Automate your Excel object as you wish
For example you can start update some cells in your workbook like that

oX.RANGE("A2").SELECT()
oX.ACTIVECELL.FORMULAR1C1 = "MIKEL91"

This will insert your name in the selected cell.
In general keep in mind that the best way to understand the Excel/Word or whatever COM object is to record what you want to do as macro inside this application (Excel for example) after you done, you can select you macro in the edit mode and study the syntax, after some modifications you can copy and paste the syntax to VFP.

*-- Some ideas to modify the generated code from the macro

If you have code like this
Selection.TypeText Text:="walid"
you have to understand that VBA don't care about the sequence of parameter if you name them in other words
Text can be the parameter number 5 if you need to just send this parameter in VFP you have to use something like that
("","","","","walid&quot. In VBA you don't need this extra typing just use Text:="Walid" so you name you parameter name
*I know we can’t do this in VFP so if you got some thing like that highlight the method name (TypeText in this case)
Then press F1 (Make sure you installed the VBA help files they are in your office installation CD) to see the syntax of this function so you can put you parameter in the right position.

*-- In most cases VBA uses a predefined constants in the code generated by the macro and we don't know what is the run time value for this constant.
Well, this is the Intellisense feature of visual basic. but you can do that if you can get all the constants from the object type library file *.OLB to a header file *.H and include it in you application
For example Excle type library file is Excel9.olb and it is located in C:\program files\Microsoft office\office
*-- One way to do that is to use my program to make the conversion for you

LOCAL lnInFile,lnOutFile,lcText,lcOutText,llOutText
SET DEFA TO GETDIR()
tcInFile  = GETFILE()
lnDotPos  =RAT('.',tcInFile)
tcOutFile = LEFT(tcInFile,lnDotPos-1)+".h" 
CLOS ALL
lnInFile  = FOPEN(tcInFile)
lnOutFile = Fcreate(tcOutFile)
lcText    = ""
lcOutText = ""
llOutText = .F.

DO WHILE !FEOF(lnInFile)
  lcText = FGETS(lnInFile)
  IF "}" $ lcText
    llOutText = .F.
  ENDIF
  IF llOutText
    lcOutText = ALLT(lcText)
    lcOutText = STRT(lcOutText,'=',' ')
    lcOutText = "#DEFINE "+ALLT(lcOutText)
    IF RIGHT(lcOutText,1)=","
      lcOutText=SUBST(lcOutText,1,LEN(lcOutText)-1)
    ENDIF
    =FPUTS(lnOutFile,ALLT(lcOutText))
  ENDIF
  IF "ENUM {" $ UPPER(lcText)
    llOutText = .T.
  ENDIF
ENDDO
CLOS ALL

Rick Strahl has a free class to do that in his web site

http://www.west-wind.com/Webtools.asp,

I didn't have time yet to play with it but I am 150% sure it will work very well.
If you don't want to go through all of this and you want to know the value if any constant very quickly run your macro in the debug mode, stop with you mouse on top of any constant, a tool tip will display the value of that constant.
One more trick, End With in VB = EndWith (one word)
Hope this will help.
Good luck
Walid Magd
Engwam@Hotmail.com

 

[MikeL91]

Thank you very much. That helped me out a lot.

-Mike

 

[Walid]

You very welcome Mike
One more trick I forgot to tell you about getting the macro generated code to work in VFP..
You need to add period before each collection. For example,

With Selection.Borders(xlEdgeBottom)

becomes

With .Selection.Borders(xlEdgeBottom)

^ This period is very important otherwise you will have a run time error.
Thanks
Walid Magd
Engwam@Hotmail.com

 

[ProgCom]

Walid, Thanks for the detailed explanation
~ProgCom

 

[BlindPete]

You really ought to read Excel's help file for detailed info about using Excel this way. It has a map of the excel object model complete with examples(in VB of coarse, but the parameters are all the same)

Look on your computer for the file VBAXL*.CHM

;-)
-Pete

 

[MikeL91]

Walid thanks again, and pete too.

Walid, If I have the code for a macro to update my pivot table, what exactly do I stripe out?? ex:

This opens my spreadsheet:
oleApp = CREATEOBJECT("Excel.Application&quot
oleApp.Visible=.T.
oleApp.WorkBooks.Open("S:\Endorsement\Reports\State.Xls&quot

and this is my macro: (without the 'green lines comments?)

Sub RefreshPivot()
ActiveSheet.PivotTables("PivotTable1&quot.RefreshTable
End Sub

so would I add:
oleApp.ActiveSheet.PivotTables("PivotTable1&quot.RefreshTable

to make it refresh from VFP?

thanks

 

[Walid]

Well, when you open your Excel file NOT from VFP just the normal way, do you need to do anything to get your table updated??
If yes, before you do that, start recording a macro, when you done with all your steps, stop recording the macro.
Then open the macro in the editor, convert the syntax with the guid lines I posted before, or post it and let's try converting it together
Thanks,
Walid

 

[Walid]

I'm so sorry, I didn't realize that you already posted your macro. Yes I think

oleApp.ActiveSheet.PivotTables("PivotTable1").RefreshTable

Will work fine. Don't be afraid to try it the simplest and quickest way to teach your self any thing is to try it.
Good luck my friend
Walid

 

[MikeL91]

It worked!! Walid you RULE!


thanks for all your help, I learned a lot.

-Mike