Michael Maddison
michael at ddisolutions.com.au
Thu Jan 5 17:40:11 CST 2006
Thanks for the replies + code sample. My client is a project manager for a large manufacturer, he is trying to get a budget up for this project to go ahead. He is aware that any $ figure I give him is an estimate. The company in question will only work on fixed price quotes (if we get that far). As my company is taking all the risk I will make sure my quote is... ahem... favourable ;-))) Hence my question. Looking more closely at the word docs I see that there are multiple tables and they are in different formats. Also they start out as columns for yes/no but they then degenerate into free text areas where the headings are in the same cell as the text... hmmm, not so good. I may need to some manual input I think. cheers Michael Maddison DDI Solutions Pty Ltd michael at ddisolutions.com.au Bus: 0260400620 Mob: 0412620497 www.ddisolutions.com.au -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Janine_docsonline Sent: Thursday, 5 January 2006 6:23 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] (Fwd) Re: Extract data from .doc into db Love the humour. Don't know if this is off base. There is a convert table to access merge thingy in Word 2003 does that help? I was going to show a client who had access how a table could be merged back to Access? Are the word documents in table? Just a suggestion only. Cant you charge him when you're done? If it takes 10 hours charge out US$55 PER HOUR (that seems fair). Conver the tabular text to tables and then use Word 2003 merge option to turn into fields or just import into Access from the table fields. I may be way off base but just a thought.... ----- Original Message ----- From: "Bryan Carbonnell" <carbonnb at sympatico.ca> To: "access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Thursday, January 05, 2006 11:46 AM Subject: [AccessD] (Fwd) Re: Extract data from .doc into db > Should have gone to the list too. > > Bryan > ------- Forwarded message follows ------- > On 5 Jan 2006 at 11:08, Michael Maddison wrote: > >> I've been asked how much $$$ to extract data from a series of word docs. >> Each record is made up >> of 3 docs. There are 80 records (240 docs). The docs are quite large >> 20+ pages each, with questions >> and either ticks or X's in a tabular format. There are no fields. >> I've never done this so my question is how easy is it to code something >> like this or should I just >> employ a temp to slog through it? > > It depends. Are all the docs the same format? The same number of > tables, columns, rows, etc? Is the marking consistant to indicate the > choice? > > If the answer is yes, then it should be fairly straight forward. Here > is some code (written in Word, so you will have to translate into > Access :)to give you an idea of what would be involved in reading the > cells. > > Sub test() > > Dim doc As Document > Dim tbl As Table > Dim lngRow As Long > Dim lngRowCount As Long > Dim strCol2 As String > Dim strCol3 As String > Dim bolYes As Boolean > Dim bolNo As Boolean > > Set doc = ActiveDocument > Set tbl = doc.Tables(1) > > lngRowCount = tbl.Rows.Count > lngRow = 1 > > > Do While lngRow <= lngRowCount > 'Get the values in the "choice" columns > strCol2 = tbl.Cell(lngRow, 2).Range > strCol3 = tbl.Cell(lngRow, 3).Range > 'Strip the trailing 2 characters asc(7) & asc(13) > If Len(strCol2) > 2 Then > strCol2 = Left$(strCol2, 1) > Else > strCol2 = "" > End If > If Len(strCol3) > 2 Then > strCol3 = Left$(strCol3, 1) > Else > strCol3 = "" > End If > > 'Check to see if first "choice" column is "checked" > If LCase(strCol2) = "x" Then > bolYes = True > End If > 'Check to see if second "choice" column is "checked" > If LCase(strCol3) = "x" Then > bolNo = True > End If > 'Store the Values in your table > > 'reset flags > bolYes = False > bolNo = False > 'increment row counter > lngRow = lngRow + 1 > > Loop > > Set tbl = Nothing > Set doc = Nothing > > End Sub > > A couple of assumptions here: > > This is a single table with a minimum or 3 columns, where the choices > are in columns 2 & 3 which are all marked with an x (upper or > lowercase doesn't matter) > > HTH, > > -- > Bryan Carbonnell - carbonnb at sympatico.ca > Earth is the insane asylum for the universe. > > > ------- End of forwarded message ------- > -- > Bryan Carbonnell - carbonnb at sympatico.ca > Needing someone is like needing a parachute. If he isn't there the first > time, chances are you won't be needing him again. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com