Excel 2008 - Howto copy ranges from several files to new workbook?

e.salves · May 9, 2008, 9:07pm

I am new to applescript and am in need of help!

I need to copy a selected range from several different workbooks to a new workbook. The ranges need to be pasted in the same sheet, side by side in consecutive columns.

Also, is it possible to copy the file name for each selected range and paste it on the first cell of each column in the new workbook?

I have so far been able to come up with the following code (some of it borrowed from a code by StefanK):

set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS", "TXT"} with multiple selections allowed without invisibles)

tell application "Microsoft Excel"
	activate
	set targetBook to make new workbook
	repeat with oneFile in sourceFiles
		set sourceBook to open workbook workbook file name (oneFile as text)
		repeat with copy_range in (get range "b587:b2614" of active sheet of sourceBook)
			if value of used range of copy_range is not "" then
				copy range (copy_range of active sheet of sourceBook)
				tell sheet "Sheet1" of targetBook
					paste worksheet destination last sheet of targetBook
				end tell
			end if
		end repeat
		close sourceBook saving no
	end repeat
end tell

I run it (with no errors popping up). It looks like it is copying the selected range, however nothing is pasted in the new workbook!

Any help would be greatly appreciated!

Model: Macbook 2.4 GHz 2 GB
AppleScript: 2.0
Browser: Firefox 2.0.0.14
Operating System: Mac OS X (10.5)

StefanK · May 12, 2008, 9:03am

Hi,

if you have less than 26 source files, try this


property firstRow : 587
property lastRow : 2614

set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS", "TXT"} with multiple selections allowed without invisibles)
set firstUnusedColumn to 1
tell application "Microsoft Excel"
	-- activate
	set targetBook to make new workbook
	repeat with oneFile in sourceFiles
		set sourceBook to open workbook workbook file name (oneFile as text)
		set thisName to name of sourceBook
		set copy_range to range ("b" & firstRow & ":b" & lastRow) of active sheet of sourceBook
		if value of used range of copy_range is not "" then
			tell sheet 1 of targetBook
				set value of cell 1 of column firstUnusedColumn to thisName
				set currentColumn to ASCII character (64 + firstUnusedColumn)
				set destinationRange to range (currentColumn & "2:" & currentColumn & (lastRow - firstRow + 2))
			end tell
			copy range copy_range destination destinationRange
			set firstUnusedColumn to firstUnusedColumn + 1
		end if
		close sourceBook saving no
	end repeat
end tell

if you have more than 26, you have to calculate the double letters of the columns (AA, AB etc.),
unless there is an easier way to define a range.

e.salves · May 12, 2008, 12:36pm

Hi StefanK,

Thank you very much for the help! The script works flawlessly! It does exactly what I needed. But as you guessed I do have more than 26 files. And being new to both Macs and applescript I not up to date on this language to understand how to “calculate the double letters of the columns (AA, AB etc.),” as you mentioned in your post!

I will play with it today and see if I can solve this puzzle!

Many thanks and cheers!

StefanK · May 12, 2008, 3:30pm

If you look in Excel at the columns, the next column after letter Z is double letter AA, then AB etc.
Excel expects a range in the format A1:A15, so you have to coerce the column number of firstUnusedColumn to the letter equivalent.
I’m sure, there is a easier way to copy and paste without this range format, but this also does it


property firstRow : 587
property lastRow : 2614

set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS", "TXT"} with multiple selections allowed without invisibles)
set firstUnusedColumn to 1
tell application "Microsoft Excel"
	-- activate
	set targetBook to make new workbook
	repeat with oneFile in sourceFiles
		set sourceBook to open workbook workbook file name (oneFile as text)
		set thisName to name of sourceBook
		set copy_range to range ("b" & firstRow & ":b" & lastRow) of active sheet of sourceBook
		if value of used range of copy_range is not "" then
			tell sheet 1 of targetBook
				set value of cell 1 of column firstUnusedColumn to thisName
				set currentColumn to CalcColumnLetter(firstUnusedColumn)
				set destinationRange to range (currentColumn & "2:" & currentColumn & (lastRow - firstRow + 2))
			end tell
			copy range copy_range destination destinationRange
			set firstUnusedColumn to firstUnusedColumn + 1
		end if
		close sourceBook saving no
	end repeat
end tell

on CalcColumnLetter(theCol)
	set theCol to theCol - 1
	set H to theCol div 26
	if H > 0 then
		return (ASCII character (64 + H)) & (ASCII character (64 + (theCol mod 26) + 1))
	else
		return (ASCII character (64 + (theCol mod 26) + 1))
	end if
end CalcColumnLetter

e.salves · May 13, 2008, 2:19am

Stefan,

Once again, thank you for your help!

I tried to run the updated version of the script, allowing more than 26 columns. However, I think there is a an error in the script, that I am too newbie to understand!

When I run it the following error message is displayed “Microsoft Excel got an error: sheet 1 of workbook “Sheet1” doesn’t understand the CalcColumnLetter message.”. And the following part of the script is highlighted “CalcColumnLetter(firstUnusedColumn)”.

I tried playing with the command “get offset” to paste the next column to the right of the current column, this script is able to copy only the name of the file to the first cell, and for some unknown (to me) reason, not the content of the selected range:

property firstRow : 587
property lastRow : 2614

set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS", "TXT"} with multiple selections allowed without invisibles)

set firstUnusedColumn to 1

tell application "Microsoft Excel"
	activate
	set targetBook to make new workbook
	repeat with oneFile in sourceFiles
		set sourceBook to open workbook workbook file name (oneFile as text)
		set thisName to name of sourceBook
		set copy_range to range ("b" & firstRow & ":b" & lastRow) of active sheet of sourceBook
		if value of used range of copy_range is not "" then
			tell sheet 1 of targetBook
				set value of cell 1 of column firstUnusedColumn to thisName
				set destinationRange to (get offset range "a2" column offset 1)
			end tell
			copy range copy_range destination destinationRange
			set firstUnusedColumn to firstUnusedColumn + 1
		end if
		close sourceBook saving no
	end repeat
end tell

I am sorry to bother you so much with such a simple problem, but to beginner it is a challenging and fun problem!

Thank you once again!

Cheers

StefanK · May 13, 2008, 7:15am

Sorry, I forgot the keyword my, because the handler call is within an application tell block


.
 set currentColumn to my CalcColumnLetter(firstUnusedColumn)
.

Edit:
PS:

Thanks, Jacques, for posting the simple way to retrieve the range address of a cell

e.salves · May 13, 2008, 12:07pm

Thank you all very much for the help! Thank you Stefan for all the effort in helping out with this code, your last correction did the trick! And thank you Jacques for another way to getting to the same answer! Both scripts work flawlessly, and will help me get started with scripting!

Cheers,

Eduardo