Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Merge with Access (Access/Excel 2003)

    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???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

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

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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)
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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>post 401925</post#> for instructions.

  13. #13
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

    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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Excel Merge with Access (Access/Excel 2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •