>>>>Hi all
>>>>
>>>>I have some Excel automation code (see below) which works to an extent. What it does is opens a xls file and does a SaveAs() xlsx file in case such a target file is not found. Then I try to open both these files and I need to copy the first sheet in the xls into the xlsx first sheet but the Paste() seems to fail for me.
>>>>
>>>>Please advise on how can I get the updated data from the old format to the new format.
>>>>
>>>>Kind regards
>>>>Bhavbhuti
>>>>
>>>>
>>>>lcXLS = (MyOldExcelFormatFilePathName)
>>>>lcXLSX = lcXLS + "X"
>>>>
>>>>
>>>>loExcel = CREATEOBJECT("Excel.Application")
>>>>
>>>>IF VARTYPE(loExcel) <> "O"
>>>> MESSAGEBOX("Cannot start Excel to convert file to XLSX")
>>>>
>>>> RETURN
>>>>ENDIF
>>>>
>>>>*loExcel.Visible = .F.
>>>>
>>>>
>>>>loWBOld = loExcel.Application.WorkBooks.Open(lcXLS)
>>>>
>>>>IF NOT FILE(lcXLSX)
>>>> loWBOld.SaveAs(lcXLSX, 51)
>>>>ENDIF
>>>>
>>>>
>>>>loWBNew = loExcel.Application.WorkBooks.Open(lcXLSX)
>>>>
>>>>
>>>>*** copy the first sheet from old to new
>>>>loExcel.WINDOWS(JUSTFNAME(lcXLS)).ACTIVATE
>>>>loExcel.Sheets(JUSTFNAME(lcXLS)).Copy(loWBOld.Sheets(1))
>>>>
>>>>loExcel.WINDOWS(JUSTFNAME(lcXLSX)).ACTIVATE
>>>>loWBNew.Paste()
>>>>
>>>>
>>>>loWBOld.Close()
>>>>loWBNew.Close()
>>>>
>>>>loWBOld = NULL
>>>>loWBNew = NULL
>>>>loExcel = NULL
>>>>
>>>
>>>
>>>You probably need to paste to the worksheet, not the workbook.
>>>
>>>Tamar
>>
>>Thanks Tamar. I tried that no joy. Further looking into it seems the Copy command itself is not working. If there a VFP code somewhere that copies all the data from the first sheet and then paste, I could probably adapt for my usage.
>>
>
>I don't think I have anything written, but rather than copying the whole worksheet, try making copying just UsedRange. Actually, writing the example below, I see what you're doing wrong. Copy actually involves both a source and a destination. The parameter is the destination.
>
>
>* I'm going to assume you add some variables to reference things,
>* so you don't have to keep going back to the top level (which is slow
>* Here, loSheet is assumed to reference the worksheet you're interested in.
>* loNewSheet is assumed to reference the worksheet you're pasting to.
>
>loSheet.UsedRange.Copy(m.loNewSheet)
>
>
>Since I'm not testing this, it may not be exactly right, but should put you on the right track. Check out this page:
http://msdn.microsoft.com/en-us/library/office/ff837760%28v=office.15%29.aspx>
>Tamar
Thanks Tamar, my first try did not give me any joy but I will try again.
Kind regards
Bhavbhuti