General Question

kfingerman's avatar

How can I automate this series of actions in excel?

Asked by kfingerman (992points) June 1st, 2010

I’m pretty Excel savvy, but have hit the edge of my abilities with this problem. I have a series of actions that I need to automate and am not sure how. I’m running a Mac, but have Parallels and a windows virtual machine, so I can use either. I basically need to take every 12 line chunk of an excel sheet (they’re monthly data for hundreds of different sites) and save each chunk (site) into a separate file. The bigger problem is that these files need to be titled (could just be numerical order, could be named for a cell, etc). Can I do this using VBA? What about Applescript? Any ideas?

Observing members: 0 Composing members: 0

7 Answers

kfingerman's avatar

I just had one idea. There are two main problems here:
1) How to get the thing to identify successive 12-line sections of cells. Maybe if I use the cut function instead of the copy function it will remove the top 12 lines…then every time, it’s the (new) top 12 that it needs to cut?
2) How to give them successive names? Maybe I tell it to save them as, say, fluther.xlsx, but then there’s already a file called that…so if I tell it not to replace it will end up saving fluther.xlsx(1), fluther.xlsx(2), etc.
Both of these are inelegant, but might work. As you can tell, I’m a newbie at automation. Any better ideas?

6rant6's avatar

Create a column in the file that calculates the file name. You could copy the 12th line out to a new spreadsheet, or alternatively you could have a spreadsheet that points to a line (using relative position) according to a variable on the new sheet. That way you can cycle through the original and export the new one with the appropriate name.

jaytkay's avatar

Absolutely anything you can do in Excel can be automated in VBA.

I have a VBA macro which creates a new worksheet based on one column, I’ll mess with it to create new files instead.

As @6rant6 suggests, this will depend on a column with the file name. When the column value changes, a new file is created. Having the trigger in the original data makes it a lot easier to test & tweak later.

Anybody have a quick and easy place to post VBA code? Rather than Fluther I’d rather post the VBA to a VBA-centric site.

se_ven's avatar

I agree with others, you can definitely do this with VBA. The way I started doing VBA was to record a macro of the basic functionality I was trying to create and then look at the code. All you have to do from there is a little research online to find out how to input variables where you need them and how to do the functionality that isn’t quite picked up with the macros.

[Quick tip] you’ll probably use the “offset” function quite a bit to manipulate the cell location

jaytkay's avatar

@kfingerman I will be able to post some code later this evening.

kfingerman's avatar

Thanks for all the help people, the things I’m suspecting will be toughest are:

1) Getting it to recognize every 12 lines as a separate file – it won’t always be the same 12, just successive ones, so i can’t ask it to do the same set of actions over and over.

2) Actions that take place not in the spreadsheet but instead in a dialogue box (e.g. save as). I need to give each file a separate name (numerical would be fine), and I need to save them as .txt files.

6rant6's avatar

If you think it through, it’s probably not that difficult. You just need to find markers in the file. Let’s say for example, that the word “Elephant” in the first column begins the sequence of twelve. So you turn on macro capture, find “elephant, do 12 down arrows, do whatever you need to capture all the columns, export this (supplying the name). Now if you run this macro again, you will start at the bottom of the first set, skip down to the next elephant, etc.

In the macro, you can replace the literal name you used with a prompt. I’m not certain, but I think you should be able to use a variable (from a named range) to set the file name.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther