Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
"Ruth" skrev i en meddelelse ... Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub Hi Ruth With "Select Case" your code will be like this: Sub BuildForm() fila = 3 I = 1 For Each wdFF In wordFile.Formfields Select Case wdFF Case wd.Name = "Text1" wdFF.Result = Range("H" & fila) Case wd.Name = "Dropdown2" wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 Case wdFF.Name = "Text3" wdFF.Result = Range("T" & fila) End Select Next End Sub Regards, Per |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
Hi Per
Thanks for your help. My idea was to get something like the code you propose however, I tried with your code and it doesn't work ... and I don't know why. In fact, it executes fine but the CASE selection doesn't seem to work as it doesn't execute any of them, so af the end the document is empty and no data was copied. Any idea of why this might happen? Note: Just to make sure, I corrected a spelling mistake in your code as it should be Case wdFF.Name for Text1 and Dropdown2. Isn't it?. "Per Jessen" wrote: "Ruth" skrev i en meddelelse ... Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub Hi Ruth With "Select Case" your code will be like this: Sub BuildForm() fila = 3 I = 1 For Each wdFF In wordFile.Formfields Select Case wdFF Case wd.Name = "Text1" wdFF.Result = Range("H" & fila) Case wd.Name = "Dropdown2" wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 Case wdFF.Name = "Text3" wdFF.Result = Range("T" & fila) End Select Next End Sub Regards, Per |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
The Select Case code should go like this:
Select Case wdFF.Name Case "Text1" wdFF.Result = Range("H" & fila) etc.... On 11 Jan, 10:56, Ruth wrote: Hi Per Thanks for your help. My idea was to get something like the code you propose however, I tried with your code and it doesn't work ... and I don't know why. In fact, it executes fine but the CASE selection doesn't seem to work as it doesn't execute any of them, so af the end the document is empty and no data was copied. Any idea of why this might happen? Note: Just to make sure, I corrected a spelling mistake in your code as it should be Case wdFF.Name for Text1 and Dropdown2. Isn't it?. "Per Jessen" wrote: "Ruth" skrev i en meddelelse ... Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub Hi Ruth With "Select Case" your code will be like this: Sub BuildForm() fila = 3 I = 1 For Each wdFF In wordFile.Formfields Select Case wdFF Case wd.Name = "Text1" wdFF.Result = Range("H" & fila) Case wd.Name = "Dropdown2" wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 Case wdFF.Name = "Text3" wdFF.Result = Range("T" & fila) End Select Next End Sub Regards, Per |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
Thanks Andrew!
It is perfect like that. It worked fine. Any thought maybe about the problem I explained in the previous post? :) "Andrew Taylor" wrote: The Select Case code should go like this: Select Case wdFF.Name Case "Text1" wdFF.Result = Range("H" & fila) etc.... On 11 Jan, 10:56, Ruth wrote: Hi Per Thanks for your help. My idea was to get something like the code you propose however, I tried with your code and it doesn't work ... and I don't know why. In fact, it executes fine but the CASE selection doesn't seem to work as it doesn't execute any of them, so af the end the document is empty and no data was copied. Any idea of why this might happen? Note: Just to make sure, I corrected a spelling mistake in your code as it should be Case wdFF.Name for Text1 and Dropdown2. Isn't it?. "Per Jessen" wrote: "Ruth" skrev i en meddelelse ... Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub Hi Ruth With "Select Case" your code will be like this: Sub BuildForm() fila = 3 I = 1 For Each wdFF In wordFile.Formfields Select Case wdFF Case wd.Name = "Text1" wdFF.Result = Range("H" & fila) Case wd.Name = "Dropdown2" wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 Case wdFF.Name = "Text3" wdFF.Result = Range("T" & fila) End Select Next End Sub Regards, Per |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with FOR - IF Loop
Yes - see my reply in the other thread
(String vs LONG format - if that's the "previous post" you mean) A On 11 Jan, 14:56, Ruth wrote: Thanks Andrew! It is perfect like that. It worked fine. Any thought maybe about the problem I explained in the previous post? :) "Andrew Taylor" wrote: The Select Case code should go like this: Select Case wdFF.Name Case "Text1" wdFF.Result = Range("H" & fila) etc.... On 11 Jan, 10:56, Ruth wrote: Hi Per Thanks for your help. My idea was to get something like the code you propose however, I tried with your code and it doesn't work ... and I don't know why. In fact, it executes fine but the CASE selection doesn't seem to work as it doesn't execute any of them, so af the end the document is empty and no data was copied. Any idea of why this might happen? Note: Just to make sure, I corrected a spelling mistake in your code as it should be Case wdFF.Name for Text1 and Dropdown2. Isn't it?. "Per Jessen" wrote: "Ruth" skrev i en meddelelse ... Hi, The idea with the macro below is to take several cells from a row in EXCEL and use then to fill in a form previously defined in a WORD file. The approach below in principle is working fine but I guess there should a more efficient way to do the loop(I use a main "For" loop with several "IF" nested, one for each potential field to be filled in the form). As I said, I checked it out and it works but the form has around 30 fields and I would like to know whether there is a more elegant way to program it. Thanks!! Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I, fila As Integer myFile = "NEW_FORM.doc" Set wordApp = CreateObject("Word.Application") Set wordFile = wordApp.Documents.Open(myFile) ....... fila = 3 I = 1 For Each wdFF In wordFile.Formfields If wdFF.Name = "Text1" Then wdFF.Result = Range("H" & fila) End If If wdFF.Name = "Dropdown2" Then wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 End If If wdFF.Name = "Text3" Then wdFF.Result = Range("T" & fila) End If ................... I = I + 1 Next wordApp.Quit Set wordFile = Nothing Set wordApp = Nothing End Sub Hi Ruth With "Select Case" your code will be like this: Sub BuildForm() fila = 3 I = 1 For Each wdFF In wordFile.Formfields Select Case wdFF Case wd.Name = "Text1" wdFF.Result = Range("H" & fila) Case wd.Name = "Dropdown2" wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1 Case wdFF.Name = "Text3" wdFF.Result = Range("T" & fila) End Select Next End Sub Regards, Per |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FOR loop macro | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming | |||
how to put a loop in a macro? | New Users to Excel | |||
Help With loop macro?? | Excel Programming |