Excel-open each file in folder and copy a range

Brazuca · May 8, 2008, 1:08am

Tried reading the MS website but even using their examples, something breaks. Please help.

I’m trying to open each of 400 files in a directory, then copying a range (a1:e90) into one workbook after the previous entry.


set csvPath to (path to desktop folder as Unicode text) & "paninsal:"

tell application "Microsoft Excel"
	set theWkbk to open workbook workbook file name csvPath & "fed.xls"
end tell


set myFolder to ((path to desktop as Unicode text) & "paninsal")
tell application "Finder" to set theFiles to (every file of folder myFolder whose name extension is "csv")
repeat with j from 1 to the count of theFiles
	tell application "Finder" to ¬
		set thisName to (name of item j of theFiles)
	set aFile to alias (csvPath & thisName)
	
	
	tell application "Microsoft Excel"
		
		open theWkbk
		set lastRowCellA to (get end range ("A" & (count rows)) direction toward the top)
		set rNext to get offset of lastRowCellA row offset 1
		
		open aFile
		copy range (range "a1:e90" of sheet "Sheet1" of aFile)
		
		tell sheet "Sheet1" of workbook theWkbk
			paste worksheet destination range rNext
		end tell
	end tell
	
	
	
	
end repeat

This fails with: “Microsoft Excel got an error: The object you are trying to access does not exist” and the following section of code highlighted: paste worksheet destination range rNext

I can’t figure it out.

e.salves · May 9, 2008, 5:37pm

Hi Brazuca,

I am a new Mac user and scripter. I was going through the forums looking for a way to write a script just like yours. I need to open several files copy selected ranges and paste these ranges into a new workbook. I came across your script and made some modifications. I solved the paste worksheet error, however not to my satisfaction, since I inserted the make new workbook into the repeat loop. This caused the script to run smoothly, however pasting the selected ranges into different workbooks.

In a nutshell: I did not really solve the problem, but you may be able to see where it is going wrong now!

Here is my script:

set txtPath to (path to desktop folder as Unicode text) & "ind 1:"

tell application "Microsoft Excel"
	activate
	set theWkbk to open workbook workbook file name txtPath & "08.03.24-ind1-C-1.txt"
end tell

set myFolder to ((path to desktop as Unicode text) & "ind 1:")
tell application "Finder" to set theFiles to (every file of folder myFolder whose name extension is "txt")
repeat with j from 1 to the count of theFiles
	tell application "Finder" to ¬
		set thisName to (name of item j of theFiles)
	set aFile to alias (txtPath & thisName)
	
	tell application "Microsoft Excel"
		
		open theWkbk
		set theRange to (get range ("b587:b2614"))
		set cNext to get offset of theRange column offset 1
		
		open aFile
		copy range (range "b587:b2614" of active sheet of aFile)
		make new workbook -- this makes a new workbook for each spectraselected
		tell sheet "Sheet1" of active workbook
			paste worksheet destination range "a1"
		end tell
	end tell
	
	
end repeat

I have a couple questions:

Is it possible to select the name of a file and copy it to the top of its selected range in the new workbook?
Can I make the script open the desired file, copy the selected range, paste it into the new workbook, then close the file before carrying on to the next one?

Kind regards, hope you can solve it!

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

Brazuca · May 11, 2008, 7:54pm

Hi e.salves,

Glad to see that this was helpful. I’m not very good at Applescript, so I hope one of the gurus here takes a look in this thread and has the inclination to fix some of the lame code I wrote.

A couple of comments on yours. I don’t think you need the block about the column offset. I was using it to paste the data from each file into the same file, under the last row. You don’t seem to need it at all since you are just selecting a fixed block of text from each file and pasting it into a new workbook at A1. Actually, is this what you want to do? Wouldn’t you end up with the same number of files?

Anyways, on your questions: I think you can set the contents of the clipboard with a block like this:

tell application "System Events"
  set the clipboard to ((stuff) as text)
end tell

You would probably change (stuff) to aFile and paste it at A1, then paste the rest of the data at A2 or something.

To close the workbook, I think you just add “close aFile” or close “theWkbk”

Looking again at your script, I think you (or I) are getting confused. I use the “fed.xls” as my destination workbook, where things get pasted into (and where the row offset matters). The aFile is the source, where I copy from. You seem to have them both as the same thing.

StefanK · May 11, 2008, 9:02pm

Hi,

try this, it copies also the name of the source file in front of the data,


set sourceFolder to (path to desktop folder as Unicode text) & "paninsal:"

tell application "Finder" to set theFiles to (every file of folder sourceFolder whose name extension is "csv") as alias list
tell application "Microsoft Excel" to set theWkbk to open workbook workbook file name sourceFolder & "fed.xls"

repeat with oneFile in theFiles
	set thisName to name of (info for oneFile)
	tell application "Microsoft Excel"
		open oneFile
		tell sheet 1 of theWkbk
			set range_value to value of used range
			if class of range_value is list then
				set firstUnusedCellA to (count range_value) + 1
			else
				set firstUnusedCellA to 2
			end if
			set value of cell 1 of row firstUnusedCellA to thisName
		end tell
		set destinationRange to "A" & (firstUnusedCellA + 1) & ":E" & (firstUnusedCellA + 90)
		copy range (range "A1:E90" of sheet "Sheet1" of workbook thisName) destination range destinationRange of sheet 1 of theWkbk
		close workbook thisName saving no
	end tell
end repeat

Brazuca · May 12, 2008, 1:50am

Thanks Stefan! Works like a charm and, as I suspected, it is a lot more elegant and sophisticated than what I was playing with. I had actually started to try to enumerate a row because I was running into trouble finding the last cell (there were blank cells in the range that were throwing me off).

I’ve added a couple of lines to turn off screen updating during the script, but what you wrote just saved me a ton of time copying 400+ worksheets (it’s actually working on the background right now).

I’m starting to learn Python and hopefully this will give me a good push towards Applescript. I’m an economist but I’m really finding that having this as a toolset is a great advantage.

Thanks again.

edit: one problem here: It doesn’t seem to close the thisName workbook. The script opened about 113 files then Excel apparently wouldn’t open any more. From what I see, the line “close workbook thisName saving no” should do the trick, but just watching the script run tells me that it isn’t working.

StefanK · May 12, 2008, 7:15am

thisname is the name of the current source file.
To close the destination file, add this line at the end of the script


tell application "Microsoft Excel" to close theWkbk saving yes

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

Thank you very much Brazuca and StefanK!

The help you provided was tremendous and solved my arduous problem of copying hundreds of sheets!

Brazuca, you can see in another post http://bbs.macscripter.net/viewtopic.php?pid=99059#p99059 I wrote that StefanK has written a different script for me that solves our problems.

Cheers mates!