Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
Posts: 972
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
query foxpro in excel 2007 steve h Excel Discussion (Misc queries) 0 May 26th 09 04:29 PM
Excel get external data from Foxpro with .dbf & .ftp Raymondwu Links and Linking in Excel 0 December 24th 06 07:19 AM
Importing Data from FoxPro to Excel djn New Users to Excel 4 April 19th 05 04:43 PM
compatibilidad de excel con foxpro francis garcia valdes Excel Programming 0 October 9th 04 02:57 AM
Excel 2003 and FoxPro tables WKIII Excel Programming 1 June 15th 04 04:55 PM


All times are GMT +1. The time now is 05:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"