sahil41352
03-06-2014, 02:54 AM
I want to extract the data from the form made in MS using VBA .PLease help me out with this problem.
"If my previous response is insufficient then it is obviously more complicated than I am able to explain."
No. What would make a difference is if you actually answered the questions. It is not more complicated than you can explain, as you did not really explain anything.
It may be more complicated that you are capable of right now, but perhaps if you tried it would become less complicated. Here is a sample, but because you did not explain anything really, it may not be applicable for you.
Sub GrabIt()
Dim appWord As Word.Application
Dim wrdDoc As Word.Document
Dim file
Dim myPath As String
Dim myArray()
Dim j As Long
Dim TempString As String
Dim var
myPath = "c:\zzz\Test\TestWord\"
file = Dir(myPath & "*.doc")
Set appWord = CreateObject("Word.Application")
Do While file <> ""
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)
ReDim Preserve myArray(j)
myArray(j) = TempString
j = j + 1
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()
Loop
appWord.Quit
Set appWord = Nothing
' now have an array of all the bookmarked data
' from all the documents, so dump into Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next
End Sub
Assumptions:
1. a bunch of Word files (.doc) in the folder "c:\zzz\Test\TestWord"
2. ALL of the files have the desired data in a bookmark named "GetData" Perhaps you can see why I asked the question of where the information is? If it is somewhere else...then you have to get it from that somewhere else.
There. Those are the assumptions. Here are the steps again (slightly modified).
1. declare an instance of Word
Dim appWord As Word.Application
2. set that instance of Word.
Set appWord = CreateObject("Word.Application")
3. use that instance of Word to open the first file
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)
4. use that instance of Word to get the information (you did not answer what form that is in). I am using it as if in a bookmark.
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)
' above line not needed if bookamrk does NOT include the paragraph mark
5. add information to an array of all the information
ReDim Preserve myArray(j)
myArray(j) = TempString
6. go on to the next Word file
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()
7. destroy the instance of Word as you are finished with it
appWord.Quit
Set appWord = Nothing
8. put that information into the Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next
Done. The information in the named bookmark in ALL the Word files in the folder c:\zzz\Test\TestWord are placed in an Excel file, starting from A2, and proceeding down.
NOTE!!!! The Dir function actions the files in order, and the default order in a folder is alphabetical.
So the files are (in this example):
four.doc
one.doc
three.doc
two.doc
THUS: the listing in the Excel file is the contents of the Word documents in that order.
A2 = "whatever FOUR.doc data" (the text in the bookmark GetData)
A3 = "1234 from ONE.doc data" (the text in the bookmark GetData)
A4 = "blah blah THREE.doc data" (the text in the bookmark GetData)
A5 = "yadda TWO.doc data) (the text in the bookmark GetData)
It may look complicated, but actually it is not. We can gladly help with the actual code (IMO, this is the least complicated part), but YOU have to do the thinking (IMO, for most people this is the hardest part).
"If my previous response is insufficient then it is obviously more complicated than I am able to explain."
No. What would make a difference is if you actually answered the questions. It is not more complicated than you can explain, as you did not really explain anything.
It may be more complicated that you are capable of right now, but perhaps if you tried it would become less complicated. Here is a sample, but because you did not explain anything really, it may not be applicable for you.
Sub GrabIt()
Dim appWord As Word.Application
Dim wrdDoc As Word.Document
Dim file
Dim myPath As String
Dim myArray()
Dim j As Long
Dim TempString As String
Dim var
myPath = "c:\zzz\Test\TestWord\"
file = Dir(myPath & "*.doc")
Set appWord = CreateObject("Word.Application")
Do While file <> ""
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)
ReDim Preserve myArray(j)
myArray(j) = TempString
j = j + 1
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()
Loop
appWord.Quit
Set appWord = Nothing
' now have an array of all the bookmarked data
' from all the documents, so dump into Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next
End Sub
Assumptions:
1. a bunch of Word files (.doc) in the folder "c:\zzz\Test\TestWord"
2. ALL of the files have the desired data in a bookmark named "GetData" Perhaps you can see why I asked the question of where the information is? If it is somewhere else...then you have to get it from that somewhere else.
There. Those are the assumptions. Here are the steps again (slightly modified).
1. declare an instance of Word
Dim appWord As Word.Application
2. set that instance of Word.
Set appWord = CreateObject("Word.Application")
3. use that instance of Word to open the first file
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)
4. use that instance of Word to get the information (you did not answer what form that is in). I am using it as if in a bookmark.
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)
' above line not needed if bookamrk does NOT include the paragraph mark
5. add information to an array of all the information
ReDim Preserve myArray(j)
myArray(j) = TempString
6. go on to the next Word file
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()
7. destroy the instance of Word as you are finished with it
appWord.Quit
Set appWord = Nothing
8. put that information into the Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next
Done. The information in the named bookmark in ALL the Word files in the folder c:\zzz\Test\TestWord are placed in an Excel file, starting from A2, and proceeding down.
NOTE!!!! The Dir function actions the files in order, and the default order in a folder is alphabetical.
So the files are (in this example):
four.doc
one.doc
three.doc
two.doc
THUS: the listing in the Excel file is the contents of the Word documents in that order.
A2 = "whatever FOUR.doc data" (the text in the bookmark GetData)
A3 = "1234 from ONE.doc data" (the text in the bookmark GetData)
A4 = "blah blah THREE.doc data" (the text in the bookmark GetData)
A5 = "yadda TWO.doc data) (the text in the bookmark GetData)
It may look complicated, but actually it is not. We can gladly help with the actual code (IMO, this is the least complicated part), but YOU have to do the thinking (IMO, for most people this is the hardest part).