General information
Title:
Import From with Excel 2007
Environment versions
Network:
Windows 2003 Server
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only