Excel Automation and email (Access 2K)

Topic

New Reply

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

Reply | Quote

Replies

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”
…

Reply | Quote

Hans

OK, excellent, that got me started……

Thanks

Ian

Reply | Quote

Huh? I was answering your question, and by the time I was ready to post my reply, the question had disappeared.

Reply | Quote

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 . 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

Reply | Quote

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 . 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

Reply | Quote

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

Reply | Quote

Something like

xlWksht.ChartObjects(1).Chart.SeriesCollection(1).XValues = _
  "='" & xlWksht.Name & "'!R1C1:R20C1"

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Something like

xlWksht.ChartObjects(1).Chart.SeriesCollection(1).XValues = _
  "='" & xlWksht.Name & "'!R1C1:R20C1"

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

Reply | Quote

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

Reply | Quote

Huh? I was answering your question, and by the time I was ready to post my reply, the question had disappeared.

Reply | Quote

Hans

OK, excellent, that got me started……

Thanks

Ian

Reply | Quote

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”
…

Reply | Quote