Source for Access Queries 3

[SaintAugustine]

Does anyone know of a good source if info on how to make "Advanced" Access Queries? I'm trying to make an Access database which populates itself by taking certain data from standardized-format Excel files.

I've made Excel macros before (though I'm far from an expert), but this is my first Access macro. All the info I can find seems to be targeted toward more basic procedures than what I'm trying to do.

Thanks!

 

[dougcranston]

SaintAugustine,

You can accomplish this by using ADO, and VBA or VBScripting.

"Export data from Excel to Access (ADO)"

http://www.erlandsendata.no/english/vba/adodao/exportado.php

Importing a Spreadsheet into Access

http://databases.about.com/library/weekly/aa123100a.htm

Hope these help out.
DougCranston

 

[sdraper]

Here is my favorite Access site. You should find some helpful info there.

http://www.mvps.org/access/

sdraper

 

[dougcranston]

SaintAugestine...
SDraper does point out a GREAT Access site. Used it in the past.

DougCranston

 

[SaintAugustine]

Cool, thanks - these are a good place for me to start, I've made a little bit of progress on the macro already.

I should have been more specific when discussing what I plan to do - I want to write a macro to get information from about 200 Excel files on a monthly basis. Importing all of these Excel files as tables would soon make the database too huge to handle, right?

I'm trying to find a way to tell database to open the Excel file and take just the info it needs, put that info into the database, and then close the file.

For example, say that each file is a spreadsheet of an employee's monthly expense report. The spreadsheets are standardized so that cell C2 *always* contains the total amount of expenses for that month. In this example, I'd like to have Access open the file for each employee, take the info in C2 and then add it to the "Monthly Total" column in the "Employee Expenses" database.

Is this a pipe dream?

(this isn't actually what I'm doing - this is just an example to illustrate the processes involved)

 

[pbrodsky]

It is actually pretty easy to do that type of thing. Look at the sample code on

http://www.mvps.org/access/

under Modules/Sample Excel Automation for an example.

 

[dougcranston]

SaintAugustine,

Answer is yes.

Can be done, I am sure using VBA from Access. Build a table with the filenames and have the VBA loop through the files and pull in the data.

Can be done using VBScript. Both

Go to Helen Feddema's site.

http://www.helenfeddema.com/CodeSamples.htm

Look at CODE19.zip Although it shows how to read from EXCEL and Write to Outlook, it will provide you some of the basics on what you want to build. Also, click on

http://www.helenfeddema.com/access.htm

and look at the EXCEL OBJECT accarch66.zip

I have a script that works the other way, taking selected data from an Access MDB and writing it to Excel, so I am convinced it is possible.

Did alot of searching at this forum, and another, found enough samples to build my own and run with it.

Hope this helps.
DougCranston

 

[SaintAugustine]

Ok, these are good responses, thanks - I appreciate everyone's help.

These sources and some digging have given me pretty much everything I need. My final question was if it was possible to get this data without importing the Excel files as a table, and it looks like the DoCmd.TransferSpreadsheet function will get me partially there.

 

[dougcranston]

SaintAugustine,

Some more references..

VBScript and Excel:

http://www.greggriffiths.org/webdev/both/excel/activex.html

How to import data from Excel (Note: Server may be down, have tried accessing this site for other purposes today.)
p2p.wrox.com/archive/vba_excel/2001-06/17.asp

Hope this helps.
DougCranston