Results 1 to 4 of 4

Thread: Append output to XLS from MS ACCESS

  1. #1
    Join Date
    Aug 2002
    Posts
    6,212
    Rep Power
    0

    Default Append output to XLS from MS ACCESS

    Currently I have a program that outputs data from a query in different MS Access Databases to an Excel sheet. I use the following code:

    Code:
        apAccess.DoCmd.OutputTo acOutputQuery, isQuery, "Microsoft Excel 97-2003 (*.xls)"
    At present it goes to each db and outputs to it's own excel file, but I want to shorten my work and append the results all in one sheet in one XLS file (and if possible to add a column with an indicator of which db it came from).

    Any suggestions?
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    Changes to posting in Classifieds

  2. #2
    Join Date
    Feb 2003
    Posts
    3,038
    Rep Power
    0

    Default

    More than likely you will have to capture the xls file names try to append them together using a loop and the file processing functions. Might work better if it were CSV files but you could try you luck with the pure xls files.

    I don't mess around with those scripts so I would not be able to tell you exactly how you could do it. But getting the list of files created is a good starting point. Then Look up the file manipulation functions available to you.

  3. #3
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    I recall having a similar requirement back in the day...The application was written in VB6...
    Essentially the application used one of the MS office references/objects to create and append to the excel file.

    ===============
    Dim objAppXls As Excel.Application
    Dim objWkbk As Excel.Workbook
    Dim objWkst As Excel.Worksheet

    Set objAppXls = CreateObject("Excel.Application")
    Set objWkbk = objAppXls.Workbooks.Add

    Set ObjWs = objWkbk.Worksheets.Add
    ================

    This is what I can remember (from 7yrs ago, haven't do this in recent times)...using something like that you should be able to created an excel sheet, where you update using another connection from you Access DB. I'm not sure of your proficiency with vb (or better - vb.net). As Owen suggested a CSV file may be easier to construct. However with the excel object approach, you'll be better able to manage the 65k row limit...dynamically/programmatically create sheets as required.

  4. #4
    Join Date
    Feb 2003
    Posts
    3,038
    Rep Power
    0

    Default

    Yeah I totally forgot about the object web of maddness that is VB4,vb5,vb6. I was thinking too simple, too opensource. I think a fancier/neater solution would be to open one of the spreadsheets and use the ADDSHEET function to add the rest of the files into the first file as worksheets. i.e. if you don't mind the 65k row limit.

Posting Permissions

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