Solved

Access 2010 > Visual Basic Express 2010 > Word 2010

Posted on 2012-04-09
34
666 Views
Last Modified: 2012-06-22
Hi all.  My end result will be a Word 2010 macro enabled document.  Here is my dilema...don't know how to get there :-).  Here is my steps taken thus far:
Create a database in Access and import it from VBE 2010.  This will become the dataset/data table in VB.  From VB I want to send specific data to bookmarks in Word 2010 documents.

This is basically a program that has a user input data (name, address, etc.) and clicking a button such as create document will open the word document and fill the information into the existing bookmarks based on the previous input in VB.

Probally a rediculous question but I am so new I am still a fetus....thanks all.
0
Comment
Question by:noticetoowner
  • 18
  • 12
  • 4
34 Comments
 
LVL 21
ID: 37825144
Are you using Access just as the back end to a VB.net app?  

Note: What you want to do can all be done from Access using VBA.

See: ACC2000: How to Use Automation to Find Bookmarks in Word 2000 Documents

I find using a mail merge to be much easier that bookmarks.

see:  How to use automation to run Word 2000 mail merge from Access 2000

The code can be easily adapted to VB is you must use VB.net
0
 

Author Comment

by:noticetoowner
ID: 37825283
A large majority of my clients do not have Access.  I am only using it as a starting point for the databases I need to import into VB Express.  I want the db's to be empty for them to input their information into.  I am sure I can probally do this without using Access but the tutorial I found referenced Access so that is where I started.

So I imported the db's and was able to add to the dataset and datatables in VB.  I now want to take that data and export it to word documents already formatted.  Again a tutorial I found said to do it via "bookmarks".  Two of the major problems I had with it was 1.) the code word.application did not work during the debug nor did 2.) code for bookmark.  I have referenced the word object 14 COM so I am not sure where to go from here.

Thanks so much for your time and I appreciate your help.  I have been searching and searching for a solution but am stuck.
0
 
LVL 21
ID: 37825853
Ah .... I think what you are saying is that you are not really using Access 2010. You are using a ACE (.accdb) format database or the older JET (.mdb) format a the back end for \ your VB app.

What you are wanting to do is commonly referred to as Word Automation.  With Word Automation yu ban perform mail merges, create a Word document, edit a Word document, edit a document and replace bookmarks, etc. Basically anything a user could do and more.  

When you use automation you7 work the the apps object model. For what you want to do you will need to learn how to use the Word Object Model (click here).  

See:
How to automate Word from Visual Basic .NET to create a new document

Word Automation using VB.NET - Part I

Office Automation in VB.NET.


FWIW:
If this were my project I would use a mail merge. This eliminates the need to write the code to replace all the bookmarks. It also make updating adding or removing merge fields a requires little to no code changes.  This is not true with bookmarks. With bookmarks you are having to writ a lot of code to do exactly what a mail merge already does. I would only consider using bookmarks if you  have some really advanced data manipulation that must be done that the built in mail merge can't handle. Even in that case I would do the process of the data before I created the merge source data so I could use a mail merge.   With Word Automation, i have yet to find a situation where replacing bookmarks is better than a mail merge.
0
 

Author Comment

by:noticetoowner
ID: 37825931
Here is the overall situation;  I will be writing a program to "create" documents that are nothing more than forms with fill ins (I already have the forms with control contents in them).  I currently have these on one document with over 100 pages per state (each state requires different documents - with each doc at a state).  I had to do this b/c I had all of the files in a folder that linked the info accordingly but when I tried to copy and rename this folder it lost its links.  No one could help me with a macro.  I plan to write this program (with help - either paid or unpaid) so I can send it to a client and they can open, fill in project info and it populate the documents.  So I started searching to learn as I have a deadline date of 12/12, so I  have time.  I have not used mail merge simply b/c I have used this format forever (tables with data linked as a copy and special paste) but I will explore not only the mail merge but any and everything that helps me learn.

I so appreciate your advise and help and I am not sure if you are able to be "hired" for a project or hourly but I am looking for some help and am able to pay for it.  I only want to learn this so I can help clients who might run into issues, the only way to help is to know from the ground up.  I have hired 4 programmers to write the previously mentioned macro and none worked and I am out a lot of funds, so you have no idea how much I appreciate your help.  I know I am not educated for this(degree in business, not computer science) but I do learn quick and retain so I plan to help anyone I can in the future, especially with a macro that MS should have available in their drop down as far as I am concerned.  A copy and paste should not require so much frustration.

FYI I even learned how to write my own DB in VB after contacting you today, without Access, imagine that......thanks again, you have been so nice and helpful....:-)
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37825971
You're welcome. I am glad I can assist.

<< ...especially with a macro that MS should have available in their drop down as far as I am concerned.  A copy and paste should not require so much frustration.>>>
Not sure I follow to what you are referring.

The additional  explanation of what you are doing helps. I do lots of contract management software. I generate lot of documents where I must "fill in the blanks".  One example is done all with Access. It is done with Access Reports. The document text is stored in the back end database.  I have built my own mail merge functionality so I do not need Word. I do this because not all of my clients own any of the Microsoft Office products. They don't need top purchase Access or Word to use my Access application.
0
 

Author Comment

by:noticetoowner
ID: 37826058
I meant that putting together a format should be able to copy and re named easily, but ok....I might have  overstood my boundaries.......

I would like to speak with you further re the documents without Access, if we could communicate outside of this forum that would be great.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37833008
0
 

Author Comment

by:noticetoowner
ID: 37833079
I have been looking at many links to try and find my answer.  I am not finished with all the links thus far, but yes, it appears I still need help.  Basically I have set up a dataset or datatable in VB where user inputs data.  I want to copy whatever the data is (column 1, row 2) to a bookmark in Word "dummytext".  I know this will be a ton of work on my end, as I will have over 2000 documents with over 5000 bookmarks.  But for now I just need basic code and I will edit it per document.  I need to know how to "identify" the data in the table and copy it to the bookmark.  Does that make any sense?  Like I said, I am a complete fetus when it comes to this...sorry.  Thanks for your help.
0
 
LVL 21
ID: 37834083
<<Does that make any sense?>>
In my experience, No it does not.

<< I know this will be a ton of work on my end, as I will have over 2000 documents with over 5000 bookmarks. >>
This will also probably be a nightmare to support and upgrade.  


My recommendation is this:
Learn to do the database portion first. Get you applicait0on built where you can add/edit/delete the data in the tables.  Once you have learned the basics you will then be ready to tackle the Word automation
0
 

Author Comment

by:noticetoowner
ID: 37834330
I have my data tables set up.  I can add, edit, delete records, etc.  I have VB set to open a Word document.  I just do not know the code to map from the db to the bookmark.  The examples I have found do not work.  For instance the "bookmark" term is underlined in VB so I cannot run it.  The same happened when I used word.Application.  This was code I found on this site.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37834341
>I need to know how to "identify" the data in the table

If I understand that correctly, you can use

DataTable1.Rows(rowindex).Item(columnindex)

to get a value.

>copy it to the bookmark

 oDoc.Bookmarks("full_name").Range.Text = "John Smith"

to set a bookmark
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37834357
You need to download and install correct version of Office PIAs(Primary Interop Assemblies) for your version of Office and reference it in your project before you can use this code.
0
 

Author Comment

by:noticetoowner
ID: 37834557
I referenced my object 14 library as my first step.  Wasn't sure what the PIA was so I downloaded it this morning to my desktop (couldnt find it to reference) but not sure how to set that up.  I went to Project, add new item but I cannot change the file type and it appears the file I downloaded is not one of the types....it seems like every code I copy and paste is not recognized in VB yet it works for others....thanks again for all of your help.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37834670
After adding the reference, you need to add the Imports statement at top. If you want add

Imports Microsoft.Office.Inteop.Excel

at top, you can use all objects directly. Usually, people add

Imports Microsoft.Office.Interop

at top and use Excel.Application to create the object.
0
 

Author Comment

by:noticetoowner
ID: 37834745
VB is underlining "Imports" as a syntax error.  I am wondering if there is something wrong with the VB download and should I uninstall and download again?  I mean that seems like a very straight forward code and it has issues?  It is like that with almost every line of code I do.  Any advise?  Thanks again so much.  I need to figure out how to give points to you also.  I am very new.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37835260
Are you sure you have a VB project template?
0
 

Author Comment

by:noticetoowner
ID: 37835376
I am pretty sure.  I d/l VB 2010 Express directly from MS.  I select new project and choose the windows application and then name the file.  And again my very first step is to reference my object library (14).  I am not sure why I cannot add the PIA file, it is an exe file.  Thanks again codecruiser you have been very helpful and insightful to me.  I will keep plugging away on my end.  I really didn't think it would be this difficult, but then again I have never tried to write a program before.....
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37835433
>I am not sure why I cannot add the PIA file, it is an exe file.

You are supposed to run that file and it would install the PIA DLLs.

Do you see formname.cs or formname.vb in solution explorer?
0
 

Author Comment

by:noticetoowner
ID: 37835512
form1.vb is there and that form opens up with I select new project.  I ran the file (PIA) and it is in my programs files.  So I tried again with the imports code you supplied and it still will not work.  Am I mistaken, or, when I begin to type Imports shouldn't there be an Imports in the drop down that I could choose?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37853640
> when I begin to type Imports shouldn't there be an Imports in the drop down that I could choose?

There should be IF you have added reference to the DLL. Use Add Reference and go to .NET tab then look for Microsoft.Office.Interop.Word
0
 

Author Comment

by:noticetoowner
ID: 37855210
Hmmm, not a listed option in either NET or COM.  Would you mind telling me how to get it added as an option.  I have it on my desktop where I downloaded it.  Thanks CodeCruiser.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37856111
The option is added automatically when PIA is installed by running the downloaded setup. If its not appearing then its not installed properly I think.
0
 

Author Comment

by:noticetoowner
ID: 37856200
Do I need to save it to a folder in VB?  I have it in Programs and on my desktop.  I am going to check the actual file again and make sure it is the correct one.  Thanks for your help.  I have the Microsoft Office 2010 Primary Interop Assemblies, is that the correct one to install?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37866832
It depends on your office version. Do you have Office 2010?
0
 

Author Comment

by:noticetoowner
ID: 37868713
Yes I do Pro Edition, isn't that a laugh....I am such the pro....
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37875957
Ok. Download and install following

http://www.microsoft.com/download/en/details.aspx?id=3508

Then try to add reference.
0
 

Author Comment

by:noticetoowner
ID: 37876774
I downloaded and saved to desktop.  Tried to install but the progress bar goes for about 2 seconds then disappears.  I tried a few times.  No luck.  Wow I am getting so frustrated with my computer I just want to throw it in the street.
0
 

Author Comment

by:noticetoowner
ID: 37878492
Ok there is obviously an issue with my computer if it wont install the msi file. I am trying an alternate route using VB 2010 Express.  I created my form and need to copy the data entered in the textboxes to either a text file within VB, notepad or wordpad. I have code to copy to the clipboard but am having issues pasting it.  I get notepad to open the file but it does not paste.  Any suggestions?  I have been searching and searching for a solution but to no avail.  I really appreciate all of your time and effort.  You need like 10000 points.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37878906
You mean you want to save text from textbox to a text file on disk?

System.IO.File.WriteAllText("path of text file", textbox1.text)


After you run the MSI, go to Assembly folder inside your Windows folder and see if you have any Microsoft.Office.Interop.* files in there.
0
 

Author Comment

by:noticetoowner
ID: 37879005
it won't let me run it. When I try to install it goes for a few seconds (progress bar about 20%) then stops and disappears.  I have searched my entire computer and the only finds are the downloads.  I can download anything else so I dont know what more to do with it.

I guess I have 2 questions about the textboxes. Ideally I would like to add a text form and format my documents there and have everything run in VB but dont know if that is possible.  If not I want to take the text in the textboxes on my form and copy it to Notepad or Wordpad. I assume in Notepad2be I can target the specific location in the document to paste the info to.  I just have no idea where to start with that process.

I have hired a few programmers on the net and have been burned 3 times that is why I am trying to get this going on my own.  I have several clients who are in need of this, weeks ago....

Thanks soooo much!!!!
0
 

Author Comment

by:noticetoowner
ID: 37883908
OK I wiped my computer out, reinstalled windows 7 and office.  I installed the file and yes it is in my assembly folder.  I went to NET reference and cant it isnt in there, but went to browse to get it.  That didnt work either as there are just folders (no files) in the assembly folder (I cant navigate to it like I can from my start menu).  Any suggestions?
0
 

Author Comment

by:noticetoowner
ID: 37883926
giggling....i found it through browsing around, it was in another folder.  Gonna try to work with it now...wheww....learning a bit each day....thanks so much for your help
0
 

Author Comment

by:noticetoowner
ID: 37884070
Hmm, that didnt work.  It appears that it is a "file" in my reference and not a NET.  How do I make them NET if it isn't under my NET choices?  Thanks.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37908613
Have you gone through the list on .NET? Did you sort it by clicking on name column header?

For me, it comes up in .NET list on both VS2008 and VS2010

.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now