Consulting

Results 1 to 16 of 16

Thread: Transferring Word to Excel -

  1. #1

    Transferring Word to Excel -

    I have been trying to find a solution to this problem all day. Yesterday I learned about Word Forms and how they can be used to transfer files to Excel. I thought my inventory dream had come true, but after making the Word Form and the Excel Sheet, I have not been able to get everything to work as I would like. I keep getting a "Compile Error: User-Defined type not defined. "Sub GetFormData ()" is highlighted in yellow and Dim wdApp As New Word.Application" is highlighted in blue.

    I have been watching this video on youtube by Neil Malek published on Jan. 22, 2016 and saw Paul Edstein's comments and links. I have tried copying and pasting his macro from those links into Excel following the directions on the video for everything else. I have tried changing the CCtrl to FmFld. I found another macro that Paul had posted on either this site or another site and tried putting that into Excel and I get the same error message.

    As you may be able to tell from my attachments, I am a vintage seller on ETSY. I need to have a form that I can print and put on the boxes once I process a product so that I can find it in my warehouse (garage for now), but I also need to be able to track the collective costs and income for taxes. Up until now, I have been doing the forms by hand and then inputting everything again into an Excel spreadsheet, but it is such a slow process and I have to keep fumbling through papers to see if I have completed all the steps needed before posting onto ETSY.

    I have attached my Word Form with info filled out for one item that has not been sold and the Excel sheet that I created today. I have also attached a Word file that has the Macro that I used in the Excel sheet.

    If I should have put this into a thread that was already going, I apologize. All the threads I found regarding this were more than one-year old and I was not getting anywhere trying the fixes shown own them.

    Thank you for any help you can give me.

    Angela Bjornstad

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I don't see the necessity of using both Word and Excel. Personally I would create the "form" in excel and save the data to another worksheet.
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Quote Originally Posted by mdmackillop View Post
    I don't see the necessity of using both Word and Excel. Personally I would create the "form" in excel and save the data to another worksheet.

    Mdmackillop,

    Thank you for your response. I want to do it as I stated because I need to be able to print the form out with all of the data and then attach it to the outside of the shipping box. Once I process an item, I box it so that I know how much shipping will cost. Also, it is easier store boxes than items sitting on a shelf. Also, it speeds up locating the item once sold. I have not figured out how to make a form, similar to the one I attached to this question, that I can then print. I need the items in a regular spreadsheet for accounting purposes. I am just trying to streamline the processing and I thought that using the Word Form and transferring to Excel would be the right way to go about this. It seemed so easy, but I just can't figure out why I keep getting that error message. I have been watching videos to make forms in Excel, but I am not seeing what I need. I would like to have a separate page (form) for each item that can be printed. So far in Excel, I see what they are calling a fillable form is really just a spreadsheet with taller row heights.

    If you can explain to me, or direct me to instructions, on how to go about making a fillable sheet in Excel and then have it go into a regular spreadsheet, I would very much appreciate it.

    Thank you again for your response.

  4. #4

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You can certainly produce something that looks like your Word form in Excel. If you really need it in Word, it is easier to export data from Excel to Word Bookmarks to produce the document. You can input data either in a spreadsheet or a Userform as per snb's article. In both cases you can set data choices where appropriate to minimise errors. I would never consider entering the data in Word as the starting point.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    I've only just discovered this thread!

    One wonders why no-one saw fit to tell the OP to do as the note at the top of the macro says...

    All that was needed was to go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Hey there Paul,

    I have been successfully been using this code few years now even though it was meant only to be an interim solution. Now the code has started throwing a "Run-time error 4248: Application-defined or object-defined error."
    The error is thrown when it finishes pulling the results from the first word doc into Excel when attempting to close that doc and move on to the next step in the process. I'm not in any way well versed in VBA and am grateful to you as the OP.


    I have enabled the "Microsoft Word 16.0 Object Library".


    This is the code as it now stands after researching the issue and attempting to troubleshoot the issue. You'll note that I adapted it to use form fields instead of content controls. It appears to be failing at the below point in the code.
    "Next
    .Close SaveChanges:=False"
    We're finally implementing a permanent solution so I only need to get this to work for 2 more months. As it stands, the error is only allowing 1 document at a time and the volume is too great. Even for 2 months.

    Sub GetFormData()
    'Note: this code requires a reference to the Word object model
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim FmFld As Word.FormField
    Dim strFolder As String
    Dim strFile As String
    Dim WkSht As Worksheet
    Dim i As Long
    Dim j As Long
    
    'Defines "strfolder" as the appropriate folder from which you want to pull your word documents from
    'It does so using the "GetFolder()" separate function below
    strFolder = "T:\Uploads"
    
    'If there is nothing in the specified folder, this line tells the macro to stop
    If strFolder = "" Then Exit Sub
    
    'Set data to dump on sheet number one regardless of what sheet in the work book you are on
    Set WkSht = Worksheets(1)
    
    'Sets "strFile" as the name of the first word doc in the chosen folder path
    strFile = Dir(strFolder & "\*.docx", vbNormal)
    
    'Prevents screen from refreshing during Macro to make it more efficient (???)
    Application.ScreenUpdating = False    'moved this down (if Exit Sub is executed, it'll never be set back to true).
    
    'The below while loop grabs a word doc in the specified folder, pastes all the form field resulsts of a word doc in a specified row/column based on the column header in row 1
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    
    strFile = Dir(strFolder & "\*.docx", vbNormal)
    While strFile <> ""
      i = i + 1
      Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
        j = 0
        For Each FmFld In ActiveDocument.FormFields
          j = j + 1
          WkSht.Cells(i, j).Value = FmFld.Result
        Next
        .Close SaveChanges:=False
      End With
      strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    PS I owe you a for drink for all the gratitude I've received for this solution.

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    Unless MS has messed up a recent Office update (always possible), I suspect a problem has developed with your Office installation. You might try repairing it.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    The problem was reported to me by anther user and I confirmed it on my machine. Nevertheless I tried repairing the installation and there is no change.

    I first suspected a Windows update and the only clue I found was this comment about Excel 4.0 macros.

    https://learn.microsoft.com/en-us/of...annel#excel-14
    Version 2202: February 28
    Version 2202 (Build 14931.20120)
    Feature updates

    Excel

    • Excel 4.0 (XLM) macros will be disabled by default to improve security for Microsoft 365 customers.: To help protect customers, Excel 4.0 (XLM) macros will be disabled by default in Microsoft 365. We encourage you to migrate these macros to the latest version of Microsoft Visual Basic for Applications (VBA). Learn more
    How can I put in an error check to skip to the next word doc and continue the process again? Then finally do something like quit the Word Application so that there are no invisible documents open in the background.

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    This has nothing to do with Excel 4 macros. Nor is likely to be anything to do with an update that old - the update may be so recent that MS hasn't documented whatever problems it has caused.


    As for quitting Word, wdApp.Quit is already in the code.

    For problem solving purposes, though, you may want to insert
    wdApp.Visible = True
    after
    Application.ScreenUpdating = False
    and change
    Visible:=False
    to
    Visible:=True
    so that you don't need to use Task Manager to kill any orphaned Word sessions that might be left running in the background (possibly with open documents).
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    Quote Originally Posted by macropod View Post
    This has nothing to do with Excel 4 macros. Nor is likely to be anything to do with an update that old - the update may be so recent that MS hasn't documented whatever problems it has caused.
    That is what I thought, but thanks for confirming.

    As for quitting Word, wdApp.Quit is already in the code.
    The trick here is that the VBA errors out before getting to adApp.Quit. I'm trying to work out an If error check in the middle of the For loop. Or since there are 91 Form Fields, when j=91 maybe have it exit the loop and continue on with the process. But I am not writing it correctly. My thinking is that I can have it run through the 91 form fields and then close the document and continue on with the rest of the word docs in the folder.

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    Quote Originally Posted by Thyme2Cook View Post
    The trick here is that the VBA errors out before getting to adApp.Quit.
    I am aware of that but, since the error apparently occurs at
    .Close SaveChanges:=False
    a crashing macro will leave both a running Word session and an open document. The changes I suggested were to facilitate manually closing the document and terminating the Word session. Moreover, they will allow you to check which particular document is having issues and perhaps identify whatever is peculiar about it. If, for example, your document has formfield checkboxes, you'll need something like the code I posted here: http://www.vbaexpress.com/forum/show...l=1#post291047
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #13
    It turns out that a change on 9/1 to the VBA in the Word document's template is causing the issue. I had added some code to manage the dropdown values of the last form field. When I attempted to import form data that predates the change there is no error. I believe the error was occurring at the "Next" part of the VBA because the only difference between an 8/31 version and a 9/1 version is that VBA code on the word template. Though, I can't seem to identify what the underlying difference actually is, because the form field bookmarks are the same and the number of form fields are the same.

    My suspicion is that the FmFld is the object definition that keeps getting broke once it reaches the last form field. If that's not the case and there's no fix, then I'll just call it good since I have a solution. Even if we have to go back to a previous version of the Word Template.

    If, for example, your document has formfield checkboxes, you'll need something like the code I posted here: http://www.vbaexpress.com/forum/show...l=1#post291047
    Even though my original code was handling the checkboxes fine I used yours since I had already tweaked it so much. This is the code that I went with since I had been importing checkboxes as 1 or 0 this whole time and I couldn't accept the TRUE or FALSE for the checks.

            With wdDoc
                j = 0
                For Each FmFld In .FormFields
                    j = j + 1
                    Select Case FmFld.Type
                        Case Is = wdFieldFormCheckBox
                          If FmFld.CheckBox.Value = True Then
                            WkSht.Cells(i, j) = 1
                          Else: WkSht.Cells(i, j) = 0
                          End If
                        Case Else
                        WkSht.Cells(i, j) = FmFld.Result
                    End Select
                Next
            End With
    Thanks for the help.
    Last edited by macropod; 09-20-2022 at 02:31 PM. Reason: Fixed code formatting

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    Quote Originally Posted by Thyme2Cook View Post
    It turns out that a change on 9/1 to the VBA in the Word document's template is causing the issue. I had added some code to manage the dropdown values of the last form field. When I attempted to import form data that predates the change there is no error.
    Without seeing what specific changes you made, I really can't comment on their impact.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #15
    Quote Originally Posted by macropod View Post
    Without seeing what specific changes you made, I really can't comment on their impact.
    I'm chalking this one up to gremlins and since I now have a solution I'm good. Here's the code that I added to the Word template that I assume caused the issue in case anyone is interested. Restoring a previous version of the template that does not have this code resolved my issue.

    Dim InAcRsn As String
     
    '''''Manages "Inactive_Reason" dropdown.
     
    With ActiveDocument.FormFields("Inactive_Reason").DropDown.ListEntries
        .Clear
        .Add "    "
        .Add "Deceased"
        …
    End With
    If InAcRsn = "" Then
        ActiveDocument.FormFields("Inactive_Reason").Result = "    "
    Else
        ActiveDocument.FormFields("Inactive_Reason").Result = InAcRsn
    End If
    Till next time

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,407
    Location
    There's nothing obviously wrong with that code, though it could be improved:
    With ActiveDocument.FormFields("Inactive_Reason").DropDown.ListEntries
        .Clear
        .Add "    "
        .Add "Deceased"
    End With
    If InAcRsn = "" Then InAcRsn = "    "
    ActiveDocument.FormFields("Inactive_Reason").Result = InAcRsn
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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