Forum Discussion
hrh_dash
Oct 17, 2022Iron Contributor
Macro execute to loop all word doc files in a folder
How do i amend the code below so that the macro could select all word doc files in the folder? There are no errors but when the code is executed line by line, the macro did not run through the Do Wh...
- Oct 17, 2022
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*")
hrh_dash
Oct 17, 2022Iron Contributor
, if i would like to add an additional file type such as docx; would the following be correct?
currently, nothing is being populated when the code is executed.
myFile = Dir(path & "*.docx*;*.doc*")
HansVogelaar
Oct 17, 2022MVP
"*.doc*" includes .doc, .docx and .docm
If the code doesn't process any documents, there must be something wrong with the path.
As JMB17 asked: "Is "Bizfile" the name of a folder, or is it the first part of the filename(s)?"
- hrh_dashOct 17, 2022Iron Contributor
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.
- JMB17Oct 17, 2022Bronze Contributor
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).
- hrh_dashOct 17, 2022Iron Contributor
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 = True
- HansVogelaarOct 17, 2022MVP
We don't know the code that is being called, but I notice that you open a document using oDoc as variable, but then close a document using a different variable wordDoc. Could that be the cause of the problem?