Building Workbook w/ VBA (I think?)

[gonzjt]

Hi everyone,

First off, thank you in advance to anyone that can assist me with this. I think this is fairly complicated, and that I need a VBA macro in order to achieve what I am looking for... but I have no idea how to write it.

I have one main document - a schedule - that I want to be completed with information from a number of other documents. All of these other documents have the same structure - basically a template is used that I created.

I want a script that will run and pull information from certain cells from each document within a set directory and populate set cells in the main schedule. Each document will have it's information inputted onto a separate row of this main schedule.

For instance - in the main Event Schedule doc - we have each column A4:I4, Date, Name, Time, etc.... These cells should be populated with the information from BEO-01. Then the script will open doc BEO-02 and populate the next row in the schedule - A5:I5 - and so on. The script should end when it has completed all of the BEO-## documents in the directory.

Is this even possible???



Thank you soooooooooooooooo very much for any information you can throw my way to make this happen.

p.s.: I hope this is possible

[Domski]

Hi,

So you want to loop through the files in a folder and pull certain information from each one?

If so that should be pretty straightforward to achieve.

Are you able to provide a sample of the source and destination workbooks?

Dom

[gonzjt]

Hi Dom,

Yes, I guess that is what I am looking to do. I have attached the schedule document that I am working with that should be populating with the information from the other "templated" documents that live in the same directory.

The BEO-01 is one of the "templated" documents that the information will be pulled from.

You can see in the event schedule which cells I am looking to pull information from the BEO docs. I started to setup this schedule doc to pull the information manually with formulas but this is going to leave too much room for error.

Thanks again for your time and let me know if you need any other info!! You are a life saver!!!


- Justin

[Domski]

Maybe something like this:

Please Login or Register  to view this content.

It will look through all the files in the specified directory and pick up the details from any file starting with Contract-BEO.

Dom

[gonzjt]

I am going to give this a try now.

So excited!

Thanks Dom!!!! I will let you know how it works out.


j.

[gonzjt]

Hi Dom,

I just gave it a try... updated the directory to mine... but nothing seems to happen. I looked through the code and (in my limited knowledge) I think something is missing. From each of these BEO documents there are 2 tabs, Contract / BEO. It needs to pull the information specifically from the BEO tab, but I don't see any reference in the script to call from that tab.

Is that what is missing?

Thanks again!

[gonzjt]

Oh, sorry, nevermind. I see the code for the tabs. I overlooked it. Must be something else. Any ideas?

Just to recap what I did... I took your script and entered it as a macro in the schedule document. That was correct... right?

[Domski]

That would be correct. I'm not in front of my pc now and out for evening. Can you post the code you've got now?

Dom

[gonzjt]

I modified it a bit because it looked like you were pulling information from the contract sheet, when all should be pull from the BEO sheet (within) the BEO document. Note, that I did try your code first before modifying it... again with no result.

Please Login or Register  to view this content.

Also, you should know is that I cleared all cells of any information or formulas that were in the schedule document. I did try running the Macro both before and after I did that though.


Thanks again!


j.

[Domski]

You need to add a \ at the end of your file path.

Please Login or Register  to view this content.

Dom

[gonzjt]

Yeah, I already tried that... but still, nada.

[Domski]

I just tried it with your code and it works absolutely fine importing data from Excel files with the name beginning with Contract-BEO.

Can you see it opening any files?

Dom

[gonzjt]

No, it doesn't open any files. My documents are named BEO-01.xls, BEO-02.xls, etc.... Does that have something to do with it?

[gonzjt]

Oh derr... let me rename to Contract-BEO... duhh!! I can't believe I missed that. I am sure it will work now... Let me give it a try.

[gonzjt]

Oh, duh! That opened all of the documents but nothing copied over... but of course that is with my modified code. Going back to your original code now.

[gonzjt]

Ok. So with both your original code (except for the directory and schedule tab name change) and my modified code... it runs, I see it opening each document, but nothing copies over.

[Domski]

And the files definitely have worksheets called Contract and BEO in them with data to copy? As I said it works fine for me with the sample that you posted.

Dom

[gonzjt]

So I just found something interesting... again something I did different and did not mention.

The original schedule document I uploaded for you to check had a bunch of manual formulas I entered in order to achieve what we are doing with the macro you wrote. Well, thinking that the macro will take care of this I actually deleted all of the manual formulas I wrote and tried the macro in that empty document (just with the layout... borders, etc.).

Anyhow, the macro was not working in that. Saw that it was opening the documents but not transferring the data from the Contract-BEO documents.

Well I just went back to my original schedule document with my manual formulas... and waalaa, the macro works!

Honestly, not sure why this is. Now that this macro is working I don't need all of those formulas. The macro was meant to replace that so the document is clean. How can I get it to work with a clean template (without my formulas from the original schedule I sent)?

Thanks again for everything!!!


j.

[Domski]

Can you upload the actual document that you want to pick the info up from without formula etc and clarify where each item should be picked up from.

Dom

[gonzjt]

It is just modified version of the original schedule. Here it is, minus the formulas I had there originally, and with your macro. For some reason when I cleared all of the cells of the formulas the macro stopped working. If I copy the formulas (from my backup original schedule) the macro works. All of the data transferred by the macro will end up at the bottom of everything I did.

Thanks! You are the best!!

[gonzjt]

Here is the original schedule document I posted. The (same exact) macro works in this document, but not the clean one.


j.

[gonzjt]

Here is a version of the Contract-BEO document that the information is being pulled from for the schedule.


j.

[Domski]

I hadn't spotted you'd got merged cells in your headings which was confusing the code when it tried to locate the row to populate the data with (VBA hates merged cells).

The layout should work.

Dom

[gonzjt]

Yes, yes, YES!!

That worked perfectly!

So what did you do to get it to work with the merged cells? Or did you just modify the cells? It didn't look like it.


Thanks again!!!!!!


j.

[Domski]

I modified the layout to get rid of them, evil things

Dom