Anders
Tue May 10 13:16:47 CDT 2005
Peter
You can use the QueryTables class to make Excel read VFP data through an VFO
PLE DB connection. Make a new query with no more than 32000 rows for each
sheet
SELECT * FROM MyTable WHERE lastname='A' TOP 3200 ORDER BY name
* get the 32000th name, Say it's 'Ericsen, Peter'
SELECT * FROM MyTable WHERE lastname > 'Ericson, Peter' TOP 3200 ORDER BY
name
and so on
This is the code for a single query using VFP's Northwind demo database:
*********
LOCAL oExcel AS Excel.APPLICATION
LOCAL oBook AS Excel.Workbook
LOCAL oSheet AS OBJECT
oExcel = CREATEOBJECT("Excel.Application")
oBook = oExcel.Workbooks.ADD
oSheet = oBook.Worksheets(1)
* 'Create the QueryTable object.
LOCAL oQryTable AS OBJECT, sNorthwind AS STRING
sNorthwind = HOME(2) + [NORTHWIND\NORTHWIND.DBC]
oQryTable = oSheet.QueryTables.ADD ;
("OLEDB;Provider=VFPOLEDB.1;Data Source="+sNorthwind+";",
oSheet.RANGE("A1"),"Select * from Orders")
oQryTable.RefreshStyle = 2 && xlInsertEntireRows = 2
oQryTable.REFRESH(.F.)
oExcel.VISIBLE=.T.
******************
-Anders
"Peter Aitken" <peter@benchmarkresearch.org> wrote in message
news:TW1ge.5156$HY6.1935@trndny05...
> Neil,
>
> Thanks so much for your response, and for the Hentzenwerke reference.
>
> Here's a piece of code that works perfectly in Excel but there are a
number
> of statements that don't run from within FoxPro. What's my best approach
to
> analyzing why a statement doesn't work in FoxPro?
>
> Peter
>
>
> Private Sub DB10_Click()
> '
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
> ActiveChart.PlotArea.Select
>
> ' Delete Existing Series
> If ActiveChart.SeriesCollection.Count = 10 Then
> ActiveChart.SeriesCollection(10).Delete
> ActiveChart.SeriesCollection(9).Delete
> ActiveChart.SeriesCollection(8).Delete
> ActiveChart.SeriesCollection(7).Delete
> ActiveChart.SeriesCollection(6).Delete
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 9 Then
> ActiveChart.SeriesCollection(9).Delete
> ActiveChart.SeriesCollection(8).Delete
> ActiveChart.SeriesCollection(7).Delete
> ActiveChart.SeriesCollection(6).Delete
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 8 Then
> ActiveChart.SeriesCollection(8).Delete
> ActiveChart.SeriesCollection(7).Delete
> ActiveChart.SeriesCollection(6).Delete
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 7 Then
> ActiveChart.SeriesCollection(7).Delete
> ActiveChart.SeriesCollection(6).Delete
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 6 Then
> ActiveChart.SeriesCollection(6).Delete
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 5 Then
> ActiveChart.SeriesCollection(5).Delete
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 4 Then
> ActiveChart.SeriesCollection(4).Delete
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 3 Then
> ActiveChart.SeriesCollection(3).Delete
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 2 Then
> ActiveChart.SeriesCollection(2).Delete
> ActiveChart.SeriesCollection(1).Delete
> ElseIf ActiveChart.SeriesCollection.Count = 1 Then
> ActiveChart.SeriesCollection(1).Delete
> Else
> End If
> ' End Delete Existing Series
>
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(1).Values = "=EXP!R133C28:R133C37"
> ActiveChart.SeriesCollection(1).Name = "=""JRPO Percentiles"""
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(2).Values = "=EXP!R132C28:R132C37"
> ActiveChart.SeriesCollection(2).Name = "=""25-50"""
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(3).Values = "=EXP!R131C28:R131C37"
> ActiveChart.SeriesCollection(3).Name = "=""50-75"""
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(4).Values = "=EXP!R130C28:R130C37"
> ActiveChart.SeriesCollection(4).Name = "=""75-90"""
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(5).ChartType = xlXYScatter
> ActiveChart.SeriesCollection(5).Values = "=EXP!R135C28:R135C37"
> Dim SchoolName As Range
> Set SchoolName = Worksheets("EXP").Range("AB4")
> ActiveChart.SeriesCollection(5).Name = SchoolName
>
> ActiveChart.SeriesCollection(1).XValues = "=EXP!R9C28:R9C37"
> ActiveChart.Axes(xlCategory).Select
> With Selection
> .MajorTickMark = xlOutside
> .MinorTickMark = xlNone
> .TickLabelPosition = xlNextToAxis
> End With
>
> Dim Title_Chart As Range
> Set Title_Chart = Worksheets("EXP").Range("AB1")
>
> Dim Title_Value As Range
> Set Title_Value = Worksheets("EXP").Range("AB2")
>
> Dim Title_Category As Range
> Set Title_Category = Worksheets("EXP").Range("AB3")
>
> Y_Max =
>
Application.WorksheetFunction.Max(Worksheets("EXP").Range("AB123:AK123,AB135
:AK135"))
>
> If Application.WorksheetFunction.And(Y_Max > 0, Y_Max <= 10) Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max)
> ElseIf Application.WorksheetFunction.And(Y_Max > 10, Y_Max <= 100)
Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max / 10) * 10
> ElseIf Application.WorksheetFunction.And(Y_Max > 100, Y_Max <= 1000)
> Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max / 100) * 100
> ElseIf Application.WorksheetFunction.And(Y_Max > 1000, Y_Max <= 10000)
> Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max / 1000) *
1000
> ElseIf Application.WorksheetFunction.And(Y_Max > 10000, Y_Max <=
100000)
> Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max / 10000) *
> 10000
> ElseIf Application.WorksheetFunction.And(Y_Max > 100000, Y_Max <=
> 1000000) Then
> YScale_Max = Application.WorksheetFunction.Even(Y_Max / 100000) *
> 100000
> Else
> End If
>
> Y_Min =
>
Application.WorksheetFunction.Min(Worksheets("EXP").Range("AB127:AK127,AB135
:AK135"))
>
> If Application.WorksheetFunction.And(Y_Min > 0, Y_Min <= 10) Then
> ' YScale_Min = Application.WorksheetFunction.Even(Y_Min) - 2
> YScale_Min = 0
> ElseIf Application.WorksheetFunction.And(Y_Min > 10, Y_Min <= 100)
Then
> YScale_Min = Application.WorksheetFunction.Even(Y_Min / 10) * 10 -
> 20
> ElseIf Application.WorksheetFunction.And(Y_Min > 100, Y_Min <= 1000)
> Then
> YScale_Min = Application.WorksheetFunction.Even(Y_Min / 100) *
100 -
> 200
> ElseIf Application.WorksheetFunction.And(Y_Min > 1000, Y_Min <= 10000)
> Then
> YScale_Min = Application.WorksheetFunction.Even(Y_Min / 1000) *
> 1000 - 2000
> ElseIf Application.WorksheetFunction.And(Y_Min > 10000, Y_Min <=
100000)
> Then
> YScale_Min = Application.WorksheetFunction.Even(Y_Min / 10000) *
> 10000 - 20000
> ElseIf Application.WorksheetFunction.And(Y_Min > 100000, Y_Min <=
> 1000000) Then
> YScale_Min = Application.WorksheetFunction.Even(Y_Min / 100000) *
> 100000 - 200000
> Else
> End If
>
> ActiveChart.SeriesCollection(1).Select
> With Selection.Border
> .Weight = xlThin
> .LineStyle = xlNone
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> Selection.Interior.ColorIndex = xlNone
>
> ActiveChart.SeriesCollection(2).Select
> With Selection.Border
> .ColorIndex = 5
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> With Selection.Interior
> .ColorIndex = 34
> .Pattern = xlSolid
> End With
>
> ActiveChart.SeriesCollection(3).Select
> With Selection.Border
> .ColorIndex = 5
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> With Selection.Interior
> .ColorIndex = 37
> .Pattern = xlSolid
> End With
>
> ActiveChart.SeriesCollection(4).Select
> With Selection.Border
> .ColorIndex = 5
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> End With
>
> ActiveChart.SeriesCollection(5).Select
> With Selection.Border
> .Weight = xlHairline
> .LineStyle = xlNone
> End With
> With Selection
> .MarkerBackgroundColorIndex = 3
> .MarkerForegroundColorIndex = 5
> .MarkerStyle = xlTriangle
> .Smooth = False
> .MarkerSize = 8
> .Shadow = False
> End With
>
> ActiveChart.Axes(xlValue).Select
> Selection.TickLabels.NumberFormat = "#,##0"
>
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title_Chart
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Title_Value
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> Title_Category
> End With
>
> ActiveChart.PlotArea.Select
> ActiveChart.Axes(xlValue).Select
> With ActiveChart.Axes(xlValue)
> .MinimumScale = YScale_Min
> .MaximumScale = YScale_Max
> .MinorUnit = YScale_Max / 10
> .MajorUnit = YScale_Max / 10
> .Crosses = xlCustom
> .CrossesAt = YScale_Min
> .ReversePlotOrder = False
> .ScaleType = xlLinear
> .DisplayUnit = xlNone
> End With
>
> ActiveChart.ChartArea.Select
> Selection.AutoScaleFont = True
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
>
> ActiveChart.Axes(xlCategory).Select
> Selection.TickLabels.AutoScaleFont = True
> With Selection.TickLabels.Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
> ActiveChart.Axes(xlValue).Select
> Selection.TickLabels.AutoScaleFont = True
> With Selection.TickLabels.Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
>
> ' Show Legend
> ActiveSheet.Range("A4").Select
> SNL = ActiveCell.Value
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.PlotArea.Select
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> Selection.Left = 85 - SNL / 2
> With Selection.Border
> .Weight = xlThin
> .LineStyle = xlNone
> End With
> Selection.Shadow = False
> Selection.Interior.ColorIndex = xlAutomatic
> Selection.AutoScaleFont = True
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
>
> ActiveChart.PlotArea.Select
> Selection.Height = 240
>
> ActiveChart.ChartArea.Select
> '
> End Sub
>
> "Neil Waterworth" <nospam@microsoft.con> wrote in message
> news:u1r12oPVFHA.2684@TK2MSFTNGP09.phx.gbl...
> > Hi Peter,
> >
> > What lines are you having problems with? Could you post some code?
There's
> > also a very good VFP/MS Office automation reference available from
> > www.hentzenwerke.com.
> >
> > Regards,
> > Neil
> >
> > "Peter Aitken" <peter@benchmarkresearch.org> wrote in message
> > news:AWPfe.5067$Dn.2151@trndny02...
> >> In an Excel workbook, I have a long macro in VBE that creates a chart
and
> >> sets properties of axes, series collections, etc.
> >>
> >> I want to automate the process from within FoxPro. I copied the VBE
code
> >> into FoxPro and much of it works but not all. What do I need to know
> >> about
> >> getting the VBE code to work from within FoxPro?
> >>
> >> Any help will be gratefully received.
> >>
> >> Thanks.
> >>
> >> Peter
> >>
> >>
> >
> >
>
>
>