Excel to Access Macro

[deepakpar]

Hello,

For a project of mine, I would need to get an excel attachment from Outlook. put it in a folder automatically (I have managed to do that). The thing I am struggling with is to transfer that info from Excel to a Access database.

If somebody knows how to do it or has already done this, I would appreciate the help!

Thank you!

[hansup]

You can use the TransferSpreadsheet method to import from Excel to Access. This example stores Excel data in an Access table named tblFromExcel. The spreadsheet has field names in the first row, so I used True for the HasFieldNames parameter. Look at Access' TransferSpreadsheet help topic for more details.
DoCmd.TransferSpreadsheet acImport,,"tblFromExcel","D:\wip\temp.xls",True

[deepakpar]

Hansup, thanks a lot for that! Appreciate the reply. It was a mistake when I said "an" attachment. I would keep getting excel attachments from that email and would keep saving those files in the folder. Is there any way a macro can scan the folder for new files and update records in the database?

Thank you

[hansup]

Sorry, I overlooked the word macro. I don't know how to accomplish what you want with a macro. But that doesn't mean it's impossible. Just that I don't know how. I almost never use macros.

[deepakpar]

No prob Hansup, thanks a lot for the reply!

[Imdabaum]

There is a way to check if files exists, but that requires that you can ensure that the files are uniquely named. If at any point your collegues are going to email you the attachment as Book1.xls then that will break the code.(Not that Book1.xls is a bad title.. just implying that if they send you a file with the default saved name a lot, you'll have multiple Book1.xls files and it won't work.)

http://www.techonthenet.com/excel/formulas/dir.php

This explains how you can use the Dir([path,[attribute]]) function. You can use this to identify whether a file exists or not. If Dir returns an empty string then the file does not exist and you would know to add it.