Re: Excel insanity
Re: Excel insanity
- Subject: Re: Excel insanity
- From: David Crowe via AppleScript-Users <email@hidden>
- Date: Fri, 27 Mar 2020 11:12:27 -0600
Yvan;
If I wrap with “tell FoundRange” then the code silently fails (while working
without this, if the correct worksheet is at the front).
I did discover that if I open the Excel file it brings it to the front, even if
the file is already open, so that is somewhat protective from accessing the
wrong sheet.
- David Crowe
>
> Message: 2
> Date: Fri, 27 Mar 2020 11:07:42 +0100
> From: Yvan KOENIG <email@hidden <mailto:email@hidden>>
> To: liste AppleScript US <email@hidden
> <mailto:email@hidden>>
> Subject: Re: Excel insanity
> Message-ID: <email@hidden
> <email@hidden">mailto:email@hidden>>
> Content-Type: text/plain; charset="utf-8"
>
>
>
>> Le 27 mars 2020 ? 03:39, David Crowe via AppleScript-Users
>> <email@hidden
>> <mailto:email@hidden>> a ?crit :
>>
>> I am trying to insert some cells into an Excel spreadsheet.
>>
>>
>> The following code works if my spreadsheet and worksheet are at the front:
>>
>> tell application "Microsoft Excel"
>> tell workbook "Update Email List.xlsx"
>> tell worksheet "TIM Emails"
>> set searchRange to range ("C2:C9999")
>> try
>> set foundRange to find searchRange what
>> theEmailAddress
>> -- If the email address is not present an error
>> will mean that the following code is not executed
>> tell me
>> activate
>> display dialog "Email address <" &
>> theEmailAddress & "> already in file" giving up after 3
>> return
>> end tell
>> end try
>> -- Find first blank row
>> set blank to ""
>> set searchRange to range ("C2:C9999")
>> set foundRange to find searchRange what blank
>> set theRow to first row index of foundRange as text
>> set value of cell ("A" & theRow) to FirstName
>> set value of cell ("B" & theRow) to LastName
>> set value of cell ("C" & theRow) to theEmailAddress
>> set value of cell ("D" & theRow) to DateString
>> set value of cell ("E" & theRow) to "Email"
>> end tell
>> end tell
>> end tell
>>
>>
>>
>> Otherwise, it attempts to modify whatever sheet is at the front. It is if
>> the ?tell workbook? and ?tell worksheet? weren?t present.
>>
>> If I put ?TIM Emails!? in front of every address I get an error (although I
>> have another script for which this works, see belowu ).
>>
>> So this following code works (and uses the right sheet as long as the sheet
>> name is put in every range specification):
>>
>> -- Account Information
>> tell application "Microsoft Excel"
>> tell workbook "CBRS ID Assignments.xlsx"
>> -- Account Information
>> tell worksheet "Account"
>> set searchRange to range ("Account!B1:B9999")
>> try
>> set foundRange to find searchRange what
>> AccountName
>> on error errmsg number errnum
>> tell me
>> display dialog "Cannot find account
>> containing: " & AccountName
>> return ("")
>> end tell
>> end try
>> etc etc
>>
>>
>> - David Crowe
>
> I dislike Merdosoft but from my point of view there is no insanity here.
> Your instruction :
>> set value of cell ("A" & theRow) to FirstName
> is executed as
> set value of cell ("A8") to FirstName
> so the target isn't defined.
>
> try to insert two new instructions:
>
>
> tell foundRange -- ADDED
> set value of cell ("A" & theRow) to FirstName
> set value of cell ("B" & theRow) to LastName
> set value of cell ("C" & theRow) to theEmailAddress
> set value of cell ("D" & theRow) to DateString
> set value of cell ("E" & theRow) to "Email"
> end tell -- ADDED
>
> With them, I guess that the script will know what is the real target.
>
>
> Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France)
> vendredi 27 mars 2020 11:06:55
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden