UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Docs To Excel, Office 2010    
 
   
mike60smart
post Apr 13 2017, 09:47 AM
Post#1


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

Is it possible Export Multiple Word Documents into an Excel Spreadsheet??

Any help appreciated


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
JonSmith
post Apr 13 2017, 10:53 AM
Post#2



Posts: 3,083
Joined: 19-October 10



Super vague dude.

What do you want to 'export'?

Embed the entire file, pull out certain data?

Short answer, perhaps.
Go to the top of the page
 
mike60smart
post Apr 13 2017, 10:56 AM
Post#3


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Jon

Sorry for short vague question but what is required from the Word Document is a table which is on Page 1 which is made up of 2 Columns and 19 Rows.

The Documents are stored in a folder named Applications

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 13 2017, 12:49 PM
Post#4


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

Yeah, if you want specific advice you need to provide details. Based on the info you've posted, I'd say that yes, you should be able to do this.

You say the table is "on page 1" but is the table the only contents of the Word file, or is there text before and/or after the table? Also, you say "multiple" word documents - which documents? Are they all in a single folder, with no other files in that folder? Does the user select them? Do they have a consistent naming scheme?

My recommendation: Take this in steps. First figure out how to work your way through the multiple word files. Once that works, then work on moving the data into an Excel file.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 13 2017, 01:48 PM
Post#5


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Sorry for vague request but the details are sketchy from the person who wants to do this

The actual word document has 2 pages with the first page having a single line of text as a heading
then a table made up of 2 Columns and 19 Rows

The second page is not required to be exported.

As for the naming convention this I will have to find out.

Please bear with me and I will obtain more details before proceeding.




--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 13 2017, 03:53 PM
Post#6


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

Here's a basic structure to get you started. It makes a few assumptions, and you'll need to customize the paths for the Word files and the Excel file, but it should be easy enough to work with. Let me know if you get stuck.

CODE
Public Sub ExportTableDataToExcel()

'   This code requires the Microsoft Excel x.x Object Library

    Dim strFile As String, strPath As String
    Dim xlApp As Excel.Application, xlFile As Excel.Workbook, xlSht As Worksheet
    Dim lngLastRow As Long, intCol As Integer, intRow As Integer
    
'   Start an instance of Excel, and open the workbook we're putting data into
    Set xlApp = New Excel.Application
    Set xlFile = xlApp.Workbooks.Open("H:\The Folder With The Excel File\The Excel File.xlsx")
    Set xlSht = xlFile.Sheets(1)
    
    strPath = "X:\The Folder With All The Word Files\"
    strFile = Dir(strPath & "*.d*")
    
ProcessFile:
'   In the Excel file, find the last row with data on it, so we can append below that point
    lngLastRow = xlSht.UsedRange.Rows.Count + 1
    
'   Open the Word file
    Documents.Open strPath & strFile
    
    With ActiveDocument.Tables(1)
'       Loop through the rows and columns of the word table
'       (Assuming it's always 2x17 and it's the only table in the Word file)
         For intRow = 1 To 17
            For intCol = 1 To 2
'               Note: The replace function removes the odd characters after the actual cell text
                xlSht.Cells(lngLastRow + intRow, intCol) = Replace(.Cell(intRow, intCol).Range, Chr(13) & Chr(7), "")
            Next intCol
        Next intRow
    End With
    
'   Close the Word file
    ActiveDocument.Close
    
'   See if there's another Word file in the folder.  If so, process that one next.
    strFile = Dir
    If strFile <> "" Then GoTo ProcessFile
    
'   Save the Excel file, close it, then quit the instance of Excel
    xlFile.Save
    xlFile.Close
    xlApp.Quit
    
'   Memory cleanup
    Set xlSht = Nothing
    Set xlFile = Nothing
    Set xlApp = Nothing

End Sub


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
JonSmith
post Apr 14 2017, 03:30 AM
Post#7



Posts: 3,083
Joined: 19-October 10



I personally go for solutions involving XML mapping in Word these days, that does require you being in control of the document before any data is filled however and it sounds like someone wants you to clean something up after they made it.

It looks likes Dennis' approach should cover what you need for existing documents.
Go to the top of the page
 
mike60smart
post Apr 14 2017, 08:48 AM
Post#8


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Not having used VBA in Word how would I go about adding a Click Event to my Command Button to run the Code you have provided??


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 14 2017, 09:08 AM
Post#9


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

The way I tested my code was to put the code in a new code module of my Normal template file. Then, I added a button to the Ribbon:

1. Right-click a part of the Ribbon and select Customize the Ribbon...
2. In the big dialog box that opens, select "Macros" in the Choose Commands From combobox.
3. Check the checkbox next to Developer tab in the large box on the right side.
4. Select the Templates section of that tab, then click New Group. Optionally, name the new group something like "My Macros".
5. Select the ExportTableDataToExcel macro in the large box on the left side.

At this point, the macro and the new group are selected in the two large boxes.

6. Click the Add>> button to add the macro to the new group in the Developer tab of the ribbon.
7. Optionally, click the Rename button and give the button a custom image, and a nice name.
8. Click OK.

At this point there's a new button on your Word ribbon under the Developer tab, on the far right.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 14 2017, 09:53 AM
Post#10


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Great That process was simple enough

Now when I click the Command Button I get the following error:-

Attached File  error.JPG ( 59.86K )Number of downloads: 4

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 14 2017, 09:55 AM
Post#11


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

Something happened when you copy/pasted my code, because a bunch of it is missing. In this case, the DIM statements, but you've also removed the bit that opens the Excel file and performs the first Dir() on the folder.

Hope this helps,

Dennis

EDIT: Wait, WHERE exactly is this code? I was running the code from a Word module. Are you running this in Excel?

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 14 2017, 10:33 AM
Post#12


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

My apologies I don't know how I copied that over incorrectly.

I am doing it in Word and Not Excel.


Now when I run the code I get this error:-

Attached File  error.JPG ( 15.33K )Number of downloads: 0


Hit Debug and it highlights this row:-

Attached File  Row.JPG ( 29.58K )Number of downloads: 5

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 14 2017, 10:51 AM
Post#13


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


mike60smart,

At this point, you'll need to check to see which parts of that line of code are valid. Open the Immediate Window (CTRL-G) and while the line of code is still highlighted, try entering stuff like this:

? xlSht.Name
? lngLastRow
? .Cell(intRow, intCol)
? intRow
? intCol
? .Cell(intRow, intCol).Range

I'm guessing one or more of these will return an error. Which one it is will help you figure out what the problem is. For example, maybe the table in Word only has 16 rows, and when intRow gets to 17, the code would likely fail.

Debugging VBA code is not that difficult. Here's a page that can help.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 15 2017, 03:49 AM
Post#14


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Ok tried that and it fails on the following :-

Attached File  error.JPG ( 25.9K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Apr 15 2017, 04:05 AM
Post#15


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Please ignore my last as for some unknown reason it is now doing what I need.

Many Many thanks yet again

I am sure I will be back with more questions

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 17 2017, 09:28 AM
Post#16


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

If it happens again, go into Debug mode, and try that bit in the Immediate Window again. If you see that error when referencing the .Cells(intRow, intCol) value, do this:

CODE
? intRow
? intCol
? strFile

That will give you the row number, column number, and filename that is currently being worked on. Stop the code, and open that word file to see if the table in that file has a problem in that cell. Based on the error, I'd guess that the Word file's table isn't 17 rows tall.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 17 2017, 09:50 AM
Post#17


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

Too true it was 21 rows

Thanks


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
doctor9
post Apr 18 2017, 09:08 AM
Post#18


UtterAccess Editor
Posts: 17,310
Joined: 29-March 05
From: Wisconsin


Mike,

Okay, that's weird. My original code is locked into 17 rows, but your post previous to that says 19 rows. Not sure how I screwed that up. fundrink.gif

So, we now know that the Word files have a varying number of rows in their tables. Let's try changing the For loop for the rows:

CODE
For intRow = 1 To .Rows.Count


That's untested air-code, but it should work. Here's hoping.

Dennis


--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
mike60smart
post Apr 18 2017, 09:17 AM
Post#19


UtterAccess VIP
Posts: 12,145
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dennis

No it was my mistake, I said originally that it was 17 and I mistakenly typed 19 in the Code.

I then found out that they are all fixed at 21 Rows so works just fine

Thanks again.
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd July 2017 - 05:36 PM