Oh yeah baby! Life on the Edge! There's just something about those hidden formulae that tends to make your life, how do the Chinese say it? - "interesting"<G>
JH -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Smith Sent: Tuesday, August 14, 2007 3:11 PM To: [email protected] Subject: Using Excel to handle VPF data Howdy Fox-folk! Below is a series of techniques I've ( sort-of ) perfected that allow local users at my company to benefit from up to date data we now have available within a VFP .dbc file.( It leverages the data extraction portion of Gil Hale's VFP-based DCMS system by tapping into the core .dbc file Gil created for us that houses the combined extraction data from across our company stores.) The thought is not everyone needs/can have access to/ has the skills or interest in learning the DCMS front end, so instead I aimed for MS Office Excel, which is comfortable and non-threatening to non-PC folk. The final product kicks out VFP .dbc data that updates automatically when opened and just works great. It's probably old news, but I don't remember reading anything like this on these lists before, so hopefully this will come in handy some time. ______________________________________ How to create a Excel-friendly database connection using the DCMS CDBM translationengine.dbc. Assumptions: 1.Excel 2007 2.VFP OLEDB provider drivers on client PC. 3.Paste-able SQL query that uses data from the translation engine.dbc or good enough SQL skills to be able to write the statemnt from scratch. I cut and paste mine from Visual Studio connection templates I created, but whatever works for you is fine. ______________________________________ One-time setup of dataconnection to the VFP database. ______________________________________ 1. Open Excel, click on Data tab, then From Other Sources, then Data Connection Wizard 2. Click other/advanced, then select 'MS OLEDB Provider for Visual Fox Pro' and click the next button. In next window choose the VFP databases option, then click browse. Navigate to the translationengine.dbc and select it. Click the 'ok' button and then click the 'test connection' button to test it using the machine collating sequence option. 3. click the 'okay' button, and uncheck the 'select a specific table' option in the new window. Click Next, then type in a new name for the connection file, and for the freindly name as well. click Finish, then Cancel. 4. Still on the Excel data tab, click connections, then Add.choose the file you just created from above. when the table list appears, select any table you wish, then click Properties, then click definitions in the new window. delete all contents of the command text window and paste in your SQL query code or type it in manually if you're skilled in SQL. Once done, click Export Connection File and save it somewhere handy. I saved mine to a shared folder on the DCMS server so I could use it from different client PC's without having to rewrite it every time. click OK, click close. whew! All done! _______________________________________ data connection use _______________________________________ 1. From a new workbook, click the Data tab, then connections, click Add, then click the browse button. Navigate to the .odc file you just saved and select it. In the window that opens up, draw out a size appropriate to the ooutput of your query, or just select the whole sheet and then click okay. the query processess, then taa daa! your data appears :) Now, save the workbook somewhere on the local client PC. If you have multiple users of the same query, just copy the workbook to whoever needs it, just be sure they have the same drive mappings.Once created, you can use the sheets within Office 2003 as well with only some minor cosmetic loss. I hope this was helpful :) David Smith Systems Administrator Doan Family of Dealerships (585) 352-6600 ext.1730 [EMAIL PROTECTED] No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.11.17/951 - Release Date: 8/13/2007 10:15 AM --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]@shelbynet.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
