[RESOLVED] Pulling data from Word/Excel files from MS Access

[oioioi]

I'm considering designing somewhat cumbersome system (there are some good reasons for this choice) for data gathering.

The scenario:

Word/Excel documents will be filled in with information, such as name, age etc... Is there any way Access can pull this data out of the files and insert it into the database?

Note, I'm familiar with VB 6.0, but have not done any recent VB stuff and thus not familiar with VB .NET.

[RobDog888]

Welcome to the Forums.

Yes, you would need to Add a reference to MS Word or Excel xx.0 Object Library. Then how would the file be identified? GetOpenFile dialog letting the user to choose or read a directory?

[oioioi]

Thanks RobDog888.

I'm thinking Access will process all the word/excel files in one directory/folder. Just process them one by one and move the completed files to a different directory/folder.

[oioioi]

I'm completely new to VBA type programming. Is there anyone who can give me a few more pointers or hints on how to get started.

What does it mean to "add a reference" to "Excel Object Library"?

[DKenny]

What does it mean to "add a reference" to "Excel Object Library"?


In the VBA editor for MSAccess under the Tools Menu select "References".

In the References dialog, check the box beside the "Microsoft Excel x.x Object Library". This will add a refeerence to that library, which in turn will allow you to create and manipulate Excel objects within your Access VBA code.

[oioioi]

DKenny,

Thanks for the pointer. Can you show me some code? Like how to create an Excel Object Library object.

Is there a good place to search for object methods/properties?

I find it incredibly hard to find class information for VB programs as opposed to Java.

[DKenny]

Can you show me some code? Like how to create an Excel Object Library object.

Sure, here some code that instantiates Excel, a workbook and a worksheet.
Sub sample()
Dim appExcel As Excel.Application
Dim wkbSampleBook As Workbook
Dim wksSampleSheet As Worksheet

Set appExcel = New Excel.Application
Set wkbSampleBook = appExcel.Workbooks.Add
Set wksSampleSheet = wkbSampleBook.Worksheets(1)

Debug.Print wksSampleSheet.Name

Set wksSampleSheet = Nothing
wkbSampleBook.Close False
Set wkbSampleBook = Nothing
appExcel.Quit
Set appExcel = Nothing
End Sub

Is there a good place to search for object methods/properties?

I find it incredibly hard to find class information for VB programs as opposed to Java.

The object Browser (F2)

[oioioi]

Thanks DKenny,

I modified the code and it works beatifully.