+ Reply to Thread
Results 1 to 70 of 70

support to capture specific data from Word to Excel using VBA Automation

  1. #1
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    support to capture specific data from Word to Excel using VBA Automation

    Hello all,

    I'm trying to automate BOE (Bill of Entry) to Excel for our internal purpose. I need VBA code to find and capture specific data from Word and paste it on Excel under specific fields

    I have attached Word and Excel for your understanding and highlighted field in which it has to paste. Some details which is one time whereas some will change based on part # such as part commodity tax.

    Please support, thanks
    Attached Files Attached Files
    Last edited by priyadharshane; 06-18-2019 at 09:39 PM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Assumes the word file is always the same format...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Dangelor, Thanks

    When I run Macro it's not working and causing error at field ( Dim wApp As New Word.Application)

    Few things, Format remains same but I have few needs. Can Macro run with Active word file as files may change but format remains same. Hence, it should run with respect active word file

    Second thing is Const sFIND As String = "Inv Val,Freight,Insurance,Misc. Charges,Exchange Rate,AAAAAA,BBBBBB,CCCCCC"

    I have mentioned AAAAAA, BBBBBB, CCCCCC as an Example, I have run on hundreds of parts. One things is macro needs to run parts on column E which is where I paste all parts.

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    When I run Macro it's not working and causing error at field ( Dim wApp As New Word.Application)
    This code needs the Microsoft Word object library activated to access Word functions. Go to Tools > References and click on the version for your system.

  5. #5
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Awesome!! Its working

    What about my other queries? Can I able to upload any word file in this macro for look up? like file name differs?

    What if I have more part numbers? will it loop and pull data based on column E (Part numbers) and bring data

    Please advise
    Attached Files Attached Files

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Can I able to upload any word file in this macro for look up? like file name differs?
    As long as it is formatted like your test BOE.docx, yes.
    What if I have more part numbers? will it loop and pull data based on column E (Part numbers) and bring data
    Working on it...

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Try this...
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Seriously it's a great work

    It's pulling parts correctly. There is a minor bug

    It's showing wrong data for below mentioned. Earlier version was showing correctly


    Inv Val 112749.20 USD TOI: FOB 100 NE ADAMS STREET
    Freight Freight Invoice
    Insurance 0.048000% 0.00 USA 61629
    Misc. Charges 0.00 5073.71 USD
    Exchange rate 1.00 USD = 70.3000 INR

    Below is the desired value,

    Inv Val 112749.20
    Freight 1694.00
    Insurance 0.048000%
    Misc. Charges 5073.71
    Exchange rate 70.3000

  9. #9
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor Any luck on this please?


    It's showing wrong data for below mentioned. Earlier version was showing correctly


    Inv Val 112749.20 USD TOI: FOB 100 NE ADAMS STREET
    Freight Freight Invoice
    Insurance 0.048000% 0.00 USA 61629
    Misc. Charges 0.00 5073.71 USD
    Exchange rate 1.00 USD = 70.3000 INR

    Below is the desired value,

    Inv Val 112749.20
    Freight 1694.00
    Insurance 0.048000%
    Misc. Charges 5073.71
    Exchange rate 70.3000

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Seems it was picking up a line near the end of the document. I clipped off the last few lines...
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Still the same. BOE Test1 was capturing correctly

    Enclosed picture of current execution and correct format for your reference
    Attached Images Attached Images

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    One more try...
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor,

    Nope, It's blank now (not pulling any data). ImportBOE_3() is affecting commodity tax for parts as well, it's blank after row# 84

    Can you able to alter ImportBOE 2 (it brought tax % for all parts) with ImportBOE 1 (It brought parameters initially)

    Thanks for your great help

  14. #14
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    For me, version 3 worked on your original BOE word file. Can you post the file it's not working on?

  15. #15
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor,

    Can you try this BOE file (word file) with BOE 2 macro (2nd Version) data please?
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Enclosed few more BOE examples
    Attached Files Attached Files

  17. #17
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    These 3 newer files do not have the same format as the original - there are 9 addition lines of data at the beginning. My previous code depended on those first few lines being the same. I will see if I can code it a different way...

  18. #18
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Try:
    Please Login or Register  to view this content.
    When you're satisfied the code works as required, you can change:
    wdApp.Visible = True
    to:
    wdApp.Visible = False
    and change:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=True)
    to:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=False)
    Last edited by macropod; 06-21-2019 at 11:32 PM.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  19. #19
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Paul

    It created a new word file and processed. It stopped at below code as an error

    Please Login or Register  to view this content.

  20. #20
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    The posted code works just fine with all your BOE document attachments and, since you don't say what the error is, I'm unable to comment further.

  21. #21
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @macropod

    Run-time error '9'

    Subscript out of range


    Please Login or Register  to view this content.
    WkSht.Range("AK" & i + 2) = Split(strTxt, vbTab)(i)
    Please Login or Register  to view this content.
    Last edited by priyadharshane; 06-22-2019 at 01:23 AM.

  22. #22
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Now you're reporting a different code line...

    As I said, the posted code works just fine with all your BOE document attachments. If you're getting errors, it's because whatever you're processing has a different structure than all those attachments.

  23. #23
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Please Login or Register  to view this content.
    Sorry I ran different BOE suppose to be. All BOE has same structure except the fact I deleted 21 rows at start of BOE due to company parameters

  24. #24
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Well, if what you're trying to process is different from what you posted, how can you expect the code to work??? The code is written for what you did post, not for what you didn't post.

    All you've done so far is waste peoples' time having them develop solutions that don't work because you keep changing the rules.

  25. #25
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @macropod

    Here you go, this is the actual file
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor

    BOE version 2 works expect few errors. Enclosed actual BOE for your reference
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Adding few more doc for your analysis
    Attached Files Attached Files

  28. #28
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Change:
    .Text = "IGM No*Inv No & Dt.*^13"
    to:
    .Text = "[ ]{1,}Original*Inv No & Dt.*^13"

  29. #29
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @macropod

    I tested mentioned file with test 3 excel file with BOE file name 3658239_ILC-4824_BOE

    It picked up values portion correctly but failed to pick tax portion rest to parts to column T, U and AF

    I tested mentioned file with test 4 excel file with BOE file name 3091811_ILC-4791_BOE

    It showing wrong data
    Attached Files Attached Files

  30. #30
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Try the following:
    Please Login or Register  to view this content.
    Next time you ask a question, kindly provide all the relevant information.
    Last edited by macropod; 06-22-2019 at 11:48 PM.

  31. #31
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Macropod,

    Once I start running macro, I keep getting message that excel is waiting for another application to complete an OLE action.

    Is it common? Do I need to wait? I tried again and again but my excel got crashed
    Last edited by priyadharshane; 06-22-2019 at 07:49 AM.

  32. #32
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    You might try changing:
    wdApp.Visible = False
    to:
    wdApp.Visible = True
    wdApp.ScreenUpdating = False
    and change:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=False)
    to:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=True)

  33. #33
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Macropod,

    Hi Paul,

    Still the same issue. I changed the parameters as mentioned and once executing macros. It has flashed error msg of "excel is waiting for another application to complete an OLE action" and crashed.

    There is no parameter of wdApp.ScreenUpdating = False in mentioned file. Is it working for you? Please advise


    Please Login or Register  to view this content.
    Sub GetBOEData()
    'Note: this code requires a reference to the Word object model.
    'See under the VBE's Tools|References.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document
    Dim WkSht As Worksheet, strFile As String, strTxt As String, i As Long, r As Long
    With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
    .Filters.Add "Images", "*.doc*", 1
    .AllowMultiSelect = False
    'use Show method to display File Picker dialog box and return user's action
    If .Show = -1 Then
    strFile = .SelectedItems(1)
    End If
    End With
    If strFile = "" Then Exit Sub
    Set WkSht = ActiveSheet
    r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=True)
    With wdDoc
    .ConvertNumbersToText (wdNumberAllNumbers)
    With .Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Format = False
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Replacement.Text = "^p"
    .Text = "[ ]{1,}[OI][rn][id][gi][ia][[n]*Inv No & Dt.*^13"
    .Execute Replace:=wdReplaceOne
    .Replacement.Text = ""
    .Text = "Duty Payable*THE ORDER.*[^13]{1,}"
    .Execute Replace:=wdReplaceOne
    .Text = "Declaration*NIC*^13*[^12^13]{1,}"
    .Execute Replace:=wdReplaceAll
    .Text = "[ ]{1,}Indian Customs*Item Details^13"
    .Execute Replace:=wdReplaceAll
    .Text = "[ ]{1,}Discount Rate*Item Details^13"
    .Execute Replace:=wdReplaceAll
    .Text = "SVB Load*^13*^13"
    .Execute Replace:=wdReplaceAll
    .Text = "([_-]{1,}^13)[!^13]@Rs.*\1*NIC*^13*^13"
    .Execute Replace:=wdReplaceAll
    .Text = "([_-]{1,}^13)[!^13]@Rs.*\1"
    .Execute Replace:=wdReplaceAll
    .Text = "([_-]{1,}^13)[Ss]lno*\1"
    .Execute Replace:=wdReplaceAll
    .Text = "[Ss]lno*[_-]{1,}^13"
    .Execute Replace:=wdReplaceAll
    .Text = "[ ]{1,}GST Cess*[^12^13]{1,}"
    .Execute Replace:=wdReplaceAll
    .Text = "^13[!^13]@NOEXCISE*Cess :[!^13]{1,}"
    .Execute Replace:=wdReplaceAll
    .Replacement.Text = "\1"
    .Text = "(Misc. Charges*.[0-9]{1,}"
    .Execute Replace:=wdReplaceAll
    .Text = "(Exchange rate*="
    .Execute Replace:=wdReplaceAll
    .Text = "([0-9]{1,})[!^13]@| ([A-Z0-9]{6})*([0-9.]{4,5} %)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*^13"
    .Replacement.Text = "\1^t\2^t\3^t\4^t\5^p"
    .Execute Replace:=wdReplaceAll
    .Text = "^13*:[ ]@([0-9.%]{1,})[!^13]{1,}"
    .Replacement.Text = "\1^t"
    .Execute Replace:=wdReplaceAll
    End With
    strTxt = .Paragraphs.First.Range.Text
    For i = 0 To 5
    WkSht.Range("AK" & i + 2) = Split(strTxt, vbTab)(i)
    Next
    For i = 2 To .Paragraphs.Count - 1
    r = r + 1: strTxt = .Paragraphs(i).Range.Text
    WkSht.Cells(r, 1).Value = Split(strTxt, vbTab)(0)
    WkSht.Cells(r, 5).Value = Split(strTxt, vbTab)(1)
    WkSht.Cells(r, 19).Value = Split(strTxt, vbTab)(2)
    WkSht.Cells(r, 21).Value = Split(strTxt, vbTab)(3)
    WkSht.Cells(r, 31).Value = Split(strTxt, vbTab)(4)
    Next
    .Close SaveChanges:=False
    End With
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    Please Login or Register  to view this content.

  34. #34
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Please Login or Register  to view this content.

  35. #35
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor

    Hiiii,

    Well few glitches that it's not picking tax portion from row 84 as well as it's not picking exchange rate for one file due to format issue.

    To resolve format issue, is there way to change word document to specific format and pick. I know it very very hard task to do. I'm just checking on possibilities to change all word file to specific format. Enclosed both file for your reference
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Test 2 files for your review
    Attached Files Attached Files

  37. #37
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Adding BOE file for your format verification with other examples of BOE
    Attached Files Attached Files

  38. #38
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: support to capture specific data from Word to Excel using VBA Automation

    Hi !

    Did you try to save Word documents to text files ?

  39. #39
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Marc

    I guess it converts into same format of text file. I couldn't able to upload it as it breaks forum rule of over 100 KB

  40. #40
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Please Login or Register  to view this content.

  41. #41
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor,

    Him

    It stopped at row 168. As you there is part numbers are dynamic always and it may increase or decrease

    Enclosed file for your reference. Does changing word file to text file and bring into excel is an option to fix word format issue and align as one

    Please advise
    Attached Files Attached Files

  42. #42
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Quote Originally Posted by priyadharshane View Post
    Still the same issue. I changed the parameters as mentioned and once executing macros. It has flashed error msg of "excel is waiting for another application to complete an OLE action" and crashed.
    In that case, you most likely have an orphanced Word session running in the background keeping whatever file you're trying to process open. Close Office completely, then start Task Manager and kill off any Word/Excel sessions running in the background. Restart both then discard any recovered files.
    There is no parameter of wdApp.ScreenUpdating = False in mentioned file.[/QUOTE]
    In what file? Did you add that line as suggested? And, yes, it does work when the other parameters are set to True.

    Needless to say, perhaps, but the code I posted works just fine with both your attachments in post #25. Of course, with over 300 pages to process and 600 rows of data to output, execution isn't instantaneous on the larger file...
    Last edited by macropod; 06-22-2019 at 06:51 PM.

  43. #43
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Any blanks in the results indicate the part number was not found in the document...
    Please Login or Register  to view this content.

  44. #44
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    I've added ReadOnly:=True to:
    Set wdDoc = wdApp.Documents.Open
    in post #30 so as to minimise OLE errors caused by other processes having the document open.

  45. #45
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor,

    I just cross verified with word file. Data is available in word file but how how its not picking it up.

    Also, I noticed it pulled wrong tax percentage for one part # (eg part # 99) Tax percentage is 7.5% whereas it picked 10%

    I have highlighted specific parts in green
    Attached Files Attached Files

  46. #46
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    For some unknown reason, there are gaps in the data when it is read from the file. I have no way of knowing why. Sorry, But I have no solution for this problem at the moment. I may look into it at a later date. Good luck!

  47. #47
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Macropod,

    I checked taskbar to check on background MS files. There is no file running in background.

    I waited for sometime and error msg keeps popping out and crashed excel and word running. I tried to fix it by alternates mentioned goggle. But it's not working

  48. #48
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @dangelor

    Thanks for supporting all along and I really appreciate you time and resource spent on this macro. As getting data from word has format issue, can you able to export word file to text file and later pull data to excel one by one. Can you look at it please?

  49. #49
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: support to capture specific data from Word to Excel using VBA Automation

    Open up one of your files in Word, export it as a text file and post it.

  50. #50
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    I tried to upload it, but it's over 100 KB and our forum rule doesn't allow it

  51. #51
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Hi Paul,

    I tried to verify code and swap couple of codes and it ran. Below are the findings

    For Test 2 file. it got stopped at below code

    Please Login or Register  to view this content.
    WkSht.Cells(r, 5).Value = Split(strTxt, vbTab)(1)
    Please Login or Register  to view this content.
    . I couldn't able to verify much after that

    Enclosed files for your reference
    Attached Files Attached Files

  52. #52
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    For Test 1 file, It ran but duplicated and pasted after the mentioned data. It trimmed part # to six digits

    Enclosed file for your reference
    Attached Files Attached Files

  53. #53
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Quote Originally Posted by priyadharshane View Post
    For Test 1 file, It ran but duplicated and pasted after the mentioned data. It trimmed part # to six digits
    That's hardly surprising, as the only examples you gave in your original BOE files had 6 characters there. Once again, it's your specifications that were the problem. Change:
    .Text = "([0-9]{1,})[!^13]@| ([A-Z0-9]{6})*([0-9.]{4,5} %)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*^13"
    to:
    .Text = "([0-9]{1,})[!^13]@| ([A-Z0-9]@>)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*^13"
    Last edited by macropod; 06-23-2019 at 08:47 AM.

  54. #54
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @ Paul,

    Sorry if i'm not clear. I never had asked for part number in E column as it is inputted by me.

    All I need is Macro needs to pull value of Inv value, Freight value, Insurance, Misc. Charges, Exchange rate (AK2 to AK6) which is at beginning of all BOE

    Only thing issue with it is doesn't have same format always. It has some minor changes

    Next is I need macro to pull different taxes which is below every part (Part # increases or Decreases with respect BOE as well as Part Characters bound to change)

    Duty rate (basic duty) to column S, Social welfare tax to column U and IGST to column AE. And yes, there have formats issue which dynamic

    Reason for this macro need is it take hrs to find tax for the specific part from word file and paste it in excel.

    When I put your macro and run , I noticed that it deleted that data in word file and extracted the mentioned data alone. But it didn't bring it excel correctly.

    If you open up, test 1 macropod file, If you drag and see at column E, S, U and AE. All data has been pasted after Row 98 i guess.

    Can you help with above requirement please? Hope I'm clear with specific needs of this macro
    Attached Files Attached Files

  55. #55
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Quote Originally Posted by priyadharshane View Post
    I never had asked for part number in E column as it is inputted by me.
    That is false. Your attachments to post #1 clearly indicate the part number is to be captured.
    Quote Originally Posted by priyadharshane View Post
    All I need is Macro needs to pull value of Inv value, Freight value, Insurance, Misc. Charges, Exchange rate (AK2 to AK6) which is at beginning of all BOE
    The macro already does that.
    Quote Originally Posted by priyadharshane View Post
    Next is I need macro to pull different taxes which is below every part (Part # increases or Decreases with respect BOE as well as Part Characters bound to change)

    Duty rate (basic duty) to column S, Social welfare tax to column U and IGST to column AE. And yes, there have formats issue which dynamic
    The macro already does that.
    Quote Originally Posted by priyadharshane View Post
    When I put your macro and run , I noticed that it deleted that data in word file and extracted the mentioned data alone.
    Nothing gets deleted from the Word files. If you open them after the macro has finished running, you'll see theyre unchanged.
    Quote Originally Posted by priyadharshane View Post
    But it didn't bring it excel correctly.

    If you open up, test 1 macropod file, If you drag and see at column E, S, U and AE. All data has been pasted after Row 98 i guess
    Well, if the data start being input after row 98, that's because you're running the macro on a worksheet that already has cells in use above that (empty cells can be flagged by Excel as in use if they havent been deleted properly). You should run it on a clean worksheet.

    Your latest attachment suggests you also want vcode, PO No., Part name, Unit price, Qty., and so on. Since you didn't ask for those, they weren't coded for. I'm done wasting my time trying to solve your problem when you can't be bothered specifying the parameters correctly.
    Last edited by macropod; 06-23-2019 at 06:10 PM.

  56. #56
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Your latest attachment suggests you also want vcode, PO No., Part name, Unit price, Qty., and so on. Since you didn't ask for those, they weren't coded for. I'm done wasting my time trying to solve your problem when you can't be bothered specifying the parameters correctly.[/QUOTE]

    As I mentioned earlier, All i need is Tax portion for the parts and AK row values. PO No., Part name, Unit price, Qty., and so on are not required as I in put it

    Thank you so much for your expertise and support. I'm really sorry not mentioned my requirement clearly at first point

    I cleared the cell and ran test 1 file, It's working as I requested.

    I ran more BOE's with it and it's causing debug at below codes. Enclosed data's for your review

    Please Login or Register  to view this content.
    WkSht.Cells(r, 1).Value = Split(strTxt, vbTab)(0)
    WkSht.Cells(r, 5).Value = Split(strTxt, vbTab)(1)
    WkSht.Cells(r, 19).Value = Split(strTxt, vbTab)(2)
    WkSht.Cells(r, 21).Value = Split(strTxt, vbTab)(3)
    WkSht.Cells(r, 31).Value = Split(strTxt, vbTab)(4)
    Please Login or Register  to view this content.
    If it's working on all these BOE. I guess this is it and Macro will be closed. Please support

    Thank you so much again!!
    Attached Files Attached Files
    Last edited by priyadharshane; 06-23-2019 at 11:53 PM.

  57. #57
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Paul

    Any luck on post# 56 please?

  58. #58
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: support to capture specific data from Word to Excel using VBA Automation

    Administrative Note:

    Unfortunately, it has come to our attention you have violated Rule #8 of our Forum RULES:

    Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

    All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

    Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offence could lead to a permanent ban, so do take this caution to heart.

    Post your question in a public thread and our many contributors will come to your assistance, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  59. #59
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Quote Originally Posted by priyadharshane View Post
    Any luck on post# 56 please?
    Read the last sentence in post 55.

  60. #60
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    @Paul,

    Sir, I never asked for vcode, PO No., Part name, Unit price, Qty., and so on.

    If you run files which I enclosed in Post# 56 you can understand it. As I said, All I need is Macro needs to pull value of Inv value, Freight value, Insurance, Misc. Charges, Exchange rate (AK2 to AK6) which is at beginning of all BOE and Duty rate (basic duty) to column S, Social welfare tax to column U and IGST to column AE.

    If you can run mentioned macro and able to capture all above data on all BOE which I enclosed on Post#56 that's all I need. I dont want macro to capture any additional data

    Part# (which has Alphabets, symbols and numbers in it), Taxes with percentage. I guess if you look at this, it will resolved immediately

    Thank you sir!!
    Last edited by priyadharshane; 06-24-2019 at 10:13 PM.

  61. #61
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    See post # 59.

  62. #62
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Sir, you mean to say that you will no longer support. Is that right?

    Atleast, can you run data before coming to a conclusion?

  63. #63
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: support to capture specific data from Word to Excel using VBA Automation

    The support you get here is free and entirely discretionary. Macropod has made it clear that he has no intention of helping further for the reasons clearly given. Please do not press him further.

    You will have to wait and hope that another VBA expert is prepared to offer support. Thank you for your patience.

  64. #64
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Interpretation of Word Macro

    I need below code to get modified to capture

    Text which as PK_2314K161 (Total Characters is 11), 1186878 (total Characters 7), 9R2613 (Total Characters 6). As of now, it's capturing 1186878 & 9R2613

    Also, need to capture if it's in second line as well. As of now, it's capturing if it's in one row

    173 73079290 ADAPTER AS.-ELBOW 90 DEG (NON ELECTRICAL) (50 NOS)
    | 1068715

    .Text = "([0-9]{1,})[!^13]@| ([A-Z0-9]@>)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*^13"

    .Replacement.Text = "\1^t\2^t\3^t\4^t\5^p"
    Last edited by priyadharshane; 06-25-2019 at 08:29 PM.

  65. #65
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: support to capture specific data from Word to Excel using VBA Automation

    Cross-posted at: https://www.msofficeforums.com/word-...ord-macro.html
    Kundly read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html

  66. #66
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Good morning all,

    Can you guide on below, thanks

    I need below code to get modified to capture

    Text which as PK_2314K161 (Total Characters is 11), 1186878 (total Characters 7), 9R2613 (Total Characters 6). As of now, it's capturing 1186878 & 9R2613

    Also, need to capture if it's in second line as well. As of now, it's capturing if it's in one row

    173 73079290 ADAPTER AS.-ELBOW 90 DEG (NON ELECTRICAL) (50 NOS)
    | 1068715

    .Text = "([0-9]{1,})[!^13]@| ([A-Z0-9]@>)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*([0-9.]{4,5} %)*^13"

    .Replacement.Text = "\1^t\2^t\3^t\4^t\5^p"

  67. #67
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96
    Quote Originally Posted by macropod View Post
    Cross-posted at: https://www.msofficeforums.com/word-...ord-macro.html
    Kundly read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html
    Sir, I have not changed my requirements and specifications any of the time. I have forwarded 4 word files. It works in two files and not others. It needs guidance and some modifications. Please see post 56 for your reference

  68. #68
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: support to capture specific data from Word to Excel using VBA Automation

    This has nothing to do with changing requirements. Please read Forum Rule #3:

    3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.
    Here is a link to all of our rules so that you can refresh your memory: https://www.excelforum.com/forum-rul...rum-rules.html

  69. #69
    Registered User
    Join Date
    01-24-2019
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    96

    Re: support to capture specific data from Word to Excel using VBA Automation

    Hello all,

    Any support to make mentioned macro work on enclosed BOE's which causes error please?

    Appreciate if you could resolve it once for all

    Thank you!!
    Attached Files Attached Files

  70. #70
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: support to capture specific data from Word to Excel using VBA Automation

    @priyadharshane

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel - Word Automation - Copying data
    By Michael Staveley in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-03-2017, 04:30 AM
  2. Class does not support Automation or does not support expected interface
    By ccs_1981 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2015, 05:57 AM
  3. runtime 430 error- Class does not support Automation or does not support...
    By kimcole5 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2012, 01:27 PM
  4. Excel 2007 -- Object does not support automation
    By rchiu5hk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2009, 02:52 AM
  5. [SOLVED] importing Word table data into Excel - solutions for automation
    By Han in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2006, 07:30 PM
  6. [SOLVED] Automation of Excel Data to Word (ref: Galimi answers)
    By Adam in forum Excel General
    Replies: 3
    Last Post: 02-28-2005, 01:06 PM
  7. Data Automation from Excel to Word
    By Adam in forum Excel General
    Replies: 3
    Last Post: 02-22-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1