PDA

View Full Version : excel vba needed



corsicanoo
08-28-2015, 03:37 PM
Hello!
I am looking for a macro that will read an excel data table. Every data row in the excel doc contains specific details of a single individual object and has to be copied in an autogenerated individual excel template provided below, and saved to an autogenerated folder.


Eg:

Excel file

14280


Location to be saved:
\root\(cell B1)\(cell C1)\"(cell B1)-(cell C1).xls"


After execution of macro there should be generated all the excel files corresponding to every row, saved in the generated folders according to the name of the object

I would be very grateful if anyone could help me with this.


PS: all the X letters in the result file (template output) are irrelevant text that i will have to complete, only the cells with yellow background are important.

SamT
08-28-2015, 05:59 PM
has to be copied in an autogenerated individual excel template
I hope you mean autofilled and not autogenerated.
What is the name and location of the existing Template file?
What is the name and location the Input file?


Assuming that you intend to fill in the X'es before saving the Template, I suggest that the Template be saved as an actual Excel Template, (*.xlt) and that all the code be in a Master Workbook.

This code would open the input file (we can code to let you browse for it,) then open the Template for each line, create a record of that line number and whether or not the template was saved before completion. This would allow you to interrupt your work and come back to it later.

The MasterBook would have a sheet with several buttons on it; "Start,""Save this Template for later," This Row is Completed," "Save This input File for Later," "Done."

Since this is a work for free project and it entails a large amount of very easy, bur repetitive and tedious code, You can write this part for us.

Since we cannot upload *.txt files I changed the name, (Corsicanoo_ Tedious Coding.txt,) to a *.zip type, (Corsicanoo_ Tedious Coding.zip.) Please rename it to a *'txt type. You will have to rename it back to a *.zip type to upload the completed file.

Please only use NotePad to edit the file. Do not use Word.

In the attached file, Corsicanoo_ Tedious Coding.jpg,) I have started the first four cells on sheet "1.34" to provide you with examples on how to write each line. Note that "Input" and "Rw" are variables we will provide the values for. Do not change those two names. You will notice that I always used 3 X'es to indicate where you will be filling in the template. Please only use 3 X'es no matter how many spaces you need. Notice that I did include space characters in the XXX string where you showed spaces on the Sheet. (See Range D11 vs Range E11.) You will have to handle any discrepancies that occur when you are manually completing the sheet.

That is the tedious part, the painstaking part is insuring that all dots and quotes are correct.

corsicanoo
08-29-2015, 05:05 AM
The code im looking for is something similar to this

/watch?v=I9fXxhaubqA in youtube

but also the code should create folders and subfolders where the files have to be saved.

eg: i run a macro inside the master workbook, choose the input file (containing the data rows), choose the location where the output files should be saved and the macro will create inside the folder i chose the directory tree (\selected location\"column B folder"\"column C folder"\"column B - column C.xls" and inside will save the result files.

If thats what you had in mind too ill start working, but i have to say it first, my vba level is under basics hehe.

SamT
08-29-2015, 06:36 AM
i run a macro inside the master workbook, choose the input file (containing the data rows), choose the location where the output files should be saved and the macro will create inside the folder i chose the directory tree (\selected location\"column B folder"\"column C folder"\"column B - column C.xls" and inside will save the result files.
Exactly.

However, if the Root of the Save tree is fixed, we can code that in and if the location of the Template is also fixed, we can code that in. We can also use an Options Sheet where you can easily change those locations.

But, do you intend to complete the output file before it is saved or will you be opening them later from the saved locations to complete?



i have to say it first, my vba level is under basics hehe.
The part I asked you to do requires no knowledge of VBA. It is closer to having a 12yo write "I will be a good student" 100 times on the chalkboard. It is just tedious. And Boring. And painstakingly detail oriented.

Look at a Yellow cell, note the pattern of X'es and Input Range(s), copy the appropriate line from the ones I provided as examples and edit the Input Range letters.

For your curiosity, this is the rest of the "macro" that your work will complete:

Sub TransferData(Template As Workbook, Input As Worksheet)
With Template
'Your work here
End with
End Sub
The reason for my madness is that Change Happens. It is the one constant in this universe. I will include simple instructions in the Master Book about How To find and edit your work when Change Happens to the Template and/or the Input.

corsicanoo
08-29-2015, 09:00 AM
ok, i attached the docs needed, hope i understood what i had to do and also the template, which now is final. i do not intend to modify the files after the macro procs, maybe just few minor changes in the template in the future.
the original template extension is xltm.

SamT
08-29-2015, 10:21 AM
Excellent work on that "Tedious" file.

SamT
08-29-2015, 01:32 PM
Please tell me the real name on the Template Workbook and the real name of the Inputs workbook.

corsicanoo
08-29-2015, 03:20 PM
I am fine with whatever you call them, the only names that are relevant are the output ones.

SamT
08-29-2015, 03:39 PM
If there is a problem with revealing the name and location of the existing Template file and the name and location the Input file, just tell me.
I have asked several times and you avoid answering.

Don't feed me BS like I am fine with whatever you call them or you will be finding someone else to do this work.

corsicanoo
08-30-2015, 01:43 AM
Well there is no avoiding, thing is that for every project the name of the input file will be different, like "evidenta <city name>" so i will have to rename it every time to something like "evidenta.xls" and the location for the input is always in dropbox in a folder named after the name of the project so everytime is different. So i will always have to manually rename the input to "evidenta.xls" and copy somewhere in my PC in a fixed location like "D:\excel macro\". The template also will be in the same location "D:\excel macro\" and its called "formulare.xltm".
Hope you see now why i let you do the naming, because however i call them here, it will never be the actual name and i will have to rename them every time.

SamT
08-30-2015, 07:14 AM
Corsicanoo,

I apologise for my previous outburst. I am under a bit of stress that has nothing to do with you.


for every project the name of the input file will be different, ... the location for the input ... everytime is different. ... The template also will be in the same location "D:\excel macro\" and its called "formulare.xltm".

Thank you for that information. It is those kind of details that are required for a good program.

Contritely yours,
SamT

corsicanoo
08-30-2015, 09:03 AM
Dear Mr. SamT,

There is no need to apologize, I am the one here asking for help which i received with no charges. So for that I am grateful. Regarding your previous outburst, well...it happened to all of us at least a dozen times, so I am not the one to judge. Just take it easy, everything will be ok eventually.

Also im impatiently waiting for the macro to test hehe. :bat2:

waterluxe
08-30-2015, 11:28 AM
good question .. thank you.

corsicanoo
09-05-2015, 03:28 AM
so any luck getting it work?

SamT
09-05-2015, 10:17 AM
It's about half complete. I know it is taking longer than either of us would like, but I do the best I can.

corsicanoo
09-06-2015, 01:11 AM
ok. if i can help anymore just tell me

corsicanoo
09-21-2015, 11:31 AM
still unsolved. looking for some directions about creating folders using excel.

SamT
09-21-2015, 04:04 PM
Corsicanoo,

I am very sorry. I am extremely busy and on'y visit VBAExpress during my breaks. Then, I only have time for very short and easy problems.
I am attaching the work I have done for anybody to finish.

It still needs a sub to open the input and template
A sub to loop through the input and set the Save to Folder Variables and Save As Variable, and a sub to save and close the template and open a new template.

To whom it may concern.
This project is designed so that Corsicanoo can modify it by copying and very sklightly editing the Button Click sub, then merely creating a new Template and a new transfer data sub as in modDataTransfers.

Almost values are transferred by Public variables, most of which are set by the button click code. I will be able for consultation thru this thread during my breaks.

I wish I had time to complete the project.

Melissa123
09-24-2015, 10:06 PM
Thanks for sharing such a nice information......