Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-export _many_ trans listings (Access '97)

    I'm working on a program that extracts a class of transaction information from an ODBC connect to an Access table, organized by {NAME, TRANS AMT}. I've made it to where I have created the extract and can export the _whole_ table as an Excel spreadsheet, but have hit a brick wall with the next step.

    Now I need to _automatically_ split out individualized transaction-listings from that extract and export them as Excel spreadsheets which can then each be attached to individual emails (i.e. "Your transactions this month were...").

    Is there a way to programmatically loop through the complete extract, splitting off individuals' exports as the program goes? I'm in about 6" too deep here.

    Thanks much.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto-export _many_ trans listings (Access '97)

    If I understand you correctly, you have an Access database with a linked table (non-Access?) from which you are extracting data so that you can export it to Excel. When you say you have created the extract, are you appending data from your linked table into an Access table or a query and then exporting that to Excel?

    If you have all the transactions in a table or query, you can use another query to filter the items down to those for a single NAME and export the results of that query as well. It's perfrectly possible to create a parameter query to filter for each name, one at a time, from code.

    Are you asking how to do that or do you need help automating it? Did you want to do this from Access or from Excel? Since you listed Access 97 as your version, I assume the answer is from Acces, but it never hurts to ask. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-export _many_ trans listings (Access '97)

    Charlotte said:
    >...or do you need help automating it?

    I apologize for not being more clear; It is the automation part that has me flumuxed.

    What's happening - at a higher level - is that the transactions reside on another system, linked though ODBC so that Access sees the transaction source as a table of many different classes of transactions. I have already made the query that will extract out the single class of transactions that we need to report on. The next high-level step is to spin off each persons transactions into an outside file in Excel format, each of which can then - by someone else's program - be _attached_ to an email to that person.

    In other words, each person get's an email - each month - with their transactions and _only_ their transactions.

    I've have made a query which I can use to extract a single person's transactions, my brick wall is automating it so that the operator doesn't have to extract out the individual transaction listings manually. Besides which, this seems like something that I ought to know, just on principle, if I want to call myself a VBA developer.

    I hope that this clears matters up.

    Thanks for the help.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-export _many_ trans listings (Access '97)

    If you already have a query that extracts ONE person's transactions, I assume that you imbedded criteria in a query to do this. You then export the query results to Excel.

    If you are kicking this process off with a form, try adding a field (it could be hidden) to the form to hold the current user's name. Next, change the criteria in the query to use the form field's contents as the criteria. For example:

    Forms!frmEmailTransactions!CurrentUser

    Then, if you have a table of the usres, walk through the table and, for each user, put that user's name in the form field. Do a TransferSpreadsheet using the query as the source table. Imbed the user's name somewhere in the Excel spreadsheet name. The query will export the transactions for the current user to an Excel spreadsheet. For example:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qryCurrentUsersTransactions, Me.CurrentUser & "'s Transactions.xls", True

    In the above example, you export to Excel97 the results of running your query (qryCurrentUsersTransactions) and create a file named "abcd's Transactions.xls" where abcd is the name of the current user.

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-export _many_ trans listings (Access '97)

    Okay...that's the line I was thinking along...

    Anybody have a good example of "For Each..." loop using records? I've got a programmatically created listing of the names included in the transactions listing along with the number of transactions in this month and would like to do something like

    FOR EACH RECORD in tbl_namesWithTransThisMonth
    strSQL = "SELECT .... WHERE [tbl_allTrans].[name] = [tbl_nameWithTransThisMonth].[name]
    INTO tbl_forExport009"
    DoCmd.runSQL strSQL
    Transfer.Spreadsheet... '<- output table to be attached - later - to indiv's email
    Next RECORD

    Please remember...the above is just a _rough_ example to let you know what I'm thinking. I'm still at the "I know that it can be done, but I sure don't know how" stage.

    Thanks.
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

Posting Permissions

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