Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-28-2017, 10:04 PM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default Macro for exporting Word data to an excel register

Hi everyone,



I have been searching for quite some time on how to solve this problem and create a macro that will export certain information from a word document and place it into an excel register.

I have found information on how to do this at techrepublic:
http://www.techrepublic.com/blog/10-...an-excel-sheet

However I cannot get the macro to work. It returns two errors.

The first is "5941: The requested member of the collection does not exist"

The second is "Runt-time error '91': Object variable or With block variable not set"

The macro code in use is:

Code:
Private Sub Export_Click()
'Transfer a single record from the form fields to an Excel workbook.
  Dim doc As Document
  Dim strCARNumber As String
  Dim strLocation As String
  Dim strDate As Date
  Dim strRisk As String
  Dim strCategory As String
  Dim strDetails As String
  Dim strInitiator As String
  Dim strResponsible As String
  Dim strDepartment As String
  Dim strDateComplete As String
  Dim strDateVerified As Date
  Dim strSQL As Date
  Dim cnn As ADODB.Connection
  'Get data.
  Set doc = ActiveDocument
  On Error GoTo ErrHandler
  strCARNumber = Chr(39) & doc.FormFields("txtCARNumber").Result & Chr(39)
  strLocation = Chr(39) & doc.FormFields("txtLocation").Result & Chr(39)
  strDate = Chr(39) & doc.FormFields("txtDate").Result & Chr(39)
  strRisk = Chr(39) & doc.FormFields("txtRisk").Result & Chr(39)
  strCategory = Chr(39) & doc.FormFields("txtCategory").Result & Chr(39)
  strDetails = Chr(39) & doc.FormFields("txtDetails").Result & Chr(39)
  strInitiator = Chr(39) & doc.FormFields("txtInitiator").Result & Chr(39)
  strResponsible = Chr(39) & doc.FormFields("txtResponsible").Result & Chr(39)
  strDepartment = Chr(39) & doc.FormFields("txtDepartment").Result & Chr(39)
  strDateComplete = Chr(39) & doc.FormFields("txtDateComplete").Result & Chr(39)
  strDateVerified = Chr(39) & doc.FormFields("txtDateVerified").Result & Chr(39)
  'Define sql string used to insert each record in the destination workbook.
  'Don't omit the $ in the sheet identifier.
  strSQL = "INSERT INTO [Sheet1$]" _
    & " (CAR_No., Category, Department, Site_Location, Initiator, Responsible_Party, Date_Raised, Details, Priority, Date_completed, Date_verified)" _
    & " VALUES (" _
    & strCARNumber & ", " _
    & strLocation & ", " _
    & strDate & ", " _
    & strRisk & ", " _
    & strCategory & ", " _
    & strDetails & ", " _
    & strInitiator & ", " _
    & strResponsible & ", " _
    & strDepartment & ", " _
    & strDateComplete & ", " _
    & strDateVerified _
    & ")"
  Debug.Print strSQL
  'Define connection string and open connection to destination workbook file.
  Set cnn = New ADODB.Connection
  With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=(withheld);" & _
      "Extended Properties=Excel 8.0; HDR=No"
    .Open
    'Transfer data.
    .Execute strSQL
  End With
  Set doc = Nothing
  Set cnn = Nothing
  End Sub
ErrHandler:
  MsgBox Err.Number & ": " & Err.Description, _
    vbOKOnly, "Error"
  On Error GoTo 0
  On Error Resume Next
  cnn.Close
  Set doc = Nothing
  Set cnn = Nothing
End Sub
I have withheld the data source information.

The debug highlight is coloured red.

Any help on this would be greatly welcomed. I cannot find an answer online and it has been quite some years since I last used VB.

If more information is required I can attached the files that I am trying to export from and export to.

Last edited by Seb; 03-28-2017 at 10:06 PM. Reason: correcting web link
Reply With Quote
  #2  
Old 03-28-2017, 11:57 PM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

See, for example: https://www.msofficeforums.com/word-...html#post59665
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 03-29-2017, 12:24 AM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default

Thank you for the reply Macropod.

I will have a read of this in more detail.

What I am looking for is a macro that I can assign to a button in th word document that will take data from specific fields and populate an excel document register, placing the appropriate data into specific columns at the bottom of the excel table. The word form has a lot more information than the register requires.

Would it be better if I posted the two files on here so you can see the information I need to export from word?
Reply With Quote
  #4  
Old 03-29-2017, 05:02 AM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

As coded, the macro in the link harvests data from all formfields and content controls. It would be a trivial undertaking to replace the loops with iterative code that just looks at nominated formfields and outputs their results to nominated cells in the Excel workbook.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 03-29-2017, 03:44 PM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default

I've had a look at the macro that you have linked, and I couldn't even begin to modify it, I don't have the coding knowledge I'm afraid.

I have linked the two files that I am trying to use together. The word document is the form that we use for a detailed record of corrective actions. The excel workbook is the register that contains brief details of each corrective action in a table for tracking purposes. The table has formula and conditional formatting for ease of use.

The details that I want to export from the word document are:

Excel table column - Word Object name - Field type
Car No. - txtCARNumber - Rich text
Site Location - txtLocation - Dropdown
Date raised - txtDate - Date picker
Risk Priority - txtRisk - Dropdown
Category - txtCategory - Rich text
Details - txtDetails - Rich text
Initiator - txtInitiator - Rich text
Responsible Party - txtResponsible - Rich text
Department - txtDepartment - Dropdown
Date completed - txtDateComplete - Date picker
Date verified - txtDateVerified - Date picker

I am looking to export these specific details from the word document once they are completed and place it at the bottom of the excel table in the CAR Register worksheet such that the table can be extended with conditional formatting and formula, and the pivot graphs/tables can be updated without having to redraw the data ranges. There are also quite a few form fields in the document that I don't want exported. These are there so I can protect the document from changes while still allowing data input.

From what I have read I understand that there are possible issues using text fiends and dropdown/date picker content control for exporting. If this is an issue, I can easily change all the fields to rich text.

Could you help with creating a workable macro to complete this data transfer on a button click? The button is located at the end of the word document, called "Export to register" and is coloured dark blue. I lack the knowledge in Visual basic to create or even modify the code.

If it's also possible, is there a way of the macro completing a search of the table for the CAR No. and updating a previous entry that has this CAR No. with new details?
Reply With Quote
  #6  
Old 03-29-2017, 05:15 PM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi Seb,

The first issue you have is that, contrary to what you initially posted, your document has content controls, not formfields. That would account for the "5941: The requested member of the collection does not exist" message.

As your code also contains an 'End Sub' instead of 'Exit Sub' before 'ErrHandler:', I'm surprised it even got to the error handler.

As for the extraction itself, are you wanting to collect these data from a series of documents, or only from one document at a time? For collection from a series of documents, an Excel macro that automates Word would be preferable to one driven by a userform. After all, what happens if one runs the Export_Click twice, or not at all?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 03-29-2017, 07:10 PM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default

Hi Macropod,

Thank you for the small explanation on the error and on the code issue, it helps understand the problems. That code was copied and then modified from the link I provided. However this is the first time I've ever tried to create these types of documents in word and excel, and the first time I've attempted to create a macro like this. I am learning as I go.

As for the extraction, I only want to obtain this information from the one document at a time that I am currently filling out, not from multiple ones simultaneously. The register only needs to be updated as a corrective action is generated which can sometimes be weeks or months between each one. This is why I was looking for the word document driving the updating of the excel workbook.

With the Export_Click - If the macro is able to ensure only unique entries based on the CAR No. field, then a second click of the Export button would simply update the same row. Is this possible?

Cheers,
Seb
Reply With Quote
  #8  
Old 03-29-2017, 10:20 PM
gmayor's Avatar
gmayor gmayor is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,346
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

You may find http://www.gmayor.com/ExtractDataFromForms.htm useful?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 03-29-2017, 10:46 PM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try the attached. I couldn't get your code to work, even after changing the formfield references to content control references, so I've taken an entirely different approach.

Issues with your coding included mismatches in the filename, content control titles and Excel column names, all of which I could correct for. The stumbling-block for me was the Excel column names with spaces etc. in them; I just don't know the SQL syntax well enough to code for that.

As coded the macro in the attached document outputs the data to the next available row in the Excel workbook. Where you have empty rows in the used range (as in your workbook), that means the data will be added after those rows. Deleting any unused rows beforehand will resolve that.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 03-29-2017, 11:18 PM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default

Thank you Macropod.

Your code looks incredibly simple!

I will try this tomorrow at work and see if it works. If I understand your post correctly, I may need to change the column titles to remove the spaces in them. I don't mind doing that at all.

I really appreciate your help on this.
Reply With Quote
  #11  
Old 03-30-2017, 12:19 AM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Seb View Post
If I understand your post correctly, I may need to change the column titles to remove the spaces in them. I don't mind doing that at all.
No, you don't need to do anything about the column titles vis--vis my code; the spaces were only an issue for the SQL code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 03-30-2017, 05:34 PM
Seb Seb is offline Macro for exporting Word data to an excel register Windows 10 Macro for exporting Word data to an excel register Office 2016
Novice
Macro for exporting Word data to an excel register
 
Join Date: Mar 2017
Posts: 7
Seb is on a distinguished road
Default

Hi Macropod,

Thank you for the macro. It works flawlessly!

The code is very elegant, and I believe I understand enough of it to modify for future documents and registers.

You've just saved me hundreds of hours of work replicating pre-existing data.

Thank you again.
Reply With Quote
  #13  
Old 03-30-2017, 07:30 PM
macropod's Avatar
macropod macropod is offline Macro for exporting Word data to an excel register Windows 7 64bit Macro for exporting Word data to an excel register Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,729
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

You're welcome.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting multiple data sets from a single Word Form into Excel Tom Saylor Word VBA 6 09-11-2016 05:22 AM
Macro for exporting Word data to an excel register Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet Labyrinth Word 7 07-19-2016 01:35 PM
Macro for exporting Word data to an excel register Exporting Resource Usage data to Excel samg54 Project 1 08-02-2013 05:28 AM
Macro for exporting Word data to an excel register Macro: Exporting Data to a LEGIBLE Excel Spreadsheet jeffcoleky Word VBA 6 05-08-2012 08:24 AM
Macro for exporting Word data to an excel register * Exporting Access Data to Excel djreyrey Excel Programming 1 03-23-2012 10:03 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft