Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access????
Insert a TOC from Word into Access???? Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Go Back  Xtreme Visual Basic Talk > > > > Insert a TOC from Word into Access????


Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2004, 05:44 PM
GusB GusB is offline
Newcomer
 
Join Date: Feb 2004
Posts: 3
Question Insert a TOC from Word into Access????


Does anyone know how to automatically insert a table of contents (the data in the field in word) into Access through VBA?? Either by specifying a file name or through an active word document???

Thanks in advance,

Gus
Reply With Quote
  #2  
Old 02-15-2004, 07:09 PM
GusB GusB is offline
Newcomer
 
Join Date: Feb 2004
Posts: 3
Default Re: Insert a TOC from Word into Access????

I've used some sample code
from the office automation and this seems to be doing the trick first time -
however whenever I try it a second time, I get a run-time error of '462' -The remote server machine does not exist or is unavailable. If I stop the
code and kill any instances of WINWORD.EXE currently running as processes,
for some reason I can then run the code again, but it thinks that the
document that I'm trying to open is already open (but if I open a read only
copy the code works again and again). Please help!!

Below is a sample of the code that I'm using in access.....

Function Test(docName)

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim excelApp As Excel.Application
Dim excelBook As Excel.Workbook
Dim acApp As access.Application
Dim acDB As database, acTab As TableDef, acFld As Variant
Dim SQL As String

Const DB_Text As Long = 10
Const FldLen As Integer = 255

If Dir(docName) = "" Then
MsgBox Path & " isn't a valid path!"
Exit Function
Else

' Create new hidden instance of Word.
Set wordApp = New Word.Application
Wait (100)
Set wordDoc = wordApp.Documents.Open(fileName:=docName)

Wait (100)
'wordApp.Visible = True
wordDoc.TablesOfContents(1).IncludePageNumbers = False
Wait (1000)
wordDoc.TablesOfContents(1).Range.Select
Wait (1000)
Word.Selection.Copy ' This is where the 'Debug' option takes me to
when the code fails.....
wordTOClen = wordDoc.TablesOfContents(1).Range
wordDoc.Close wdDoNotSaveChanges

wordApp.Quit
Set wordApp = Nothing

Wait (100)
Set excelApp = New Excel.Application

Set excelBook =
excelApp.Workbooks.Open(fileName:="c:\TOC_Converter.xls")

Wait (100)

excelApp.Visible = True
'excelBook.Worksheets("Import").Cells(1, 1).Select
excelBook.Worksheets("Import").Activate
excelBook.Worksheets("Import").Range("a1").Select
Wait (100)
'Excel.Selection.Paste
Excel.SendKeys "^v", 10
Wait (100)
'excelBook.Worksheets("Output").Cells(1, 1).Select

excelBook.Worksheets("Output").Activate
'excelBook.Worksheets("Output").Range("a1:b300").Select
'Wait (100)
'Excel.Selection.Copy
excelBook.Worksheets("Output").SaveAs fileName:="c:\output.xls"
'excelBook.Worksheets("Import").Activate
'excelBook.Worksheets("Import").Cells(1, 1).Select
excelBook.Close xlDoNotSaveChanges

excelApp.Quit
Set excelApp = Nothing

' Get Database object variable.
Set acDB = CurrentDb

' Create new table.
Set acTab = acDB.CreateTableDef("Import")

' Create field in new table.
Set acFld = acTab.CreateField("F1", dbText, FldLen)
acTab.Fields.Append acFld
Set acFld = acTab.CreateField("F2", dbText, FldLen)

' Append Field and TableDef objects.
acTab.Fields.Append acFld
acDB.TableDefs.Append acTab

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"Import", "C:\output.xls", False, "output!A1:B300"

Kill "c:\output.xls"
End If
End Function
Reply With Quote
  #3  
Old 02-26-2004, 11:35 AM
mdmilner mdmilner is offline
Regular
 
Join Date: Jun 2003
Posts: 58
Default usercontrol = False

For Word you will need to include this:

wordApp.usercontrol = True

For Excel it's:

excelBook.usercontrol = True

The explanation I found a few weeks ago from another helpful soul was that by default, opening MS Applications like word/excel in VBA does not give the user full control over the application, specifically including shutting it down.

Drop these lines in above what you'd expect - before any .Quit or closes.

Mike
Reply With Quote
  #4  
Old 02-26-2004, 10:15 PM
GusB GusB is offline
Newcomer
 
Join Date: Feb 2004
Posts: 3
Default Access Automation

Thanks that'll work too - instead of that I referenced all copy and paste references (that was what was causing the problem) to the object.range rather than the application.copy or application.paste keys.....
Reply With Quote
  #5  
Old 02-27-2004, 12:36 PM
mdmilner mdmilner is offline
Regular
 
Join Date: Jun 2003
Posts: 58
Default usercontrol = true

Gus,

I took at look at your application and rewrote portions of it. Unfortunately, I didn't really have the time to do it right - I think it might be possible to remove the entire xls step by using a Dictionary object to save the contents of the TOC.

As an intermediate offer, I wrote this - it throws out the SendKeys (bad, very bad thing), copies the word TOC, drops it into an xls, identifies how many rows you have, then uses a recordset object to add the field contents into a table in Access.

Drop this into an AccessDB and it'll run as is, if you have the DAO3.6, Word & Excel references added (you probably did). While yes, using Tabledefs would work and would be an easy way to specify the field lengths in your table, it's a little easier to just make a table with a few fields in it as Text and all at 255 characters.

I don't think I removed all of the now unneeded variables but this should point you in a good technique direction. Routine handles (clumsily) whether the TOC values are larger than Access can handle (255 char) and splits them into segments. A better routine would also look at complete words and not split any. I didn't have one handy atm.

Mike

Code:
Function Test11(docName) Dim wordApp As Word.Application Dim wordDoc As Word.Document Dim excelApp As Excel.Application Dim excelBook As Excel.Workbook Dim objSht As Worksheet Dim fld As Variant Dim acDB As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String, strVal As String Dim x As Integer Const DB_Text As Long = 10 Const FldLen As Integer = 255 Set acDB = CurrentDb ' Use any tblName - make the first field 256 character string field ' If length of TOC exceeds 256, add additional fields to table strSQL = "SELECT * FROM Table1" Set rs = acDB.OpenRecordset(strSQL, dbOpenDynaset) If Dir(docName) = "" Then MsgBox path & " isn't a valid path!" Exit Function Else ' Create new hidden instance of Word. Set wordApp = New Word.Application Set wordDoc = wordApp.Documents.Open(FileName:=docName) 'wordApp.Visible = True wordDoc.TablesOfContents(1).IncludePageNumbers = False With wordDoc.TablesOfContents(1) .UseFields = True .Range.Copy End With wordDoc.UserControl = True wordDoc.Close wdDoNotSaveChanges wordApp.Quit Set wordApp = Nothing Set excelApp = New Excel.Application Set excelBook = excelApp.Workbooks.Open(FileName:="c:\TOC_Converter.xls") Set objSht = excelBook.Worksheets("Import") excelApp.Visible = True With objSht .Range("a1").Select .Paste For Each fld In .Range("A1:A300") 'Testing Each field value If Len(fld.Value) < 1 Then Exit For Else With rs .AddNew For x = 0 To Int(Len(objSht.Cells(fld.Row, 1)) / 255) strVal = objSht.Cells(fld.Row, 1) .Fields(x).Value = Left(objSht.Cells(fld.Row, 1), 255) If Len(strVal) > 255 Then strVal = Mid(strVal, 256, Len(strVal) - 255) End If Next x .Update End With End If Next End With excelApp.UserControl = True excelBook.Close xlDoNotSaveChanges excelApp.Quit Set excelApp = Nothing End If Set rs = Nothing Set acDB = Nothing End Function

As a note: If you'd like to clear the contents of the Access table first, run something like this:

Code:
strSQL = "DELETE * from tblName" DoCmd.RunSQL strSQL
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit a Word doc. starting in Access Keesb Word, PowerPoint, Outlook, and Other Office Products 2 01-04-2004 06:21 AM
Insert Data from Word to Excel andrewj Word, PowerPoint, Outlook, and Other Office Products 1 12-16-2003 10:00 AM
Moving OLE Object from ACCESS to WORD smorty Word, PowerPoint, Outlook, and Other Office Products 1 02-16-2003 01:53 AM
Word - MailMerge with VB, Word 2000 and an Access 97 database Agamemnon1 Word, PowerPoint, Outlook, and Other Office Products 0 08-27-2002 07:33 AM
Access DB to Word Form mattg Word, PowerPoint, Outlook, and Other Office Products 4 08-14-2002 11:20 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access???? Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access???? Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
 
Insert a TOC from Word into Access????
Insert a TOC from Word into Access????
 
-->