Convert tables in Word 2003 to Access 2007 (1 Viewer)

Zlatkodo · Apr 3, 2010

I have a lot of tables in Word 2003 (total number of rows exceed 8 000). I want to convert these tables into Access 2007 database for further use in a simple application in Visual Basic. Can someone explain how I can do this, ( please, notice that I am a beginner in Access 2007).
Thanks in advance. Zlatkodo.

vbaInet · Apr 3, 2010

1. Copy and paste the table into Excel
2. Copy and paste the contents from Excel into a table in Access. You would need to highlight the whole table (just like you do in Excel, click the top left corner) before pasting.

OR

2. Import that Excel file into your db using the Import wizard.

Zlatkodo · Apr 3, 2010

Thank you. I almost managed to do it. There is an error in one column that I must somehow eliminate.
In this column in the Word document are strings, something like 1-7, or 1-11 or 1-10.
As soon as I copy-paste in Excel I already have a error. I get something like a date (in my language) for example: 1.lis or 1.pro .... When I import to Access I get something like: 40360 or 40483 etc.. It seems to me that I need somehow to validate columns in Word before copying.
How can I do it? I want to get into Access exactly the same string as in Word.
Thanks.
Zlatkodo

vbaInet · Apr 3, 2010

1. Instead of using Paste, try PASTE SPECIAL in Excel when you right-click.
2. Select VALUES in the window that pops-up and paste it.

If that doesn't work, select that whole row in Excel and convert it to Date. Right-click > Properties > General Tab > select Date > OK.

Zlatkodo · Apr 3, 2010

1. Instead of using Paste, try PASTE SPECIAL in Excel when you right-click.
2. Select VALUES in the window that pops-up and paste it.
That doesn't work.
If that doesn't work, select that whole row in Excel and convert it to Date. Right-click > Properties > General Tab > select Date > OK.
This I do not understand. I've tried:
- highlight this column,
- right click,
- format Cells
but I failed.
Whether this can be done somehow in the beginning (in the Word)?
Zlatkodo

vbaInet · Apr 3, 2010

Send some sample data in word format.

Zlatkodo · Apr 3, 2010

vbaInet said:
Send some sample data in word format.

Here is part of tables. Problem is with "Span" column.
Zlatkodo

Zlatkodo · Apr 3, 2010

vbaInet said:
Send some sample data in word format.

And also, here is picture of Excel table.

vbaInet · Apr 3, 2010

Just had a look. This is what you do:

1. Open a blank Excel sheet.
2. Select the whole sheet (Ctrl + A) or selecting the top left hand corner
3. Righ-click > Format Cells > select Text > OK
4. Now use the Import Wizard in Access or try pasting the contents.

Zlatkodo · Apr 4, 2010

Sorry, it doesn't work.
I've found another (weird but effective) way .
I have changed the string in the problematic column (but only in the first row) in a Word table in this way: I replaced the string (for example, 1-10) with a string (for example, 110 or whatever) ..
Then I copied the whole table and paste directly into the Access database (not Excel).
I've got everything OK except for one column, but only in the first row. Then I made a manual correction, ie instead of string 110 I returned string 1-10, and now everything is fine.
Can someone explain this relatively simple but unusual procedure?
I know this is not the right way and that there must be an exact way to do this but I do not know it. It seems to me that the program defines a format of one of the column as a date (instead of me). Why ?
Zlatkodo

vbaInet · Apr 4, 2010

The method of changing the whole excel sheet to Text before pasting is actually a tried and tested method. There must be something you missed along the way.

You can also copy and paste it into Notepad, then import from notepad into your table.

If you want this to work when pasting/importing into your db then you must create the fields before import and make that problematic field Text.

Convert tables in Word 2003 to Access 2007 (1 Viewer)

Zlatkodo

Registered User.

vbaInet

AWF VIP

Zlatkodo

Registered User.

vbaInet

AWF VIP

Zlatkodo

Registered User.

vbaInet

AWF VIP

Zlatkodo

Registered User.

Attachments

Zlatkodo

Registered User.

Attachments

vbaInet

AWF VIP

Zlatkodo

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread