I don't know the columns from run-to-run. The data can come from anywhere and contain anything and I want to get it into a dbf so I can do more work on the data. The data has lots of "comments" types of fields, randomly, and I want to make sure that I do not lose ANY of the long comment information. Some of the comments take 5 fields defined as 254 characters to hold all of it. I also, for ease later, want to keep the extra columns inserted adjacent to the 'source' column. And, of course, as I insert columns the column IDs ("AA", "DT", etc) change.
So I start my work from the right side, so that my reference columns remain with the same col#.

Now I do see that I could essentially do the work to a new sheet in the same workbook, and I think I will give that a go.

No pain, Cetin < s >


>I warned you I could be a pain in the neck:)
>This way or that way you know those columns from run-to-run. Since you know xx to add 1 to it I assume you do. OK this is the last one and I would let you go:) More than needed variables here for some clarity what it's doing (note that it intentionally includes a memo as I remember you said dividing memo into columns and later deal with).
>

>Select emp_id,First_Name,Last_Name,Title,Notes ;
>    from (Home()+'samples\data\employee') ;
>    into Cursor crsToExcel ;
>    nofilter
>
>oExcel = Createobject("Excel.Application")
>With oExcel
>    .Workbooks.Add
>    .Visible = .T.
>    With .ActiveWorkBook.ActiveSheet
>        .Range('A1:F7000').Cells.Value = 'filled'
>        lnStartColumn = 3 && this is xx+1
>        lnColumns = Fcount()
>        lcColStart = _GetChar(m.lnStartColumn) && Get Excel column name
>        lcColEnd = _GetChar(m.lnStartColumn+m.lnColumns-1)
>        lnRowEnd = Reccount()+1
>        lcDataRange = Textmerge('<<m.lcColStart>>1:<<m.lcColEnd>><<m.lnRowEnd>>')
>        .Range(Textmerge('<<m.lcColStart>>:<<m.lcColEnd>>')).EntireColumn.Insert
>        .Range(m.lcDataRange).Name = "myDataIsland"
>        VFP2Excel(oExcel, 'crsToExcel', .Name, "myDataIsland") && Insert data there
>    Endwith
>Endwith
>
>* 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
>
>Function VFP2Excel
>Lparameters toExcel, tcCursorName, tcSheetName, tcRange
>tcCursorName = Iif(Empty(m.tcCursorName),Alias(),m.tcCursorName)
>tcSheetName = Iif(Empty(m.tcSheetName),"Sheet1", m.tcSheetName)
>Local loConn As AdoDB.Connection, loRS As AdoDB.Recordset,;
>    lcTempRs, lcTemp, oExcel
>lcTemp = Forcepath(Sys(2015)+'.dbf',Sys(2023))
>lcTempRs = Forcepath(Sys(2015)+'.rst',Sys(2023))
>Select (m.tcCursorName)
>Copy To (m.lcTemp)
>loConn = Createobject("Adodb.connection")
>loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+Sys(2023)
>loConn.Open()
>loRS = loConn.Execute("select * from "+m.lcTemp)
>loRS.Save(m.lcTempRs)
>loRS.Close
>loConn.Close
>Erase (m.lcTemp)
>loRS.Open(m.lcTempRs)
>
>With toExcel.WorkSheets( ;
>        Iif(Empty(m.tcSheetName),;
>        .ActiveWorkBook.ActiveSheet.Name, ;
>        m.tcSheetName))
>    .QueryTables.Add( loRS, .Range(m.tcRange)).Refresh()
>    .Range(m.tcRange).AutoFormat(8) && xlRangeAutoFormatColor2
>    .Range("A1").Activate
>Endwith
>loRS.Close
>Erase (m.lcTempRs)
>

>PS:ADO part is based on FoxyClasses' dbf2excel class.
>Cetin
>
>
>>My first problem was that I could not name a range by "A:A" format, because I did not know which columns were involved from run-to-run.
>>TamarG came up with

            Set oRange = oSheet.Columns(xx + 1)
>>oRange.Insert (xlShiftToRight)

, which did the job nicely compared to cell-by-cell.
>>cheers, and thanks for the hints