How can I streamline this process?

[Christine]

Hi group,

I hope I picked the right one...I use several Office products for analyzing
metadata, but it seems no one product will do everything. I inherited this
process from someone else, but I'm hoping there is a more efficient way of
doing the same job.

Here's how it goes:

1. Open metadata file in Excel.
2. Copy Column B into Notepad to remove Excel formatting.
3. Copy contents of Notepad into Word.
4. Replace commas with ^p to create a list of words.
5. Save list of words as a text file.
6. Open database in Access and import list of words. The list of words is
always tab-delimited and does not need a primary key.
7. Run Group By query to eliminate duplicate words.
8. Copy and paste new list of words from Access into Notepad and save.
9. Import new list of words.
10. Run query to compare to the master list to get words that are not in the
master list.

It would be nice to:

- be able to import the word lists without having to go through the wizard
(is it possible to make a macro?)

- be able to create a list in Excel instead of having to copy and paste into
Notepad and Word

- any other ideas?

Thanks,
Christine

 

[Beth Melton]

For starters you should be able to import your data directly into Access.
You can use the Text Import or Excel Import wizard to specify only the
columns/fields you want to import and save an import specifications table
for use in subsequent imports and also use it in a Macro (depending on the
version of Access you are using) to help reduce the process to a few clicks.

btw, I'm not sure why you need to strip formatting - this is taken care of
for you when you save as a text file.
~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx

 

[Christine]

Hi Beth,

Thanks for posting a reply.

For starters you should be able to import your data directly into Access.
You can use the Text Import or Excel Import wizard

Yup, I can do this. However, if I were to import the data from Column B
directly into Access, I could not run the Group By query. All the words in
Column B are seperated by commas, which is why they are pasted into Notepad
and then Word to have the formatting stripped. Is there a way to convert all
the comma seperated words to a single column in Access?

In the past I have used the text to columns command in Excel to explode
Column B, but then I have over 20 columns and no way to combine them all into
one column. Cutting and pasting all the columns was extremely time consuming
- I deal with thousands of words from the metadata.

columns/fields you want to import and save an import specifications table
for use in subsequent imports and also use it in a Macro (depending on the
version of Access you are using) to help reduce the process to a few clicks.

Now that sounds handy! I'll look up how to do that. I am using Access 2003
btw.

Best,
Christine

 

[Beth Melton]

Ah, the detail that your column/field contains multiple data separated by
commas helps explain things. I think I know what you meant by "stripping
formatting", you're using the Notepad to prevent pasting the data as a table
in Word. One step you could eliminate is the Notepad. Use Edit/Paste Special
in Word and paste as Unformatted Text.

BUT, you could still do all of this in Access by importing the data to an
Import table and running some code that will split the data into multiple
records. Which I now realize is what you are really asking. ;-) I did a
quick Google Groups search and found an example you might be able to modify
fit your needs. It looks like a matter of changing the table names, field
names, and eliminating code for fields you aren't using:
http://groups.google.com/group/comp...data+multiple+records+commas#da962e3f9788d606

If you need help with the example, post in an Access Programming newsgroup,
they should be able to help you out.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx

 

[Christine]

Hi Beth,

Thanks so much again. I was wondering afterwards if I had explained
something poorly. What a simple fix...I have been using Office for about 10
years now and there's so much that I don't know. I wrote a macro this morning
to take care of the comma replacement.

BUT, you could still do all of this in Access by importing the data to an
Import table and running some code that will split the data into multiple
records. Which I now realize is what you are really asking. ;-)

Yes, sorry for being obtuse That example looks pretty good, I'll give it
a shot tomorrow.
This has really been an educational day. When you mentioned import
specifications, I discovered that I could attach it to a macro. This further
led me to create a switchboard manager, and now I have eight dandy little
buttons so that I can update, view, delete and run the queries on the tables
I need. It's going to save me a lot of time.

Thanks again for all your help Beth!

Best,
Christine

 

[Beth Melton]

Hi Beth,

Thanks so much again. I was wondering afterwards if I had explained
something poorly. What a simple fix...I have been using Office for about
10
years now and there's so much that I don't know. I wrote a macro this
morning
to take care of the comma replacement.

You're welcome.

btw, you can add the following line of code at the beginning of the macro
you created and you can skip the pasting to the Notepad or using Paste
Special manually.
Selection.PasteSpecial DataType:=wdPasteText

Yes, sorry for being obtuse That example looks pretty good, I'll give
it
a shot tomorrow.

No worries on not understanding exactly what you were trying to do at first,
sometimes it takes putting it down on paper, analyzing, and asking questions
to realize your exact question. ;-)

This has really been an educational day. When you mentioned import
specifications, I discovered that I could attach it to a macro. This
further
led me to create a switchboard manager, and now I have eight dandy little
buttons so that I can update, view, delete and run the queries on the
tables
I need. It's going to save me a lot of time.

Hopefully you'll find something that work for you. I'm sure you will since
you've already taken the small tidbit I gave you and discovered you can do
so much more. Good for you!

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx

 

[Christine]

Hi Beth,

I was able to find a pretty cool solution in the Access group. It's here:

http://www.microsoft.com/communitie...370b&tid=3870fe9b-4634-4ac2-9813-78019303e486

 

[Beth Melton]

I figured you would. The solution provided looks similar to the example I
previously cited.

Thanks for the follow-up.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx