Sleepgone


I am creating a workbook for a local company using excel. I've got most of the book worked out except that when information is input into the "main" sheet I need the same information - the entire row - copied over to another sheet within the same book depending on certain criteria. In other words, if the value in a column is 2 then copy the entire row to sheet 2, if it's 3 then copy to sheet 3, and so on. Is there a way I can accomplish this kind of automation


Re: Copying from one sheet to another within the same book

Alex Feldstein


Anything you can do in Excel can be done through COM Automation. The easiest trick is to record a macro while doing the actions in Excel. This creates a VBA file. The code it produces is not the best otr the most efficient, but it copies your actions.

Then you open the VBA Editor (ALT-F11 in Excel) and get to the proper book and file. Copy the code to your VFP editor and with minor modifications (VBA is a simple to understand language), you can easily transform it into VFP Automation code.

If you get stuck in one line or other, copy the line or portion of code here and somebody will be able to help you with the translation.

 A simple example:

loExcel = CreateObject("Excel.Application")
*loExcel.Visible=.t.
loSheet=loExcel.ActiveSheet

*IF <condition>

* select row to copy (row 1 in this example)
loSheet.Rows("1:1").Select()
loExcel.Selection.Copy
loExcel.Sheets("Sheet2").Select()
loExcel.ActiveSheet.Paste()

*ENDIF

 






Re: Copying from one sheet to another within the same book

dni

You need to create a function (macro in excel) and apply it in each cell of column 2. Macro can be done with vb editor from excel using "if" and "copy" range from excel visual basic.





Re: Copying from one sheet to another within the same book

CetinBasoz

I agree that recording a macro is a way to find out code to write. Unfotunately generally the macro Excel creates is ineffective and doesn't really reflect what you would do programmatically. Worse in cases like you describe it's even trickier to do that by hand to create an effective macro. The code here might still be ineffective but is better than Excel macro recording IMHO:

Local oExcel,oSheet,oDataRange
oExcel = Createobject("Excel.Application")
With oExcel
 .WorkBooks.Add
 .Visible = .T.
 oSheet = .ActiveWorkBook.ActiveSheet
 m.oSheet.Name = "Customers"
 VFP2Excel(_samples+'data\testdata.dbc', 'select * from customer', m.oSheet)

 oDataRange = oSheet.UsedRange
 * Add a dummy sheet and copy unique country values
 WITH .ActiveWorkBook.WorkSheets.Add
  .Name = 'dummysheet'
 endwith

 * "Hardcoded" value 9 - Country is 9th field
 oDataRange.Columns(9).AdvancedFilter(2,'',.ActiveWorkbook.Worksheets('dummysheet').Range('a1'),.t.)

 laVals = .ActiveWorkbook.Worksheets('dummysheet').UsedRange.Value
 * Done with dummy sheet - delete
 .DisplayAlerts = .F.
 .ActiveWorkbook.Worksheets('dummysheet').Delete

 * For each country in laVals (if not empty) create a new sheet and copy data
 FOR ix=2 TO ALEN(laVals)
  IF !EMPTY(laVals[m.ix])
   loSheet = .ActiveWorkBook.WorkSheets.Add
   loSheet.name = TRIM(laVals[m.ix])
   oDataRange.AutoFilter(9,laVals[m.ix],,,.f.)
   oSheet.UsedRange.Copy(loSheet.Range('a1'))
   loSheet.UsedRange.Columns.Autofit
  endif
 endfor
 oSheet.AutoFilterMode = .f.
 oSheet.Activate
Endwith


Function VFP2Excel
 Lparameters tcDataSource, tcSQL, toSheet
 If Type('m.toSheet.Application.Name') = 'C' And Atc('Excel',m.toSheet.Application.Name) > 0
  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)
  With m.toSheet
   For ix=1 To loRS.Fields.Count
    .Cells(1,m.ix).Value = Proper(loRs.Fields(m.ix-1).Name)
   Endfor
   .Range('A2').CopyFromRecordSet( loRS )
   .UsedRange.Rows(1).Font.Bold = .T.
   .UsedRange.Columns.Autofit
  Endwith
  loRs.Close
  loConn.Close
 Endif
Endfunc