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

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

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

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

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

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