#1
|
|||
|
|||
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 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 |
#2
|
||||
|
||||
See, for example: https://www.msofficeforums.com/word-...html#post59665
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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? |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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? |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
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 |
#8
|
||||
|
||||
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 |
#9
|
||||
|
||||
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] |
#10
|
|||
|
|||
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. |
#11
|
||||
|
||||
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] |
#12
|
|||
|
|||
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. |
#13
|
||||
|
||||
You're welcome.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
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 |
Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet | Labyrinth | Word | 7 | 07-19-2016 01:35 PM |
Exporting Resource Usage data to Excel | samg54 | Project | 1 | 08-02-2013 05:28 AM |
Macro: Exporting Data to a LEGIBLE Excel Spreadsheet | jeffcoleky | Word VBA | 6 | 05-08-2012 08:24 AM |
* Exporting Access Data to Excel | djreyrey | Excel Programming | 1 | 03-23-2012 10:03 PM |