I wrote my first Access macro today! Now, I am not a big fan of Access, but unfortunately I have to use it at work because it is the first choice for most people looking for a db (short for "database") program. For most situations, I prefer Excel.
At work I have to import data from Excel to Access. Being as lazy as I am, I always try to find an easy way to do things. Otherwise I have to do it manually three times a day, and we can't have that, can we? Since we are referring to MS Office programs, my first choice is VBA. So I found an Access function that automates importing XL spreadsheets. Here is the syntax:
DoCmd.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
For my purposes, the code is:
DoCmd.TransferSpreadsheet acImport, , "table name", "C:\Filename.xls", True
My .xls files always have headers so the 'HasFieldNames' argument is 'True'.
Now my next task is to create a macro in Excel to pull the spreadsheet from Outlook, format it, then start an instance of Access (always with early binding, of course) and import the spreadsheet! Once that is done I will post the code on the regular site (with the data scrubbed to protect the innocent).
TTFN,
JP
Acknowledgements:
http://msdn2.microsoft.com/en-us/library/aa220766(office.11).aspx
http://www.mvps.org/access/general/gen0008.htm
About JP
Follow Me