Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to Send Foxpro Data to an Excel Template Programatically 1
- Thread starter carolx
- Start date
There is nothing attached.
But you would need to use automation to pop data into a spreadsheet.
Here is some random code to do that...
I would suggest as you develop this skill, you use the Excel record macro feature to work out most of the things
you need to do in VBA
Regards
Griff
Keep [Smile]ing
But you would need to use automation to pop data into a spreadsheet.
Here is some random code to do that...
I would suggest as you develop this skill, you use the Excel record macro feature to work out most of the things
you need to do in VBA
Code:
OEXCEL = CREATEOBJECT("Excel.Application")
* make excel visible during development
OEXCEL.VISIBLE = .F.
OEXCEL.WORKBOOKS.OPEN("afile.xls")
OEXCEL.CELLS(1,1).VALUE = "Originator"
OEXCEL.CELLS(1,2).VALUE = "Document Number"
OEXCEL.CELLS(1,3).VALUE = "Revision"
OEXCEL.CELLS(1,4).VALUE = "Date Received"
OEXCEL.CELLS.SELECT
OEXCEL.CELLS.VERTICALALIGNMENT= -4160
OEXCEL.SELECTION.ROWS.AUTOFIT
OEXCEL.SELECTION.COLUMNS.AUTOFIT
OEXCEL.RANGE("A2").SELECT
OEXCEL.DISPLAYALERTS = .F.
OEXCEL.ACTIVEWORKBOOK.SAVE
OEXCEL.QUITRegards
Griff
Keep [Smile]ing
There are 10 kinds of people in the world, those who understand binary and those who don't.
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
-
1
- #3
Olaf Doschke
Programmer
Just google VFP Excel and you'll find lots of automation examples
Visit the FAQ section here and you again find lots of help in the section COM and Automation.
As you didn't attach a file there can be no specific help with it, but in general, you can automate Excel, open an existing file, any type xls, xlsx or xlt, xltx, xltm with macros embedded or not.
You can work with explicit cell row/column numbers to fill in single values and let the macros/code or simply cell formulas do their work. It's really not hard to do and to find this.
If you or your company creates the templates you can do things to simplify the automation process for FoxPro, you could, for example, put expressions into cells that could be evaluated by EVAL() or even better TEXTMERGE(), which can combine text portions and multiple expressions, and then put the result back. So you read and evaluate prepared templates from VFP with current data and reverse the process and program VFP from Excel, the designers of templates will either need to know VFP functions or you program function by your own names, because EVAL() and TEXTMERGE() can of course also execute user-defined functions. Your fantasy and ideas are your only limitations.
If you're not the template maintainer and it comes from a third party you're still able to modify a template not only by putting in some numbers in "user interface" cells and automate that, you can put in expressions, you can name ranges, not only rows and columns or rectangles but any arbitrary selection of cells and then address them by name.
And last not least, how to automate Excel is no secret. Microsft has the MSDN available publicly, including, for example, the Excel object model that tells you what objects and methods and properties exist in the Excel application, the workbooks or a sheet. If you do so interactive in the command line - and this is something I would generally recommend to anyone typically asking for code in forums, you learn and can investigate what else exists through intellisense. Even in the code editor non-interactively you can get intellisense while you write your code by defining:
You just also need to set them so they become such objects, ie loExcel = CreateObject("Excel.Application") and later, after loading the template maybe loSheet = loExcel.ActiveWorkbook.ActiveSheet or loExcel.ActiveWorkbook.Sheets[1] or whatever sheet number is relevant for input into the template.
Also, even while you define LOCAL variables to store object references with the options AS clause and type Excel, intellisense already helps you see which Excel objects are available via COM, so take a look for yourself. Even just to have the names you can google for their MSDN reference description.
Bye, Olaf.
Olaf Doschke Software Engineering
Visit the FAQ section here and you again find lots of help in the section COM and Automation.
As you didn't attach a file there can be no specific help with it, but in general, you can automate Excel, open an existing file, any type xls, xlsx or xlt, xltx, xltm with macros embedded or not.
You can work with explicit cell row/column numbers to fill in single values and let the macros/code or simply cell formulas do their work. It's really not hard to do and to find this.
If you or your company creates the templates you can do things to simplify the automation process for FoxPro, you could, for example, put expressions into cells that could be evaluated by EVAL() or even better TEXTMERGE(), which can combine text portions and multiple expressions, and then put the result back. So you read and evaluate prepared templates from VFP with current data and reverse the process and program VFP from Excel, the designers of templates will either need to know VFP functions or you program function by your own names, because EVAL() and TEXTMERGE() can of course also execute user-defined functions. Your fantasy and ideas are your only limitations.
If you're not the template maintainer and it comes from a third party you're still able to modify a template not only by putting in some numbers in "user interface" cells and automate that, you can put in expressions, you can name ranges, not only rows and columns or rectangles but any arbitrary selection of cells and then address them by name.
And last not least, how to automate Excel is no secret. Microsft has the MSDN available publicly, including, for example, the Excel object model that tells you what objects and methods and properties exist in the Excel application, the workbooks or a sheet. If you do so interactive in the command line - and this is something I would generally recommend to anyone typically asking for code in forums, you learn and can investigate what else exists through intellisense. Even in the code editor non-interactively you can get intellisense while you write your code by defining:
Code:
LOCAL loExcel as Excel.Application, loSheet as Excel.SheetAlso, even while you define LOCAL variables to store object references with the options AS clause and type Excel, intellisense already helps you see which Excel objects are available via COM, so take a look for yourself. Even just to have the names you can google for their MSDN reference description.
Bye, Olaf.
Olaf Doschke Software Engineering
Koen Piller
Programmer
Carol,
In addition to the complete answers from Olaf, I would sgtrongly advise you to visit the site / blog of Vilhelm-Ion Praisach. Vilhelm produced a scale of procedures to append to and append from Excel into from VFP and other MS Office applications.
His procedures work very elegant, they have all good explaining examples and what's also valuable, Vilhelm is rather quick in helping and if ness. builing a new version to your needs.
Regards
Koen
In addition to the complete answers from Olaf, I would sgtrongly advise you to visit the site / blog of Vilhelm-Ion Praisach. Vilhelm produced a scale of procedures to append to and append from Excel into from VFP and other MS Office applications.
His procedures work very elegant, they have all good explaining examples and what's also valuable, Vilhelm is rather quick in helping and if ness. builing a new version to your needs.
Regards
Koen
- Status
Similar threads
- Locked
- Question