Combine multiple spreadsheets in survey format into one database

**ChrisSoulier** · 11-30-2011, 11:31 AM

Dear all,

I have a problem facing me, which I would have imagined to be quite a common problem but despite ruthlessly Googling for nearly an hour I have found no solution, so apologies if this has already been answered in another thread.

My problem is that I have inherited multiple excel spreadsheets that have been used as surveys, and I need to consolidate these into a database.

For example;
'Survey 00001.xls' has 30 different cells where users have input information. These input cells are not in a set structure throughout the individual surveys, e.g. the first two input cells are B5 and then E5, then the next 6 are B8, C8, D8, E8, F8, G8. And so on.

Aesthetically it's marvellously lain out however now that I am faced with the task of extrapolating all of these fields from all of the worksheets that I have into one database I am increasingly less impressed with the template.

The positive thing is that ALL of the documents that I need to process are in exactly the same format. I am using Excel 2003, and don't have access to Access.

Many thanks in advance, and I look forward to a lovely solution to my not so lovely problem!

Kind regards,

Chris

**Kyle123** · 11-30-2011, 11:37 AM

Can you post a sample workbook of the current layout and how you want it laid out in your db spreadsheet?

**watersev** · 11-30-2011, 11:39 AM

hi Chris, can you post couple of workbooks with surveys for consolidation and a master workbook with the result you need to obtain?

**ChrisSoulier** · 11-30-2011, 11:44 AM

Hi Kyle123, thanks for your swift repsonse.

Of course, I have attached examples of the survey form and the format of the export that I would like.

Is there anything else that you need me to provide?

**savio21** · 11-30-2011, 11:45 AM

Hi Chris,

I had a similiar problem and found Jerry Beaucaire's webiste to be extremely useful and the comments are easy to follow to modify to your liking.

https://sites.google.com/a/madrocket...s-to-one-sheet

**Kyle123** · 11-30-2011, 12:15 PM

give this a whirl, it goes in a standard module in the DB workbook.

To use it create a folder with all the surveys in and then change the path at the top of the module to the location that you have saved the surveys in. Make sure that only surveys are in the folder

Please Login or Register  to view this content.

HelpfulHarry1 · 11-30-2011, 01:09 PM

I have to agree with Roy.

please read the forum rules from now on.

HelpfulHarry1 · 11-30-2011, 01:10 PM

Oops, damn autofill. x

**ChrisSoulier** · 11-30-2011, 01:19 PM

Thanks Kyle123, but I am struggling to get this to work.

I have followed the instructions carefully and it doesn't seem to be doing anything at all.

Am I missing something?

**Kyle123** · 11-30-2011, 01:21 PM

Hi Chris, do you get an error at all? Also have you included the slash at the end of the directory?

**ChrisSoulier** · 12-01-2011, 04:53 AM

Hi Kyle,

Yes I have put a slash at the end of the directory path, the error message reads;

"'SurveyABC123.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct."

Thanks

*Edit* - When I click debug it is highlighting 'With Workbooks.Open(MyFile)' in yellow.

**snb** · 12-01-2011, 05:24 AM

Please Login or Register  to view this content.

The only thing you have to adapt is the path.
Always end the path with a backslash.

**Kyle123** · 12-01-2011, 05:38 AM

@snb nice code but a few typos

If you hover over MyFile while it is highlighted, it will give you the full filepath. See if you can open this file, this is a bit odd, I can't think of why you'd be getting this error

**snb** · 12-01-2011, 05:46 AM

@kyle123

Which typos ?

**ChrisSoulier** · 12-01-2011, 05:50 AM

Hi Kyle,

When I hover over 'FolderPath' it gives me the correct directory, but when I hover over 'MyFile' it just gives me the filename rather than the directory+filename.

One of my objectives of posting the question was not just to get a solution, but to understand the code behind the solution. I know that it's extra work for you, but would you be able to explain the code a little so that I can adapt it if needs be. I would be very appreciative and it might help me to make it work.

Thanks in advance.

**Kyle123** · 12-01-2011, 05:56 AM

@snb, you fixed the .End and:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

**Kyle123** · 12-01-2011, 06:00 AM

Try this:

Please Login or Register  to view this content.

If you want a good description of the Dir() function, have a look here http://www.hobbub.com/vba-vb-vsto/it...-directory-vb/

snb's concise code works in a similar way, is faster, but slightly less flexible as it relies on the data being structured in a specific way.

**watersev** · 12-01-2011, 07:06 AM

hi, ChrisSoulier, please check attachment, run code "test"

**snb** · 12-01-2011, 07:23 AM

@Kyle123

Thanks for 'close reading'. I restored my previous code.
I think the main advatage of my approach is that the result will be written into the worksheet in 1 go, reducing writing processes to the minimum.

@watersev

Can you do us mortals the favour of posting the code in your reply, apart from the attachment ? So we will be able to assess your approach at first glance without having to open the attachment.

**ChrisSoulier** · 12-01-2011, 07:51 AM

@Kyle123 - I've got it to work for the examples that I sent across, (exciting stuff!) now I need to adapt it to my problem. I couldn't send across the exact templates I was using because of company policy conflicts etc. (red tape nonsense). But with your notes this should be fairly straight forward for me to do. Thank you very much for your solution.

@snb - Thanks for posting a solution, I will be trialling your solution as well, disecting the code and trying to understand it all!

@watersev - Thanks for posting a response, I will be having a look at the code provided and having a play with it.

Thanks to everyone, it has been a warm welcome to a new forum. I will be testing the spreadsheets and as soon as I have a result, I will tag this thread as solved and relay my appreciation through reputation!

**Kyle123** · 12-01-2011, 07:59 AM

Glad it's working

Only thing to be aware of is to increase the size of the array and loop to cope with the number of fields you are importing

**ChrisSoulier** · 12-02-2011, 06:59 AM

Thanks Kyle123, I've got almost all of it working. Thanks for providing your notes I wouldn't have been able to do it without them.

There is one last thing that doesn't seem to be working, one of the cells that I am trying to copy from is throwing up this error message; "Run-time error '1004': Application-defined or object-defined error".

Then the debugger is highlighting the following line of code;

Please Login or Register  to view this content.

The only difference between this cell and all the other cells, is that within this cell, a lot of the information has been split with carriage returns within the cell (e.g. by pressing ALT+ENTER). If you were to copy it from within the cell and paste it normally then it would split it onto several rows. The way of getting around this manually is to enter the target cell and then paste. Is this something that can be incorporated?

Does that all make sense? I can upload examples of what I mean if necessary.

Thanks in advance.

**Kyle123** · 12-02-2011, 07:01 AM

If you could upload an example, that would be great

**ChrisSoulier** · 12-02-2011, 08:13 AM

Sorted it, some of the surveys had been saved as 2007.

I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Seems to have worked.

Thanks everyone for all of your help on this. Especially Kyle123.

**Kyle123** · 12-02-2011, 08:46 AM

Glad you've got it sorted. Please mark your thread as solved

**feistyrebel** · 05-10-2013, 06:39 PM

Originally Posted by Kyle123

Try this:

Please Login or Register  to view this content.

If you want a good description of the Dir() function, have a look here http://www.hobbub.com/vba-vb-vsto/it...-directory-vb/

snb's concise code works in a similar way, is faster, but slightly less flexible as it relies on the data being structured in a specific way.

Kyle,

I was going through this thread and tried using the code, but I keep getting an error. I TRULY NEED YOUR HELP!

I attached the 1 survey and 1 database spreadsheet. I will be receiving about 100 of these surveys (there will be more questions added) and want to be able to combine all of the answers on to the database.

Can you assist? I can't seem to get it to work... Sorry I'm a Newbie when it comes to VBA.

Thanks,

Valeri

**protonLeah** · 05-11-2013, 06:39 PM

feistyrebel, Welcome to the Forum.

1. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

2. Your post does not comply with Rule 12 of our Forum RULES. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Combine multiple spreadsheets in survey format into one database

LinkBack

Thread Tools

Rate This Thread

Display

Combine multiple spreadsheets in survey format into one database

Re: Possible VBA trickery required

Re: Possible VBA trickery required

Re: Possible VBA trickery required

Re: Possible VBA trickery required

Re: Possible VBA trickery required

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Re: Combine multiple spreadsheets in survey format into one database

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions