Excel Merge with Access (Access/Excel 2003)

Topic

New Reply

The basic project is I want to email each manager 2 spreadsheets with the list of assets they own. The spreadsheets have combo boxes to tell me if it is available, etc….

This is a Two Part Project:

1. I need to make Excel spreadsheets for 120 managers.
2. I need to email them the spreadsheet.

I have a list of Asset that are owned by Managers. Potentially there are 120 Managers.
a. I have 2 Queries: (q1) Computers that have logged on within 30 days. (q2) Computers logging on longer than 30 days
Question: What is the best way to create multiple tables with assets belonging to each manager. Currently I proposed running a query that will prompt the manager’s name, then it will produce the results. I envision a way to run a macro that will take the manager, produce the list, copy that data to a predestined excel sheet, and save the file as %managerlastname%_01.xls and %managerlastname%_02.xls for the 2 different queries.

Does this make sense???

Reply | Quote

Replies

You can create Visual Basic code that

– Opens a recordset on the table that lists the managers.
– Loops through the records of this recordset (each record represents a manager).
– Changes the SQL of the first query so that it selects the records for the manager.
– Exports the first query to an Excel workbook named after the manager.
– Changes the SQL of the second query so that it selects the records for the manager.
– Exports the second query to the same workbook (or to a different one if you prefer.
– Uses Outlook to send an e-mail with the workbook(s) as attachment.
– Closes the recordset.

You can find the ingredients for all this by searching this forum. If you would like more help, we’d need to have detailed information.

Reply | Quote

More detailed information:

5 Cols:

Serial Number | User Name | Location | Manager | Email |

The recordset I want to build is all the equipment serial numbers belonging to one Manager.

Once determined, I would like to take the first three columns and place it in a pre-programmed Excel sheet in the first three columns and starting at row 5 for example.
The excel sheet should then be saved as %managername%.xls

If I get this far, I can do the Emailing manually…

Reply | Quote

I have attached a text file with sample code that you can use as starting point.
You’ll have to subsitute the correct names for:
– the table (tblData), and if necessary the exact field names.
– the path and name of the “preprogrammed” workbook (C:ExcelPreprogrammed.xls).
– the folder where you want to save the individual workbooks (C:Excel).

The code needs a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor.
The code uses late binding for Excel, so there is no need to set a reference to the Excel object library.

The code could be expanded to send e-mails automatically if you’re using Outlook.

Reply | Quote

THAT IS SO SWEET!!!!

So this worked FLAWLESSLY!!!!

I dont know where you guys are, but I’ll buy you a Starbucks, or Dunkin Donut card or send you some pizzas!!!!… seriously.. send me a email.

Here is the next part:
1. I have the 150 Excel files formated as First Last.xls
2. I use Outlook 2003 and I have access to a generic MailBox (Asset Inventory).

Step1: Lookup Manager’s Name in a table: “ManagerList” and Extract the Email Address
Step2: The FROM Field has to be active and the Exchange name: Asset Inventory must be selected.
Step3: The new Message now needs to open the corresrponding file from c:Excel%First Last%.xls and attach it.
Step4: Finally, the New email should be SAVED… We don’t want to send it automatically….

Thanks

Reply | Quote

Is the manager name stored in the ManagerList table as one field containing “Last First”, or as separate LastName and FirstName fields?

Reply | Quote

Is the manager name stored in the ManagerList table as one field containing “Last First”, or as separate LastName and FirstName fields?

One field: [First {space} Last]

Reply | Quote

Thanks (sorry about the confusion between First Last and Last First).

I have attached a sample procedure. Just like with the previous one, you’ll have to substitute the correct names for:
– The folder (C:Excel)
– The table (ManagerList)
– The manager name field (Manager)
– The e-mail field (Email)

Reply | Quote

This is working GREAT!!!

However there is a problem with this line:
‘ Look up e-mail address
strEmail = DLookup(“Email”, “ManagerList”, _
“[Manager]=” & Chr(34) & strName & Chr(34))
When I run it, I get an error “Invalid Use of Null”

If I take it out, it creates 700 emails in draft, but the Email Address is missing…

I created a “test” table called ManagerList and it has 2 Fields {Manager} and {Email}

What is the error pointing to???

Thanks
Leonard

Reply | Quote

I’d set a breakpoint at the beginning of the code (click in a line and press F9).
When you run the code, it will pause at the breakpoint. You can press F8 repeatedly to execute the code step by step.
You can see the value of variables by hovering the mouse pointer over a variable in the code.
Try to find out where the code fails.

Reply | Quote

I did something similar in that I set the cursor to a spot and ran the script to that cursor.

What you suggested got the same result.

It is having issues getting the email address from ManagerList.

So the script jumps from line 34 to Line 60 and the error code is produced.

Thanks

Reply | Quote

I’m afraid it’s not possible for me to know what goes wrong without seeing a stripped down copy of the database. See post 401925 for instructions.

Reply | Quote

I fixed it…

Basically, the XLS Data has to have a corresponding Name and Email Address:

So the “TargetList” with the Manager and Email HAS to have a Corresponding Excel File for it to work…

Thanks

Reply | Quote

Er yes. I thought that you would have taken care of that as a matter of course…

Reply | Quote