importing data from excel with a watched folder

[Guest]

I have 2 issues i would like to solve. First case... I have an excel file
emailed to me on a a daily basis. I then open the file in excel and run a
macro against it to format it and save it. The next thing i do is open an
access db and run a macro that imports the excel spreadsheet. I would like ot
automate this a little bit more. Can someone give me some guidance on how to
automate this? ideally i would like to save the file to a folder and do the
formating and importing all from within access. I can setup an email rule to
save the file when recieved.

2nd case, is there a way to make a watched folder in access without a lot of
programming on my part? ideally, when the access db is openned and a form
opens it would check for a file in a specific folder. if the file is new it
woudl import otherwise it would ignore and keep going. Any thoughts? Thanks.

 

[Guest]

1st Question

If you mean by Macro, there is VBA code in Excel you are using to format the
worksheet, you can do that from Access using Automation. That means opening
an instance of Excel from Access and running the code there.

2nd Question

It will take some coding, but not that much. If you have a form that stays
open all the time (it can be hidden), use the form's Timer event to check for
the file. The problem is, how do you know it is new?

 

[Guest]

Ok, what is meant by "automation". You mean just put in some vba code to
launch excel and run the macro? I can probably launch the application but how
do i start the macro?

For the second part, i can just delete the file after import so anytime
there is a file there it imports it. I will always have the original in email.

thanks.

 

[Guest]

It is a bit more complex than that. The macro code would be run from Access.
Automation is what used to be called COM. You use the CreateObject method
to create an instance of Excel. Then you open the workbook, and manipulate
the worksheet using VBA. It takes a bit of coding, but you can do anything
to a worksheet in Excel you can do from within Excel.

Yes, you could delete the file once you have processed it.