Oct 16 2022 06:54 PM
Oct 16 2022 06:54 PM
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 While loop.
Dim path As String Dim oDoc As Object Application.ScreenUpdating = False Application.EnableEvents = False path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile" myFile = Dir(path & "*.*") Do While myFile <> "" Set oDoc = documents.Open(filename:=path & myFile) Call extractdatafromwordtoexcel Call findcopyandpaste wordDoc.Close myFile = Dir Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Appreciate the assist in advance..
Oct 16 2022 10:14 PM
Oct 17 2022 12:49 AMSolution
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*")
Oct 17 2022 02:17 AM
, 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*")
Oct 17 2022 02:35 AM
Oct 17 2022 02:50 AM
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.
Oct 17 2022 03:26 AM
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?
Oct 17 2022 06:18 AM
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.
Macro2(byref localVariableName as object)
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).
Oct 17 2022 06:30 AM
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
Oct 17 2022 06:48 AM
I'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
Oct 17 2022 07:30 AM
Oct 17 2022 07:33 AM