Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel to FoxPro
Does anyone know how or a macro that would link Excel to FoxPro, I'm trying
to get some data from a FoxPro application, it was built on the FoxPro engine and was wondering if anyone know of a macro to pull data from FoxPro and place in Excel. Kind of like drawing data from a database and placing it into Excel, does anyone know of a way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel to FoxPro
Data Import External Data New Database Query
Regards, Ryan--- -- RyGuy "drinese18" wrote: Does anyone know how or a macro that would link Excel to FoxPro, I'm trying to get some data from a FoxPro application, it was built on the FoxPro engine and was wondering if anyone know of a macro to pull data from FoxPro and place in Excel. Kind of like drawing data from a database and placing it into Excel, does anyone know of a way to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel to FoxPro
You could try using ADO, since most Foxpro tables utilize the dBase format.
I'd at least test it and see if it will give you what you want from the Foxpro tables. If you are unfamiliar with ADO, here is starting place: http://support.microsoft.com/kb/257819 And then you can use standard SQL to query the data you want. feel free to contact me direct if you have any additional questions. "drinese18" wrote: Does anyone know how or a macro that would link Excel to FoxPro, I'm trying to get some data from a FoxPro application, it was built on the FoxPro engine and was wondering if anyone know of a macro to pull data from FoxPro and place in Excel. Kind of like drawing data from a database and placing it into Excel, does anyone know of a way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel to FoxPro
Try the following. This macro creates a cs.query that interogates the dbf
file or table you want access and creates a working table. The working table in then extracted in its entireity to excel cell by cell... very fast. There are two macros here, you will need to setup a data area with named references to which you can refer in the macros Sub Create_main_TotalsFile1() ' ' Define all the variables required in the CSQuery Dim db As csquery.foxquery Dim main_dbf As String Dim total_dbf As String Dim sql As String Dim time480 As String ' Create the CS object main_dbf = Range("main_dbf").Cells(1, 1).Value total_dbf = Range("main_avg_gar").Cells(1, 1).Value time480 = "480" Set db = CreateObject("CSQuery.Foxquery") 'db.opentable (main_dbf) ' copy fields from main file to temporary working table sql = "SELECT AVG(pv_gar_hed) AS av_gar_hed, AVG(pv_gar_cos) AS av_gar_cos, " & _ "AVG(pv_mc_tot) AS av_ressurp, AVG(pv_t_pr_mc) AS av_t_pr_mc, " & _ "AVG(pv_t_ex_mc) AS av_tot_exp, AVG(pv_ex_o_mc) AS av_exp_or, " & _ "AVG(pv_inv_e_m) AS av_inv_exp, AVG(pv_swi_e_m) AS av_swi_exp, " & _ "AVG(pv_t_cm_mc) AS av_com_exp, AVG(pv_rsur_m1) AS av_rsur_m1, " & _ "AVG(pv_rsur_m2) AS av_rsur_m2, AVG(pv_saifpac) AS av_saifpac " & _ "FROM '" & main_dbf & "' WHERE Val(trim(Time)) = " & time480 & " INTO TABLE '" & total_dbf & "'" db.sqlquerytext = sql db.runquery (True) If db.ErrorMessage < "" Then MsgBox db.ErrorMessage 'abort? End If Set db = Nothing End Sub Sub Get_corp_model_data1() ' Retrieve the corp data from the dbf file and populate the specified range in the spreadsheet template Dim db As Object Dim strvar01 As String Dim strvar02 As String Dim strvar03 As String Dim strvar04 As String Dim strvar05 As String Dim strvar06 As String Dim strvar07 As String Dim strvar08 As String Dim strvar09 As String Dim start_marker As Range Range("corp_data1").ClearContents Set db = CreateObject("CSQuery.Foxquery") filename = Range("main_avg_gar").Cells(1, 1).Value Sheets("Totals_Data").Select strvar01 = Range("$A$18").Cells(1, 1).Value strvar02 = Range("$B$18").Cells(1, 1).Value strvar03 = Range("$C$18").Cells(1, 1).Value strvar04 = Range("$D$18").Cells(1, 1).Value strvar05 = Range("$E$18").Cells(1, 1).Value strvar06 = Range("$F$18").Cells(1, 1).Value strvar07 = Range("$G$18").Cells(1, 1).Value strvar08 = Range("$H$18").Cells(1, 1).Value strvar09 = Range("$I$18").Cells(1, 1).Value strvar10 = Range("$J$18").Cells(1, 1).Value strvar11 = Range("$K$18").Cells(1, 1).Value strvar12 = Range("$L$18").Cells(1, 1).Value Set start_marker = Range("corp1_st") sql = "SELECT " & strvar01 & ", " & strvar02 & ", " & strvar03 & ", " & strvar04 & ", " & _ "" & strvar05 & ", " & strvar06 & ", " & strvar07 & ", " & strvar08 & ", " & _ "" & strvar09 & ", " & strvar10 & ", " & strvar11 & ", " & strvar12 & " " & _ "FROM '" & filename & "' " db.sqlquerytext = sql db.runquery If db.ErrorMessage < "" Then MsgBox db.ErrorMessage 'abort? End If For i = 1 To db.norows For j = 1 To 12 start_marker.Cells(i, j).Formula = db.getcell(i, j) Next j Next i Set db = Nothing End Sub I hope this helps Cheers Bob "drinese18" wrote: Does anyone know how or a macro that would link Excel to FoxPro, I'm trying to get some data from a FoxPro application, it was built on the FoxPro engine and was wondering if anyone know of a macro to pull data from FoxPro and place in Excel. Kind of like drawing data from a database and placing it into Excel, does anyone know of a way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
query foxpro in excel 2007 | Excel Discussion (Misc queries) | |||
Excel get external data from Foxpro with .dbf & .ftp | Links and Linking in Excel | |||
Importing Data from FoxPro to Excel | New Users to Excel | |||
compatibilidad de excel con foxpro | Excel Programming | |||
Excel 2003 and FoxPro tables | Excel Programming |