Hi,
I’m trying to create an array but I need to use variables as I use dialog to enter limits of this array.
… but I can’t get this to work :
repeat
display dialog "enter column B" default answer ""
set colB to text returned of result
if colB ≠"" then exit repeat
end repeat
repeat
display dialog "enter column C" default answer ""
set colC to text returned of result
if colC ≠"" then exit repeat
end repeat
set colBC to "(B & colB & : & C & colC)" -- to get say "B300:C306"
tell application "Microsoft Excel"
tell active sheet
set MyArray to get value of (get range value of colBC)
end tell
end tell
Is it a syntax or a concept problem ?
Thanks for your help
Phil
You’re close but you have the variables colB and colC inside the quotes, which makes them just text instead of a variable. So change that line to this…
set colBC to "B" & colB & ":C" & colC
Hi Regulus6633,
Thanks for your response. You’re right ; it works now…
But now that all my program (my first one) works fine, I’m getting into a new small problem : when it runs, it makes dozens and dozens of loops. I think that the fact I concatenate small parts of scripts (thanks to this forum) is not the right way to structure a program, but for the moment, I’m just learning.
I suspect the “repeat with N from 1 to doccount” loop to be redundant but if I delete it, nothing works.
I’m not quite familiar with loops, so I can’t find where the problem is (but it works anyway :°)
Could someone have a small look at this, please ?
set targettexteditor to "BBEdit"
set doclst to {}
set sp to " "
repeat
display dialog "enter B" default answer ""
set colB to text returned of result as Unicode text
if colB ≠"" then exit repeat
end repeat
repeat
display dialog "enter C" default answer ""
set colC to text returned of result as Unicode text
if colC ≠"" then exit repeat
end repeat
set colAA to "A" & colB & ":A" & colC as Unicode text -- sets the limit in the first column
set colBC to "B" & colB & ":C" & colC as Unicode text -- set the limits of array
tell application "Microsoft Excel"
tell active sheet
set FileArray to get value of (get range colAA) -- gets names of files in the first column
set DataArray to get value of (get range colBC) -- gets values to replace placeholders in text file
end tell
end tell
tell application targettexteditor
activate
set doclst to name of every document
set doccount to count of doclst
--////////////////////////////////////////////////////////////////////////////////////////////////
repeat with F from 1 to count of FileArray -- loop the filenames list
repeat with D from 1 to doccount -- loop for opened documents
if (item D of doclst) contains (item F of FileArray) then -- if filename matches document name...
--//// replacement loop/////////////////////////////////////////////////////////////////////////////
repeat with N from 1 to doccount -- seems to be useless but can't work without this one ?!!
set targdoc to document F
select targdoc -- select the found file
set search_strings to {"varTitle", "varComment"} -- defines variables as list
set replace_strings to {}
set flattenedDataArray to item D of DataArray -- removes the outer list
repeat with N from 1 to 2 -- if 2 placeholders
set end of search_strings to {"varTitle", "varComment"} -- search "varTitle", "varComment" in text file
set end of replace_strings to (item N of flattenedDataArray) -- set replacement values to "value of B1","value of C1"...
end repeat
set x to 1
repeat with x from 1 to 2 -- for 2 replacements
tell application "BBEdit"'s document F
replace (search_strings's item x as Unicode text) using (replace_strings's item x as Unicode text) searching in it ¬
options {returning results:0} saving no
end tell
end repeat
end repeat
--//// end replacement loop////////////////////////////////////////////////////////////////////
end if
end repeat
end repeat
--////////////////////////////////////////////////////////////////////////////////////////////////
end tell
Thanks for your help
Phil