Complete table from database

[M_Michael]

Hi everyone,

I'd like to do through VBA the following thing: fill-in data from a database to a template. The challenge here is that the columns names from the database and the template fields are not similar (and that cannot be changed unfortunately). I've then created a correspondance table:

Règles de remplissage
Catégorie formulaire -> Colonne base de données correspondante
Numéro d'étudiant -> [ID E]
Nom de l'étudiant -> [N.E] - [P.E]
Degré actuel -> [CL]
Corps d'étude -> [Spec]

Does anyone know how to make them correspond and fill in automatically? I did it few years ago BUT the names were the same but now I'm completely blocked...I attached the file.

A massive thank you to anyone who will provide me support !!

Jack

[Marc L]

As a beginner starter :

Sub Demo1() 
    Dim V 
    With Worksheets(2) 
        V = Split(.[E2].Value, " - ") 
        If UBound(V) <> 1 Then Beep: Exit Sub 
        V = Array(.[B2].Value, V(0), V(1), .[B4].Value, .[E4].Value) 
    End With 
    With Worksheets(1) 
        .Cells(.[A1].ListObject.ListRows.Count + 2, 1).Resize(, 5).Value = V 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[M_Michael]

Hi Marc,

Thank you for your answer. I'm afraid it does not work. It adds one single line to the first database worksheet.
What I would need is that for each line of the database, one template is filled with the information from the database. For example, the first template should equal this :

Numéro d'étudiant A-12345 Nom de l'étudiant Jean - Dupont

Degré actuel 2ème Corps d'étude Math

When it's done, rename the worksheet with the student ID number and save it in a separate folder, then make a file for each line of the database. I don't know if it is clear because it is a bit difficult to explain. Feel free to ask me any question.

Thank you again

[Greg M]

Hi there,

I'm not sure if I've understood your requirements correctly, but see if the attached workbook does what you need.

Selecting a Student Number from the dropdown list in Cell B2 (green) of the Template worksheet will cause the other cells to be populated with the appropriate values.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[M_Michael]

Hi Greg,

Thank you for your answer.

That's sort of what I need. Sorry if I was not clear. What I'd like to achieve is the following: having a button somewhere that I can click on. It would for each line of my database copy the "template" in a new sheet and then populate the data from the corresponding database column. i.e. for the first row of the database :

Numéro d'étudiant A-12345 Nom de l'étudiant Jean - Dupont
Degré actuel 2ème Corps d'étude Math

then, save this file in a directory. Then do the same for each line of the database. I've about 600 rows so choosing in a dropdown would be way too long and I'd really prefer a full VBA since I have to insert some text and add lines etc. My "only" big problem is to make correspond the titles from the database to the template. (i.e. Numéro d'étudiant = [ID E])

I hope it's clear, otherwise do not hesitate to ask me further questions.
Thanks a lot for your time

[Greg M]

Hi again,

Ok, your requirements are a bit clearer now.

See if the attached workbook does what you need - it uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements & layout.

To save the individual files in a folder of your choice, just include the Path of that folder in the Filename parameter of the SaveAs command.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[M_Michael]

Hi Greg,

Thank you very much for this, it's great ! Really !

What I would like to modify here is that I want to get rid of the formulas and instead do as you do here for all the columns of my database (if I understand correctly) :
" rIdCell.Value = rIdCells.Cells(iIdNo, 1).Value"
i.e. -> having the first name of the student pre populated using the code.
The reason I'm asking this is that that file is a very simplified version of what I am trying to do. When everything will be in place, my objective is to modify the code to
1) insert text before and after the data pre-populated. e.g for student name : The student name is Jean Dupont (this would be a text constant)
2) insert other categories in the template and merge several "boomarks" in once box: e.g. : The student [N.E] [P.E. born in [Birth date] is registered in our school since [registration date] --> The student Jean Dupont born in 1995 is registered in our school since 2013 and has successfully passed his exams.


I don't know if it is clear but where I'm really struggling is inserting those [Category X] in the form to then make something easy for reporting.

Thank you again for your support.

[M_Michael]

Hi Greg,

So I've declared the other cells following your model e.g:

Const sDeg_CELL As String = "B4"

and for my text:
Const text1 As String = "The student-"

and added some text in the value definition : rIdCell.Value = text1& rIdCells.Cells(iIdNo, 1).Value & rIdCells.Cells(iIdNo, 4).Value & text2

And doing this, it works very well so far. I will keep trying to work on it !!

Thank you !

[Marc L]

What I would need is that for each line of the database, one template is filled with the information from the database.

Désolé, j'avais compris le contraire !

As a beginner starter :

Sub Demo2() 
     Dim D$, Lr As ListRow 
         D = ThisWorkbook.Path & "\" 
         Application.ScreenUpdating = False 
         Worksheets(2).Copy 
    For Each Lr In Worksheets(1).[A1].ListObject.ListRows 
        With Lr.Range 
            ActiveWorkbook.ActiveSheet.Name = .Cells(1).Value 
            [B2].Value = .Cells(1).Value 
            [E2].Value = .Cells(2).Value & " - " & .Cells(3).Value 
            [B4].Value = .Cells(4).Value 
            [E4].Value = .Cells(5).Value 
            ActiveWorkbook.SaveAs D & .Cells(1).Value & " .xlsx", 51 
        End With 
    Next 
         ActiveWorkbook.Close 
         Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Greg M]

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated

The attached workbook contains the following code which should do what you need:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your own requirements.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[M_Michael]

Hi Greg !

So I needed the whole day to understand and modify the code (I know I'm slow but god I had to figure everything out !) to my needs but it's PERFECT, very smart !! Thank you !

Now I'm trying to make the form dynamic according to the cells values so I entered a private sub on the template worksheet but it does not work. Would you know why? Here is the code:

Please Login or Register  to view this content.

End Sub

I've done some research but what I do seems ok I don't get it !

Thank you

@Marc : Merci beaucoup pour ton aide ! J'ai continué avec le code proposé par Greg qui marchait bien selon mes besoins. Au plaisir de te reparler.

[Greg M]

Hi again,

Many thanks for your feedback - glad to hear that it's working for you. Don't worry about taking time to understand the code and feel free to ask me for further explanations about anything.

I'm not sure I understand exactly what you need - it looks as if you wish to hide Row(1) on the Template worksheet if the Named Range "CellName" (on that worksheet) contains the string value "The student has good grades", and that you wish to use event-driven code to perform this action.

Question: Where does the value "The student has good grades" come from? Is it entered directly by the User? Is it the result of a formula which uses grade values etc. to produce its result? Is it generated in some other way?

In the first case the "Worksheet_Change" routine should be used, but the second case should use the "Worksheet_Calculate" routine. Obviously I can't suggest anything for the third case at this stage!

Let me know the situation and I'll see if I can provide the code for you.

Best regards,

Greg M

[M_Michael]

Hi Greg,

So I changed to Worksheet_Change since the user will enter values and depending on those, rows should be hidden.

I've changed like this :

Application.EnableEvents = False

Application.ScreenUpdating = False

and put a small cap to the "The" because the Lcase do not accept it. It works !

Yet, I need that "The" to be written that way, so I removed the Lcase, everything works

[Greg M]

Great!

For future reference you could also use:

Please Login or Register  to view this content.

Best regards,

Greg M

[Greg M]

Hi again,

As per your private message, the code in the attached workbook will save the individual student file in a folder with the name "Id No nnnnn". This folder will be created if it does not already exist.

The code which provides this feature is as follows:

Please Login or Register  to view this content.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[M_Michael]

Hi Greg !

Works perfectly, thanks again !!

[Greg M]

Great!

Thanks for all of your feedback - glad I was able to help.

Best regards,

Greg M