Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Tidewater, VA
    Posts
    8

    Unhappy Unanswered: TransferSpreadsheet then open Excel

    Does anyone know of a way to create one macro or use code that would transfer the results of a select query to an existing Excel spreadsheet and then open that spreadsheet?

    If that is not easy to perform, is there a calculation like Excel's "NETWORKDAYS" that I can use in Access and then add that to the select query? If so, then I could just use a macro to OutputtoExcel.

    Thank you,

    Mike Britt

  2. #2
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44

    automation

    Here is something I have used to transfer into Excel. This example has Excel in control.
    Code:
    Sub DoAccessQuery()
       Set ws = DBEngine(0)
       Set DB = ws.OpenDatabase("C:\My Documents\Access\Fin\Rand.mdb")
       Sql = "SELECT UB92.PAT_NO, UB92.AA6_ADMIT_DATE, UB92.PAT_NAME, UB92.DIAG_PRIN_DSCH_CODE, UB92.SURG_PRIN_PROC_CODE, UB92.SURG_PRIN_PROC_DATE FROM UB92 WHERE (((UB92.PAT_NO)=[patient number]));"
    
       Set QD = DB.CreateQueryDef("", Sql)
       QD.Parameters![patient number] = [A1].Value
       Set RS = QD.OpenRecordset()
       Worksheets("Sheet1").Range("A1").CopyFromRecordset RS
       
       Set DB = Nothing
       Set ws = Nothing
    Exit Sub
    End Sub
    The Microsoft Knowledge Base has an article (Q247412) that shows several methods for automating the process.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    This bit of code will do all that u ask for in One line
    DoCmd.OutputTo acOutputQuery, "output", acFormatXLS, location_filename, True


    "output" = the query
    location_filename = the location and file name ie c:\export.xls

    the true statment at the end opens excell i beleve
    Jim

  4. #4
    Join Date
    Nov 2002
    Location
    Tidewater, VA
    Posts
    8

    Smile

    Thank you, I'll give it a try.

  5. #5
    Join Date
    Nov 2002
    Location
    Tidewater, VA
    Posts
    8
    Originally posted by JDionne
    This bit of code will do all that u ask for in One line
    DoCmd.OutputTo acOutputQuery, "output", acFormatXLS, location_filename, True


    "output" = the query
    location_filename = the location and file name ie c:\export.xls

    the true statment at the end opens excell i beleve
    Jim
    The problem with this code is that it does not export to an existing spreadsheet which is already formated and used the NETWORKDAYS forumula. Is there a way to make it work with an existing spreadsheet?

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by mcbritt
    The problem with this code is that it does not export to an existing spreadsheet which is already formated and used the NETWORKDAYS forumula. Is there a way to make it work with an existing spreadsheet?
    im sorry i didnt understand. The only way to get any formulas into this way would be to have them in the query that u export, so this may not be what u want
    Jim

Posting Permissions

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