Check Re: Excel 2007 Thread #
1413572 Message #
1413574>Hi All,
>
>I know this is a known issue, I just can't seem to find the fix. I've been hunting the net and come up empty. I have a standard .prg file that I add to any of our applications when I need to import data from Excel. Below is all the code that I use to convert the file into a cursor.
>
>pcXlsFile is the name & path of the Excel file that is passed to this procedure
>
>#Define END_DOWN -4121
>#Define END_RIGHT -4161
>
>* Store the current SAFETY setting of the program.
>lcSafety = SET("SAFETY")
>* Turn off the SAFETY to avoid VFP validation.
>NOTE: Will NOT avoid 'Excel' validation. ( Done just below )
>SET SAFETY OFF
>* This will be the name of the Temp 'XLS' file that will be created
>loTempExcelFile = "C:\temp\NewImport.xls"
>* Create an instance of EXCEL.
>loExcelApp = CREATEOBJECT("excel.application")
>* This will turn off the EXCEL 'SAFETY' messages.
>loExcelApp.DisplayAlerts = .F.
>* Open the file that the user selected for Import.
>loExcelWb = loExcelApp.Workbooks.OPEN(pcXlsFile)
>* Select the sheet
>loExcelSheet = loExcelWb.ActiveSheet
>* Select all the cells in the desired range.
>loExcelSheet.Range("A1", loExcelSheet.Range("A1").SpecialCells(11)).Select()
>* Copy all the info in the cells to the clipboard
>loExcelSheet.Range("A1", loExcelSheet.Range("A1").SpecialCells(11)).Copy()
>* Select the 1 cell
>loExcelSheet.Range("A1").Select
>* This will paste the VALUES of the previously selected cells back into themselves.
>loExcelSheet.Range("A1").PasteSpecial( -4163, -4142 )
>* Save the change done on the previous line
>loExcelWb.SAVEAS( loTempExcelFile )
>* Close the EXCEL file
>loExcelWb.CLOSE
>* Exit the Excel program
>loExcelApp.Quit && Important!!
>* Import the data from the temp Excel file created.
>
IMPORT FROM &loTempExcelFile TYPE XL8>* Release the variables used for the Excel objects.
>RELEASE loExcelSheet
>RELEASE loExcelWb
>RELEASE loExcelApp
>IF FILE('&loTempExcelFile')
> DELETE FILE '&loTempExcelFile'
>ENDIF
>
>It is of course the IMPORT FROM line where the error occurs. Could somebody please point me to where I can find the solution to my dilemma?
>
>TIA
>Mike
If it's not broken, fix it until it is.
My Blog