Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
With help from macropod in the Word General Group and Jean-Yves in this group
I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
You probably need to include the full path, not just the filename.
"expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Thanks Barb, I can see what you mean, so I need to modify this line, right:
strFilename = Dir("C:/AAA-TriageHolder/*.doc") How would I change it to get it to include the path? Would this work? strFilename = Dir("C:/AAA-TriageHolder/*.doc") strFilename = "C:/AAA-TriageHolder/" & strFilename Thanks for the quick response. ed "Barb Reinhardt" wrote: You probably need to include the full path, not just the filename. "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
OK, Adjusted for the path and got it to open the file. Now it gets to:
For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Untested.
Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Thanks Dave,
Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
That doesn't sound like the best approach to me. But I don't know enough about
MSWord to know if that's true. I'd ask in a forum dedicated to MSWord for a better approach. But if you're only looking to populate a single form field, can't you refer to it by its name--not loop through all of them? expect_ed wrote: Thanks Dave, Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Put in a MsgBox for Temp to see if it is gathering the fields properly.
Trap for an empty FormField, not On Error Resume Next For Each oFld In wdDoc If LEN(oFld.Result) = 0 Then Range("A2").Offset(x, y) = "" Else Temp = oFld.Result Msg Temp Range("A2").Offset(x, y) = temp End If y = y + 1 Next oFld Mike F "expect_ed" wrote in message ... Thanks Dave, Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
In this case there are about 150 form fields on the form, of which I need to
capture about 1/2 of them. But not all of them have bookmarks, which I think is the only way to name them, at least that I know of, so on a post in the Word group this technique was suggested. If there is a way to reference a formfield that does not have a bookmark I would love to know about it. Thanks for your help. "Dave Peterson" wrote: That doesn't sound like the best approach to me. But I don't know enough about MSWord to know if that's true. I'd ask in a forum dedicated to MSWord for a better approach. But if you're only looking to populate a single form field, can't you refer to it by its name--not loop through all of them? expect_ed wrote: Thanks Dave, Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Thanks for your help Mike,
Unfortunately that code still hangs. Not sure what the story is with these forms but there are some form fields that are blank in a strange way i.e. normally on a Word form what I've seen is the blank fields have several small circles to hold the place for the entry. This form has those in some places but in others there is just a grayed area about the width of 3 periods/pixels. This was the first place the code hung up, and your solution worked there. But in other places there seems to be no indication of a formfield and yet the code to step through every form field seems to find one and enters a value in excel. It is at those points that your code fails with the Run-time error 5825 'object has been deleted'. Not sure how to handle that other than with the Resme Next, which works?? Thanks again for your help. ed "Mike Fogleman" wrote: Put in a MsgBox for Temp to see if it is gathering the fields properly. Trap for an empty FormField, not On Error Resume Next For Each oFld In wdDoc If LEN(oFld.Result) = 0 Then Range("A2").Offset(x, y) = "" Else Temp = oFld.Result Msg Temp Range("A2").Offset(x, y) = temp End If y = y + 1 Next oFld Mike F "expect_ed" wrote in message ... Thanks Dave, Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up Macro to Copy Word Formfields into Excel
Sounds like you should visit a forum dedicated to MSWord.
expect_ed wrote: In this case there are about 150 form fields on the form, of which I need to capture about 1/2 of them. But not all of them have bookmarks, which I think is the only way to name them, at least that I know of, so on a post in the Word group this technique was suggested. If there is a way to reference a formfield that does not have a bookmark I would love to know about it. Thanks for your help. "Dave Peterson" wrote: That doesn't sound like the best approach to me. But I don't know enough about MSWord to know if that's true. I'd ask in a forum dedicated to MSWord for a better approach. But if you're only looking to populate a single form field, can't you refer to it by its name--not loop through all of them? expect_ed wrote: Thanks Dave, Yes, that fixed it, or rather using just .FormField fixed it (including the Word gave a wierd error about trying to open some other Excel file) I now have it working to read in the first form, and it actually opens the subsequent forms and goes through them apparently collecting the form field data, but it only puts the data from the first form in Excel. The only change i made to the code was to add "On Error Resume Next" after the temp=oFld.Result line because if the formfield was blank it would bomb out. (is there a better way to handle that error?) I also inserted a few MsgBox commands so I can confirm that the strFilename variable is updating correctly, which it is. Thanks again for your help, both past and future. ed "Dave Peterson" wrote: Untested. Dim oFld As Word.FormField .... For Each oFld In wdDoc.FormFields expect_ed wrote: OK, Adjusted for the path and got it to open the file. Now it gets to: For Each oFld in wdDoc And I get a Run-time error '438': Object doesn't support this property or method Help very appreciated. ed "expect_ed" wrote: With help from macropod in the Word General Group and Jean-Yves in this group I think I have a start on copying all the Formfield data from a series of Word files into Excel. But I'm stuck on a command I do not fully understand. The intent of the code is to: 1. Dim and initialize all the variables, etc. 2. Get the first filename in a directory that holds all the relevant files 3. Open each file in turn (THIS IS THE PROBLEM AREA SO FAR) 4. Read each FormField in the open file 5. Set a cell via offsets to each Formfield value 6. Close the document and repeat from step 3 7. Quit Word and clear the objects Here is my code so far: Sub WordToExcel() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim x As Integer Dim y As Integer Dim strFilename As String Dim temp As String Dim oFld As FormField Set wdApp = New Word.Application 'initialise counter x = 1 y = 1 'search for first file in directory strFilename = Dir("C:/AAA-TriageHolder/*.doc") Do While strFilename < "" Set wdDoc = wdApp.Documents.Open(strFilename) '//Error message results here With wdDoc For Each oFld In wdDoc temp = oFld.Result Range("A2").Offset(x, y) = temp y = y + 1 Next oFld End With wdDoc.Close x = x + 1 strFilename = Dir Loop wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub As noted above, I get an error when I run the macro saying: Run-time error '5174': This file could not be found. Try one or more of the following: *Check the spelling of the name of the document. *Try a different file name. (A12345.doc) The A12345.doc is the first file in the directory, so it is clearly seeing it in order to set the strFilename, so why can it not then find the file. Any help greatly appreciated. ed -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (or other) macro for cleaning date data | Excel Discussion (Misc queries) | |||
Copy from WORD to EXCEL using a macro..... | Excel Discussion (Misc queries) | |||
Macro to copy a word doc into an excel doc | Excel Discussion (Misc queries) | |||
copy from Excel to Word by macro with certain for | Excel Discussion (Misc queries) |