none
Excel To Cursor?

    Question

  • I want to create cursor from Excel File - particular sheet "abcd1". Is it possible from vfp?

    Thanks

    AJEETH KUMAR
    Tuesday, February 10, 2009 2:56 PM

Answers

  •  SET TALK OFF
    LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
        lcSQLCmd AS STRING, lnSuccess AS INTEGER, ; lcConnstr AS STRING
    CLEAR

    lcXLBook = GETFILE("xlsx")

    lcConnstr = [Driver=] + ;
        [{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
        [DBQ=] + lcXLBook

    IF !FILE( lcXLBook )
        ? [Excel file not found]
        RETURN .F.
    ENDIF
    lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
    ** change sheet_name with your sheet name
    lcSQLCmd = [Select * FROM "abcd1$"]
    lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
    ? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
    IF lnSuccess < 0
        LOCAL ARRAY laErr[1]
        AERROR( laErr )
        ? laErr(3)
        SQLDISCONNECT( lnSQLHand )
        RETURN .F.
    ENDIF


    SELECT xlResults
    browse normal
    SQLDISCONNECT( lnSQLHand )

    surinder singh
    • Marked as answer by AJEETH Monday, February 16, 2009 12:20 PM
    Tuesday, February 10, 2009 3:06 PM

All replies

  •  SET TALK OFF
    LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
        lcSQLCmd AS STRING, lnSuccess AS INTEGER, ; lcConnstr AS STRING
    CLEAR

    lcXLBook = GETFILE("xlsx")

    lcConnstr = [Driver=] + ;
        [{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
        [DBQ=] + lcXLBook

    IF !FILE( lcXLBook )
        ? [Excel file not found]
        RETURN .F.
    ENDIF
    lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
    ** change sheet_name with your sheet name
    lcSQLCmd = [Select * FROM "abcd1$"]
    lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
    ? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
    IF lnSuccess < 0
        LOCAL ARRAY laErr[1]
        AERROR( laErr )
        ? laErr(3)
        SQLDISCONNECT( lnSQLHand )
        RETURN .F.
    ENDIF


    SELECT xlResults
    browse normal
    SQLDISCONNECT( lnSQLHand )

    surinder singh
    • Marked as answer by AJEETH Monday, February 16, 2009 12:20 PM
    Tuesday, February 10, 2009 3:06 PM
  • Showing error , " it must be variable reference " at the place of

    browse normal



    AJEETH KUMAR
    Tuesday, February 10, 2009 4:04 PM
  • It should work.
     
    Is cursor xlResults created?
    if yes then you can use
    browse


    surinder singh
    Tuesday, February 10, 2009 4:17 PM
  •  Hi,

    Can you explain me ?

    To to this whether we need SQL server installation for every  machine?

    And I can able to see xlResults on status my bar, but it's not browsing....

    I checked Browse Normal & browse.


    SQL Cmd Success : Good!

    Showing Error

    Datasource for this object must be a variable reference.



    AJEETH KUMAR
    Wednesday, February 11, 2009 6:00 AM
  • you don't need SQL server anywhere for this.

    I am not able to get why are you getting this error on
    browse
    command. this is simple command.

    you can try by copying your cursor to some table if it helps you

    select xlResults
    copy to someTableName

    and then open this table and see if you are getting records in it.
    surinder singh
    Wednesday, February 11, 2009 10:51 AM
  • I tried. It's showing error !!!!!!

    Datasource for this object must be a variable reference.

    AJEETH KUMAR
    Wednesday, February 11, 2009 1:08 PM
  • IMPORT FROM "c:\myfile.xls" TYPE XL5 SHEET "sheet1"


    Computer and Informatics Engineer
    Wednesday, February 11, 2009 1:24 PM
  • IMPORT FROM "c:\myfile.xls" TYPE XL5 SHEET "sheet1"'

    that may not work in MS office 2007
    surinder singh
    Wednesday, February 11, 2009 2:11 PM
  • No. I don't want to use those Import , Append from...

    Need to be a user friendly. They can select 2007 or earlier....



    AJEETH KUMAR
    Wednesday, February 11, 2009 2:16 PM
  • So, we need to keep the top row as text for field names, then the problem could solve.


    AJEETH KUMAR
    Monday, February 16, 2009 12:20 PM
  • I used this code with no problem. However when in the runtime, all my field type, Character has been changed to memo and double.
    Do you know what are the best solution to this problem? Thank you.
    Monday, October 26, 2009 3:16 AM