Transferring Word to Excel -

[PandaBear]

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

[mdmackillop]

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.

[PandaBear]

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.

[snb]

See http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[mdmackillop]

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.

[macropod]

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.

[Thyme2Cook]

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.

[macropod]

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.

[Thyme2Cook]

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.

[macropod]

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).

[Thyme2Cook]

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.

[macropod]

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

[Thyme2Cook]

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.

[macropod]

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.

[Thyme2Cook]

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

[macropod]

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