>Hello,
>
>I need a little help in converting an Excel Macro into VFP syntax. Basically I have a simple spreadsheet and I want highlight all the cells with data in them and generate a column graph. I started recording a macro, used Cntrl-Shift-End to highlight all data and then clicked on the graph wizard button.
>
>The first problems I have is the first line --
>"Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select"
>I can't seem to translate this properly (one of these days I will come up with a translation 'dictionary' to help the conversion process).
>
>The next problem is with the chart source line.
>"ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3"), PlotBy:= _"
>It seems the excel macro put hard names in -- A1:B3, instead of a reference.
>
>Any suggestions? Does the new Hentzenwerke book on VFP and Office Automation cover graphing with excel in detail?
>
>Kevin
>
>*****************
>Macro from excel
>
>Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Charts.Add
> ActiveChart.ChartType = xlColumnClustered
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3"), PlotBy:= _
> xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = "Defect Pareto"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
> With ActiveChart
> .HasAxis(xlCategory, xlPrimary) = True
> .HasAxis(xlValue, xlPrimary) = True
> End With
> ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
> ActiveChart.Legend.Select
> ActiveChart.Legend.LegendEntries(1).Select
> ActiveChart.ChartArea.Select
>End Sub

---

With simple sheets you could use :
lcLastCell = ;
oExcel.ActiveSheet.Range('A1').SpecialCells(xlLastCell).Address(.f.,.f.,xlA1)

or to mimic your select all :
oExcel.ActiveSheet.Range('A1').SpecialCells(xlLastCell).CurrentRegion.Select

Also when you're sending data from VFP you know rows, columns in count. All you need is to convert it to xlA1 notation :

* Return A, AA, BC etc noation for nth column
FUNCTION _GetChar
LPARAMETERS tnColumn && Convert tnvalue to Excel alpha notation
IF tnColumn = 0
 RETURN ""
ENDIF
IF tnColumn <= 26
 RETURN chr(asc("A")-1+tnColumn)
ELSE
 RETURN _GetChar(int(iif(tnColumn % 26 = 0,tnColumn - 1, tnColumn) / 26)) + ;
	_GetChar((tnColumn-1)%26+1)
ENDIF

* Handy wrapper for charting
* select blahblah from blahblah into cursor crsXL
* ...
* lcRowRange = ltrim(str(reccount()+1))	&& 1 for header
* lcColumnRange = _GetChar(fcount())  		
* lcRangeEnd   = lcColumnRange + lcRowRange
* =_ExcelGraph("My Sheet Name", "A1:"+lcRangeEnd, xlBarStacked, xlColumns, ;
* "My Chart Name", "Chart Title","Category Title",90,;
* "Value Title",0,"Series Title")

FUNCTION _ExcelGraph
LPARAMETERS tcDataSheetName, tcRange, tnChartType, tnPlotBy, tcChartName, tcChartTitle, ;
  tcCategoryTitle, tnCategoryRotation, tcValuesTitle, tnValuesRotation, tcSeriesTitle

WAIT window nowait "Charting  " + tcChartName
WITH oExcel
  .ActiveWorkBook.worksheets(tcDataSheetName).activate
  WITH .charts.add	&& Add chart in a new sheet
    * Required to fit different regional range delimiters
    * ie: "A4:H5;Z10:Z20" valid if regional setting is Turkish but not in US.
    * Should be "A4:H5,Z10:Z20" (not valid in Turkish setting) - Excel returns error
    * So instead of changing code per region Union ranges
    tcRange = [oExcel.Sheets("]+;
      tcDataSheetName+;
      [").range("]+;
      strtran(tcRange, ";", ["),oExcel.Sheets("]+;
      tcDataSheetName+;
      [").range("])+;
      [")]

    IF occurs(",",tcRange) = 0
      toRange = &tcRange				&& Single range
    ELSE
      toRange = oExcel.Application.Union(&tcRange)	&& Multipl ranges
    ENDIF
    * Required to fit different regional range delimiters
    .SetSourceData(toRange, tnPlotBy)

    .Location(xlLocationAsNewSheet)

    .hastitle = .t.
    .haslegend = .t.

    .ChartType = tnChartType
    WITH .ChartTitle
      .caption = tcChartTitle
      .Shadow = .t.
      WITH .Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
      ENDWITH
    ENDWITH

    WITH .Axes(xlCategory).TickLabels
      .Font.Size = 8
      .Orientation = xlTickLabelOrientationAutomatic
    ENDWITH
    * Set axis titles
    =_FormatAxis(xlCategory, tcCategoryTitle,tnCategoryRotation) && Set axis titles
    IF .Axes.count > 2			&& Otherwise following formatting
      =_FormatAxis(xlSeries, tcSeriesTitle) && cause illegal operation (if chart is not 3D)
    ENDIF
    =_FormatAxis(xlValue, tcValuesTitle, tnValuesRotation)
    .Legend.Position = xlTop
    .Name = tcChartName
  ENDWITH
ENDWITH


FUNCTION _FormatAxis
LPARAMETERS tnAxis, tcCaption, tnOrientation, tcFontName, tnFontSize, tlBold, tlItalic, tlUnderline, tlShadow
IF type("tcCaption") = "C" and !empty(tcCaption) and type(".Axes(tnAxis)") = "O" && Not empty title and axis is a supported type
  IF type("tnOrientation") # "N"
    tnOrientation = 0
  ENDIF
  IF type("tcFontName") # "C" or empty(tcFontName)
    tcFontName = "Arial"
  ENDIF
  IF type("tnFontSize") # "N"
    tnFontSize = 10
  ENDIF

  WITH .Axes(tnAxis)
    .hastitle = .t.
    WITH .AxisTitle
      .Caption = tcCaption
      .Orientation = tnOrientation
      WITH .Font
        .Name = tcFontName
        .Size = tnFontSize
        .Bold = tlBold
        .Italic = tlItalic
        .Underline = tlUnderline
        .Shadow = tlShadow
      ENDWITH
    ENDWITH
  ENDWITH
ENDIF

Cetin