Forum Discussion
Macro execute to loop all word doc files in a folder
Also, to be on the safe side, I'd explicitly loop through Word documents, so that you don't get an error if the folder contains another type of file:
path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile\" myFile = Dir(path & "*.doc*")
alright, i thought i would need to include "doc" and "docx" file type to have the code running. ok, so right now, the folder has 2 word document files.
Currently when the macro is executed, data from the 1st word document file was populated twice rather then both word documents file being populated.
Attaching the screenshot below:
Data in the 1st and 2nd column has been removed due to confidentiality.
JMB17 , appreciate the response. Bizfile is folder.
The macro works after inputting the "\" after the Bizfile and include the "*.docx*" but somehow it resulted in duplicate entries.
As Hans already stated, we don't know what's in the other procedures being called that extract the information. Specifically, we can't see how those procedures reference the word document referenced by the variable "oDoc" (because oDoc is a local variable and it's not being passed as an argument to the other procedures, so it's unclear how those procedures are referencing the document).
I'm assuming the word object variable itself is globally scoped - are the other procedures referencing the document by index number (documents(1))? If the first document is not closed (because the variable name "wordDoc" appears to be incorrect as Hans noted), then documents(1) would still be referencing the first document.
Personally, I would pass the document as an argument to the other function(s) instead of assuming which document it should process by some other means such as index number.
Macro1()
Macro2 oDoc
End Sub
Macro2(byref localVariableName as object)
debug.print localVariableName.Paragraphs.Count
End Sub
Also, you should consider adding Option Explicit to the top of your module, if you're not already using it. Then, the compiler will help catch variable name issues such as misspellings or if you accidentally used "wordDoc" when you meant "oDoc" (if worddoc is not module or global level).
JMB17 , HansVogelaar ,
i think i know what was the issue. The 2 sub (extractdatafromwordtoexcel and findcopyandpaste) was taking reference from a range hence explains why there seems to be a duplicate data.
The macro works when i debug.print myFile.
However, i need to tweak the code for extracting data in a tables from a doc type document. Therefore, i believe the GetObject liner is throwing out issues. Is there a way to work around for this GetObject liner?
Dim path As String Dim oDoc As Variant Dim wordDoc As Object Application.ScreenUpdating = False Application.EnableEvents = False On Error Resume Next path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile\" myFile = Dir(path & "*.doc*") Do While myFile <> "" Debug.Print myFile Set oDoc = documents.Open(filename:=path & myFile) Set wordDoc = GetObject(oDoc) '<-- I believe this is the error populating. Not able to take reference from a word document With wordDoc tbls = wordDoc.Tables.Count If tbls = 0 Then MsgBox "There no tables To extract" End If rowO = 6 'Extracting into cell A6 For tbBegin = 1 To tbls With .Tables(tbBegin) For RowNo = 1 To .Rows.Count For ColNo = 1 To .Columns.Count ws.Cells(rowO, ColNo) = Application.WorksheetFunction.Clean(.Cell(RowNo, ColNo).Range.Text) Next ColNo rowO = rowO + 1 Next RowNo End With rowO = rowO Next tbBegin End With Call findcopyandpaste 'oDoc.Close myFile = Dir Loop Application.EnableEvents = True Application.ScreenUpdating = TrueI'm not sure what the purpose of wordDoc is since you already have a reference to the document with oDoc?
GetObject accepts string argument(s), but you're passing it an object. Since error handling is set to 'resume next' you're not getting an error. If you want wordDoc to also reference the document (but is this really necessary - do you really need 2 references to the same object?), then
Set wordDoc = oDoc