1

I have never used Visual Basic before but could do with a pointer on where to begin.

I have 750 excel spreadsheets that contains various amounts of data of different types. The columns are always the same, but the number of data rows vary per spreadsheet. I need to extract data and put it into two new spreadsheets.

Obviously to do this 750 times manually would be a nightmare. I just want to run a script that can do it for me and thus thought of Visual Basic although i've never used it before.

My specific questions are:

  1. What type of command should i research that would allow me to copy data where the row number to start at varies (as data above varies in no of rows). There is a title before this new data - how can i get it to search for this title and then choose the row below?
  2. Would all my spreadsheets have to be in one folder so that the script goes through them all, or can i have some kind of folder structure in that folder too?
  3. Anyone recommend any good resources for me to get to grips with visual basic and grasp what i need to do?

thanks

Tom

Tom Chambers
  • 364
  • 2
  • 4
  • 17

1 Answers1

2

So the compilation task got easier with the introduction of MS PowerQuery. If you are using MS Excel 2013, you already have this. If no, you should download it and use the extension from MS.

The following guide outlines how to Using Power Query to Combine Data from Multiple Excel Files into One Table. This means that with Power Query (PQ), MS has taken and enabled easy aggregation using a few simple button clicks. PQ is a lightweight alternative to a lot of tasks that used to require VBA.

In this example, you will use PQ to point to an entire folder (750 should be no problem) worth of commonly formatted Excel files. The only limitation is that each data file should have a similarly named tab.

I won't repeat the details of the guide for how to do it, as it is in-depth and visual. But if you run into issues, get in touch.

Community
  • 1
  • 1
iamstrained
  • 164
  • 11
  • 1
    I think @iamstrained was trying to link here: http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/ – GuitarPicker Jul 14 '15 at 14:54
  • Thanks @GuitarPicker that's correct. Fixed the main post's link. – iamstrained Jul 14 '15 at 16:48
  • I actually need to get each input file to be split into two output files (with different bits of the inputs file data in each one) - is that also possible with this? – Tom Chambers Jul 15 '15 at 12:47
  • Why not use this method to simply aggregate all the files, the quickly reshape the columns into two separate files as you want? But yes, you could also use this method to: 1) extract the stuff you want, exclude the stuff you don't - export it to a file. 2) setup a second PQ pull that does the other set, excluding the first set of data and then spitting out only the other set. – iamstrained Jul 16 '15 at 14:47