Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro - Code ??? (Access 2000)

    Hi,

    I have a table that I run a query on that using only 5 fields from the table. One field in the table is

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Macro - Code ??? (Access 2000)

    What do you want to do with these lists?
    Do you just want to eyeball them or print them?
    Or what?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Code ??? (Access 2000)

    HI,

    I move the data to Excel.

    Thanks
    Bill

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Macro - Code ??? (Access 2000)

    As you said (or at least I think you said) set up the names required in another table and use VBA code and the TransferSpreadsheet method to export the output from the query to Excel.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Code ??? (Access 2000)

    Hi,

    I'm not having a problem getting the data to Excel, I just need to automate the query process somehow.

    Thanks
    Bill

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Macro - Code ??? (Access 2000)

    Here is an example of a sub I use to export multiple copies of same query with different criteria to Excel files. I modified it using NorthWind.mdb Customers & Orders tables for demonstration purposes.
    <pre>Sub ExportToExcel()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qry As DAO.QueryDef
    Dim strQry As String
    Dim strSQL As String
    Dim strCustomerID As String
    Dim strFileName As String
    Dim strPath As String
    Dim n As Integer
    Dim strMsg As String

    'Get list of unique Customers:
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT CustomerID FROM Customers;"
    Set rst = db.OpenRecordset(strSQL)

    strPath = "C:ACCESSTEMP"
    strQry = "TEMP"
    Set qry = db.CreateQueryDef(strQry)

    'Note: If EXCEL file already exists, the data will exported to
    ' new worksheet in same file named TEMP1, etc
    ' If file open error will occur
    With rst
    .MoveFirst
    Do Until .EOF
    strCustomerID = !CustomerID
    strSQL = "SELECT OrderID, CustomerID, EmployeeID, " & _
    "OrderDate, ShippedDate " & _
    "FROM Orders WHERE CustomerID = '" & strCustomerID & "'" & _
    "ORDER BY CustomerID, OrderDate;"
    qry.SQL = strSQL
    strFileName = strCustomerID & "_ORDERS"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strQry, strPath & strFileName, True
    n = n + 1
    .MoveNext
    Loop
    .Close
    End With

    strMsg = n & " files have been exported to EXCEL."
    MsgBox strMsg, vbInformation, "EXPORT COMPLETE"

    Exit_Sub:
    db.QueryDefs.Delete strQry
    Set db = Nothing
    Set rst = Nothing
    Set qry = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err
    Case 3012 'Object already exists (strQry is name of existing query or table)
    strQry = strQry & "1"
    Resume
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "EXPORT TO EXCEL ERROR"
    Resume Exit_Sub
    End Select

    End Sub</pre>

    You can copy this sub into code module in NorthWind to see how it works. You must set a reference to "Microsoft DAO 3.6 Object Library" if using A2K or later (Tools, References in VB Editor). In this example I used a Select Distinct SQL statement to generate a unique list of customers to use as criteria for temp query. If the names you are using for criteria are not listed in an existing table, recommend create table for this purpose, which can then be used as source for the recordset as illustrated.

    HTH

  7. #7
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Code ??? (Access 2000)

    Hi Mark,

    Thanks for your help, It will take me a little while to figure all of this out but thanks for getting me started.

    Bill

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Macro - Code ??? (Access 2000)

    You wrote:
    >>I have a table that I run a query on that using only 5 fields from the table. One field in the table is
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Code ??? (Access 2000)

    Hi Mark,

    Thanks for all of your help.

    I will explain things a little more in depth.

    -I have just 1 table that I am running these queries on.
    -The table has every name in it already (plus I also have a list of the names that I am going to run queries on).
    -I use only 4 fields in the query (student name)-(school)-(date)-(average)
    -the only field that I select criteria for is (student name)
    -there are thousands of students names
    -I already have list of the (student names) they are in groups of ten
    -I can easily copy and paste each group of ten
    -The reason that I move the query information to Excel is to perform mathematical functions such as (std. Deviation)-(z scores)-(outliers) etc..


    Below is kind of how I have the data in my Excel worksheet. I stopped at two students but on my worksheet it goes over 10 students.

    Name-----School-----Date-----Average----- Name------School------Date-----Average

    John____Main___10/2/02__ 74_____ Jane____ South____10/1/02__ 69
    John____Main___10/2/02__ 97_____ Jane____ South____10/1/02__ 92
    John____Main___10/2/02__ 84_____ Jane____ South____10/1/02__ 88
    John____Main___10/2/02__ 79_____ Jane____ South____10/1/02__ 79
    John____Main___10/2/02__ 94_____ Jane____ South____10/1/02__ 89

    Again to just compare 10 students I am running a 10 queries one for each student, then I move each query one at a time to Excel.

    Any help on improving this process would be greatly appreciated because on most days I am running over 100 small queries like this.

    Thanks Bill

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

    Re: Macro - Code ??? (Access 2000)

    In your example, you place the results for Jane to the right to those of John. If you would do this a 100 times, you'd end up with 400 columns, which is impossible - an Excel worksheet has 256 columns. So how do you want to organize your data in Excel? Do you want to put everything in one worksheet, or use more worksheets?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Code ??? (Access 2000)

    Am curious if she couldn't be served by learning a few Parameter query's, and then either Import the data to Excel from Excel as a link, or Export the data from Access as a link, if she wants it in Excel to do functions on.

    Parameter querys have one rule. Enter your question in brackets on the criteria line. eg. [Enter LastName], and you can use as many as you need, placing them on the OR lines below first entry.
    Exporting to Excel as Link, requires simply highlighting the Query icon which holds your results, Click copy on the toolbar, and then open Excel and choose the cell you would like it pasted in...then simply choose Edit...Paste Special from the menubar, and then Link, and OK.
    I may be way off base here, but maybe its something she can use.
    Good luck.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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