Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Automation and email (Access 2K)

    Hello again

    I've had a request to reduce someone's work load.... This entails providing an automated report producing facility. The current reports are a combination of Word (I can do this bit) and Excel. I'm struggling with Excel automation.

    I have a query that provides the right data for the workbook to graph, I can sort it out by using QueryDef to give me the data I want. What I'm having real difficulty in understanding is the method of getting the data from the query into Excel. I'm slightly familiar with DAO, but not at all with ADO (the book I have with an example is using ADO). The query will produce 7 columns of data, with an unknown number of rows, potentially up to 300.

    Once I've got the basic code sorted I then need to cycle through a table of contacts (a Do ..... While will sort this, I hope) to send the Excel workbook and the Word document to around 35 different people. I'm reasonably happy with the Outlook bit, having used it a few times with Word.

    If anyone can point me at the commands I need to know in the Excel object model (DAO please, pretty please) I'll almost certainly be able to get this to work. At the moment I'm a bit like a hungry guy in the jungle, losts of pretty looking berries, but!!!!!!!

    If it's easier, I'd be prepared to use the TransferSpreadSheet command and then format the resulting workbook with automation. At the moment this looks a simpler option to me, but am I digging a big hole for myself?

    Thanks for reading this.

    Ian

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

    Re: Excel Automation and email (Access 2K)

    If you want to get data from Access into Excel, you can use DoCmd.TransferSpreadsheet from the Access side, or CopyFromRecordset from the Excel side. CopyFromRecordset can work with either DAO or ADO recordsets. The following code is to be run from Access; you need to set references to the Microsoft DAO 3.,6 Object Library and Microsoft Excel 9.0 Object Library in Tools | References...

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim lngCount As Long

    Dim xlApp As New Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet

    ' Excel workbook and worksheet
    Set xlWbk = xlApp.Workbooks.Add
    Set xlWsh = xlWbk.Worksheets(1)

    ' Database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(...) ' up to you

    ' Field names in row 1
    For i = 0 to rst.Fields.Count - 1
    xlWsh.Cells(1, i + 1).Value = rst.Fields(i).Name
    Next i

    xlWsh.Range(xlWsh.Cells(1, 1), _
    xlWsh.Cells(1, rst.Fields.Count)).Font.Bold = True

    ' Data
    lngCount = xlWsh.Range("A2").CopyFromRecordset(rst)
    MsgBox lngCount & " record(s) transferred."

    ' Save etc.
    xlWbk.SaveAs "C:ExcelData.xls"
    ...

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and email (Access 2K)

    Hans

    OK, excellent, that got me started......

    Thanks

    Ian

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

    Re: Excel Automation and email (Access 2K)

    Huh? I was answering your question, and by the time I was ready to post my reply, the question had disappeared. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and email (Access 2K)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I made the post after about an hour of staring at the code, decided to have one last look and the error leapt out at me <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. I new you'd receive the original as an email, but was hoping I'd get the errant code removed before you got back.....

    My humble apologies for wasting your time.

    Ian

  6. #6
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and email (Access 2K)

    Thought I might be back soon....

    How do I set the axis labels? I know which column they will be in, but can't see what to use. There are a few items in the object model that look like they might, but I've had no luck with them as they don't seem to provide the 'template' to fill in as many of the Access parts of VBA do...

    Thanks

    Ian

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

    Re: Excel Automation and email (Access 2K)

    Something like
    <pre>xlWksht.ChartObjects(1).Chart.SeriesCollectio n(1).XValues = _
    "='" & xlWksht.Name & "'!R1C1:R20C1"
    </pre>

    where xlWksht is the worksheet object, and R1C1:R20C1 is the range containing the x-axis labels in R1C1 notation.

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and email (Access 2K)

    Brilliant, works fine.

    I also found the ChartWizard Method in the object browser, this does have the 'template' like many of the other Access methods, seems easy to use as well, as long as I arrange the original query sensibly.

    Thanks

    Ian

Posting Permissions

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