Excelde belirlenen hücrelere kilit koyma

#Define xlTop -4160

Local oExcel

oExcel = Createobject("Excel.Application")

With oExcel

  .WorkBooks.Add

  .Visible = .T.

  VFP2Excel(_samples+'data\testdata.dbc',;

    'select emp_id,first_name,last_name,'+;

    'cast(EVL(birth_date,null) as d) as birth_date,'+;

    'CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee',;

    .ActiveWorkBook.ActiveSheet.Range('A1'))

 

  * This part is cosmetic

  With .ActiveWorkBook.ActiveSheet

    .UsedRange.VerticalAlignment = xlTop && set all to top

    With .Columns(5)

      .ColumnWidth = 80 && 80 chars width

      .WrapText = .T.

    Endwith

    .UsedRange.Columns.AutoFit

    .UsedRange.Rows.AutoFit

  Endwith

 

 

  *** Protection

  With .ActiveWorkBook.ActiveSheet

    .UsedRange.Locked = .T.

    .Range('B:C').Locked = .F.

    .Protect('mypassword')

  Endwith

Endwith

 

Function VFP2Excel

  Lparameters tcDataSource, tcSQL, toRange

  Local loConn As AdoDB.Connection, ;

    loRS As AdoDB.Recordset,;

    ix

  loConn = Createobject("Adodb.connection")

  loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource

  loConn.Open()

  loRS = loConn.Execute(m.tcSQL)

 

  For ix=1 To loRS.Fields.Count

    toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)

    toRange.Offset(0,m.ix-1).Font.Bold = .T.

  Endfor

  toRange.Offset(1,0).CopyFromRecordSet( loRS )

  loRS.Close

  loConn.Close

Endfunc