lalitfrom

Q: 1700 error when try to get/read value from a xlxs

Hi,

I am a beginner and trying to write an AppleScript that will read the cell value from an xlsx file located in document folder. I am storing the values (username and password) in an Excel sheet and will pass the values to login into skype.


On my 1st step of the scripting, while retriving the value got 1700 error. Here is my script

Here is my code


tell application "Numbers"

  set f to POSIX file "/Users/admin/Documents/DDSheet1.xlsx"

  set x to value of range ("A2:A4") of Sheet1 of f as string

display dialog x

end tell


This is the error message

 

tell application "Numbers"

get value of range "A2:A4" of Sheet1 of file "Macintosh HD:Users:admin:Documents:DDSheet1.xlsx"

  --> error number -1700 from Sheet1 of file "Macintosh HD:Users:admin:Documents:DDSheet1.xlsx" to specifier

Result:

error "Can’t make «class NMCv» of «class NmCR» \"A2:A4\" of Sheet1 of file \"Macintosh HD:Users:admin:Documents:DDSheet1.xlsx\" into type string." number -1700 from «class NMCv» of «class NmCR» "A2:A4" of Sheet1 of file "Macintosh HD:Users:admin:Documents:DDSheet1.xlsx" to string

Mac mini, OS X Yosemite (10.10.2)

Posted on Apr 20, 2015 3:00 AM

Close

Q: 1700 error when try to get/read value from a xlxs

  • All replies
  • Helpful answers

  • by Jacques Rioux,Solvedanswer

    Jacques Rioux Jacques Rioux Apr 21, 2015 11:53 PM in response to lalitfrom
    Level 4 (3,418 points)
    Mac OS X
    Apr 21, 2015 11:53 PM in response to lalitfrom

    Hi,

     

    You need to open the file in Numbers :

     

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as alias
    tell application "Numbers"
        launch
        set tDoc to open f
        tell tDoc
            set myList to value of cells of range "A2:A4" of table 1 of sheet 1
            close saving no
        end tell
    end tell
    displayDialog_list(myList)
    
    on displayDialog_list(l)
        set tid to text item delimiters
        set text item delimiters to return
        set t to l as text
        set text item delimiters to tid
        activate
        display dialog t
    end displayDialog_list
    

     

     

     

    If you have Excel :

     

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as text
    tell application "Microsoft Excel"
        launch
        set tdoc to open workbook workbook file name f with read only
        tell tdoc
            set myList to value of range "A2:A4" of sheet 1 -- or use the name of the sheet --> sheet "Sheet1"
            close
        end tell
    end tell
    displayDialog_list(myList)
    
    on displayDialog_list(l)
        set tid to text item delimiters
        set text item delimiters to return
        set t to l as text
        set text item delimiters to tid
        activate
        display dialog t
    end displayDialog_list
    
  • by lalitfrom,

    lalitfrom lalitfrom Apr 22, 2015 12:14 AM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 22, 2015 12:14 AM in response to Jacques Rioux

    Hi Jacques thanks for the reply, this solved my original problem. how to read the data

     

    "Open file in number" part is working fine but with Excel is is giving synatx error on workbook stating "Expected on of line , etc. but found identifier"

     

    Can you please little explain about below script part

     

     

    on displayDialog_list(l) 

        set tid to text item delimiters 

        set text item delimiters to return 

        set t to l as text 

        set text item delimiters to tid 

        activate 

        display dialog t 

    end displayDialog_list 


    Rather than showing, I am thinking to pass individual cell value to other application

    like A2 as username and B2 as password to third part application like skype


     

     

  • by Jacques Rioux,Helpful

    Jacques Rioux Jacques Rioux Apr 22, 2015 11:27 PM in response to lalitfrom
    Level 4 (3,418 points)
    Mac OS X
    Apr 22, 2015 11:27 PM in response to lalitfrom

    Hi,

     

    It is for that values are more readable in the dialog, the displayDialog_list() handler, use a return as separator to display three lines (one value per line).

    Example when the script get the values of range "A2:A4" :

    With Excel, myList contains {{"xxxx"}, {"yyyy"}, {"zzzz"}} (this list contains three lists which contain one item).

    With Numbers myList contains {"xxxx", "yyyy", "zzzz"} (this list contains three items).

    A dialog can display a string only, so you must convert a list to a string, like this --> display dialog (myList as string)

    But, if the text item delimiters is set to default, the dialog display "xxxxyyyyzzzz" (not very readable)



    lalitfrom wrote:

     

    Rather than showing, I am thinking to pass individual cell value to other application

    like A2 as username and B2 as password to third part application like skype

     

    To put one value into a variable use this script :

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as alias 
    tell application "Numbers"
        launch
        set tDoc to open f
        tell table 1 of sheet 1 of tDoc
            set tUserName to value of cell "A2"
            set tPassword to value of cell "B2"
        end tell
        close tDoc saving no
    end tell
    -- use tUserName and tPassword to do something with Skype
    
  • by lalitfrom,

    lalitfrom lalitfrom Apr 22, 2015 11:29 PM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 22, 2015 11:29 PM in response to Jacques Rioux

    Thank a lot Jacques, my problem solved . Now all is working fine.

  • by lalitfrom,

    lalitfrom lalitfrom Apr 24, 2015 2:38 AM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 24, 2015 2:38 AM in response to Jacques Rioux

    Hi,

    The script is running fine with 1st time, but giving error "Microsoft Excel got an error: Can’t continue open workbook." number -1708 on next run (2nd run).

    Can you please check what could be the reason.

    Here is the detail script


        

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as text -- featching data from excell sheet

    repeat with i from 2 to 3 -- code to repeat

    tell application "Microsoft Excel"

      launch

      set tdoc to open workbook workbook file name f with read only

      tell tdoc

      set myID to value of cell ("A" & i) of sheet 1

      set myPass to value of cell ("B" & i) of sheet 1

      close

      end tell

    end tell

     

    set Sid to myID as text -- storing the data as variable

    set Spassword to myPass as text

     

    tell application "Skype" --passing the data to skype

      activate

      tell application "System Events"

      delay 3

      keystroke Sid

      keystroke tab

      keystroke Spassword

      delay 1

      keystroke return

      end tell

    end tell

     

    delay 20

    tell application "Skype"

      quit

    end tell

     

    end repeat


  • by Jacques Rioux,

    Jacques Rioux Jacques Rioux Apr 24, 2015 7:24 AM in response to lalitfrom
    Level 4 (3,418 points)
    Mac OS X
    Apr 24, 2015 7:24 AM in response to lalitfrom

    Hi,

     

    lalitfrom wrote:

     

    Hi,

    The script is running fine with 1st time, but giving error "Microsoft Excel got an error: Can’t continue open workbook." number -1708 on next run (2nd run).

    Can you please check what could be the reason.

     

     

    I tested your script with more loop --> repeat with i from 2 to 100 -- code to repeat , it gives me no error.

     

     

    My Excel version is 14.4.5 on OS X 10.9.5

    Try to repair permissions with the "Disk Utility" application

     

     

    Otherwise try these workarounds.

    Workaround 1 :

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as alias  -- featching data from Excel sheet
    tell application "Microsoft Excel"
        launch
        set tdoc to open workbook workbook file name f with read only
        tell tdoc
            set myList to value of range "A2:B3" of sheet 1
            close
        end tell
    end tell
    
    repeat with thisList in myList -- code to repeat,  2 iteration because myList contains two sub-lists (one list per row)
        set Sid to (item 1 of thisList) as text -- value in cell A
        set Spassword to (item 2 of thisList) as text -- value in cell b
        
        tell application "Skype" to activate --passing the data to skype
        tell application "System Events"
            delay 3
            keystroke Sid
            keystroke tab
            keystroke Spassword
            delay 1
            keystroke return
        end tell
    
        delay 20
        quit application "Skype"
    end repeat
    

     

     

     

     

    Workaround 2 :

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as alias   -- featching data from Excel 
    tell application "Microsoft Excel"
        launch
        set tdoc to open workbook workbook file name f with read only
        tell tdoc
            repeat with i from 2 to 3 -- code to repeat
                set myID to value of cell ("A" & i) of sheet 1
                set myPass to value of cell ("B" & i) of sheet 1
                my skypeFunction(myID as text, myPass as text)
            end repeat
            close
        end tell
    end tell
    
    on skypeFunction(Sid, Spassword)
        tell application "Skype" to activate -- passing the data to skype
        tell application "System Events"
            delay 3
            keystroke Sid
            keystroke tab
            keystroke Spassword
            delay 1
            keystroke return
        end tell
      delay 20
      quit application "Skype"
    end skypeFunction
    
  • by lalitfrom,

    lalitfrom lalitfrom Apr 26, 2015 11:07 PM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 26, 2015 11:07 PM in response to Jacques Rioux

    Hi,

    My Excel version is 14.4.9 on OS X 10.10.2

    Both Workaround are not working, Getting same error message ""Microsoft Excel got an error: Can’t continue open workbook." number -1708" for the code "open workbook workbook file name f with read only"

     

    Can you please tell me how to "repair permissions with the "Disk Utility" application"?

  • by Jacques Rioux,

    Jacques Rioux Jacques Rioux Apr 27, 2015 6:12 AM in response to lalitfrom
    Level 4 (3,418 points)
    Mac OS X
    Apr 27, 2015 6:12 AM in response to lalitfrom

    Hi,

     

    I  pasted the wrong text in the first line of the workaround scripts

     

     

    Use this --> set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as text

    Instead of --> set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as alias

    lalitfrom wrote:

     

    Can you please tell me how to "repair permissions with the "Disk Utility" application"?

     

     

    Read this page https://support.apple.com/en-us/HT201560

    And this page  http://osxdaily.com/2015/01/13/repair-disk-permissions-mac-os-x/

     

     

    I updated my Microsoft Office, Excel 14.4.9, I try the script, no error here.

     

    Try this

    set f to "/Users/admin/Documents/DDSheet1.xlsx" as POSIX file as text  -- featching data from Excel sheet 
    repeat with i from 2 to 3 -- code to repeat
        tell application "Microsoft Excel"
            activate
            if not (exists workbook "DDSheet1.xlsx") then
                set tdoc to open workbook workbook file name f
            else
                set tdoc to workbook "DDSheet1.xlsx"
            end if
            tell tdoc
                set myID to value of cell ("A" & i) of sheet 1
                set myPass to value of cell ("B" & i) of sheet 1
                close saving no
            end tell
        end tell
        delay 1
    end repeat
    
    

     

     

    Also, try to re-create the workbook, and save it to overwrite the original file

  • by lalitfrom,

    lalitfrom lalitfrom Apr 28, 2015 12:12 AM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 28, 2015 12:12 AM in response to Jacques Rioux

    Hi,

    Still getting same issue.  "error "Microsoft Excel got an error: Can’t continue open workbook." number -1708" for this code open workbook workbook file name f

    1 . Repaired the Disk Permissions of Disk Utility.

    2. Re created the workbook.

     

    Here is the code

    - featching data from Excel Sheet

    set f to "/Users/admin/Documents/Few Test/MSExcel/DDSheet.xlsx" as POSIX file as text

    repeat with i from 2 to 3 -- code to repeat 

      tell application "Microsoft Excel"

      activate

      if not (exists workbook "DDSheet.xlsx") then

      set tdoc to open workbook workbook file name f

      else

      set tdoc to workbook "DDSheet.xlsx"

      end if

      tell tdoc

      set myID to value of cell ("A" & i) of sheet 1

      set myPass to value of cell ("B" & i) of sheet 1

      my skypeFunction(myID as text, myPass as text)

      close saving no

      end tell

      end tell

    delay 1

    end repeat

     

    on skypeFunction(Sid, Spassword)

      tell application "Skype" to activate -- passing the data to skype 

      tell application "System Events"

      delay 3

      keystroke Sid

      keystroke tab

      keystroke Spassword

      delay 1

      keystroke return

      end tell

     

    delay 20

    quit application "Skype"

     

     

    delay 3

    end skypeFunction

  • by Jacques Rioux,

    Jacques Rioux Jacques Rioux Apr 28, 2015 8:27 AM in response to lalitfrom
    Level 4 (3,418 points)
    Mac OS X
    Apr 28, 2015 8:27 AM in response to lalitfrom

    Hi,

     

    Maybe it's a Yosemite issue.

     

    Try the open command instead of open workbook command, like this

     

    set f to "/Users/admin/Documents/Few Test/MSExcel/DDSheet.xlsx" as POSIX file as alias
    repeat with i from 2 to 3 -- code to repeat
        tell application "Microsoft Excel"
            activate
            if not (exists workbook "DDSheet.xlsx") then
                open f
                repeat until exists workbook "DDSheet.xlsx"
                    delay 1
                end repeat
            end if
            set tdoc to workbook "DDSheet.xlsx"
            tell tdoc
                set myID to value of cell ("A" & i) of sheet 1
                set myPass to value of cell ("B" & i) of sheet 1
                my skypeFunction(myID as text, myPass as text)
                close saving no
            end tell
        end tell
        delay 1
    end repeat
    
    
  • by lalitfrom,

    lalitfrom lalitfrom Apr 29, 2015 12:28 AM in response to Jacques Rioux
    Level 1 (0 points)
    Apr 29, 2015 12:28 AM in response to Jacques Rioux

    Hi,

    The above script is working with workaround by opening the excel sheet manually with each run.

     

    and small change in the script:

    set f to "/Users/admin/Documents/Few Test/MSExcel/DDSheet.xlsx" as POSIX file as text