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
