Well sort of. The error I get is usually a "Object Required" error, and it highlights the bolded section below. I have been through this thing backwards and forth and cannot figure it out. I am new to this whole thing, so be easy on me.
Thanks!
Alex
----------code pasted below------------
Expand|Select|Wrap|Line Numbers
- Private Const conQuery = "alexpartcountrial"
- Private Const conSheetName = "Part Count"
- Public Sub CreateExcelChart()
- Dim rst As ADODB.Recordset
- ' Excel object variables
- Dim xlApp As Excel.Application
- Dim xlBook As Excel.Workbook
- Dim xlSheet As Excel.Worksheet
- Dim xlChart As Excel.Chart
- Dim i As Integer
- ' Create Excel Application object.
- Set xlApp = New Excel.Application
- ' Create a new workbook.
- Set xlBook = xlApp.Workbooks.Add
- ' Get rid of all but one worksheet.
- xlApp.DisplayAlerts = False
- For i = xlBook.Worksheets.Count To 2 Step -1
- xlBook.Worksheets(i).Delete
- Next i
- xlApp.DisplayAlerts = True
- ' Capture reference to first worksheet.
- Set xlSheet = xlBook.ActiveSheet
- ' Change the worksheet name.
- xlSheet.Name = conSheetName
- ' Create recordset.
- Set rst = New ADODB.Recordset
- rst.Open Source:=conQuery, ActiveConnection:=CurrentProject.Connection
- With xlSheet
- ' Copy field names to Excel.
- ' Bold the column headings.
- With .Cells(1, 1)
- .value = rst.Fields(0).Name
- .Font.Bold = True
- End With
- With .Cells(1, 2)
- .value = rst.Fields(1).Name
- .Font.Bold = True
- End With
- ' Copy all the data from the recordset
- ' into the spreadsheet.
- .Range("A2").CopyFromRecordset rst
- ' Format the data.
- .Columns(1).AutoFit
- .Columns(2).AutoFit
- .Columns(3).AutoFit
- End With
- ' Create the chart.
- Set xlChart = xlApp.Charts.Add
- With xlChart
- .ChartType = xl3DBarClustered
- .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
- .PlotBy = xlColumns
- .Location _
- Where:=xlLocationAsObject, _
- Name:=conSheetName
- End With
- ' Setting the location loses the reference, so you
- ' must retrieve a new reference to the chart.
- With xlBook.ActiveChart
- .HasTitle = True
- .HasLegend = False
- With .ChartTitle
- .Characters.Text = conSheetName & " Chart"
- .Font.Size = 16
- .Shadow = True
- .Border.LineStyle = xlSolid
- End With
- With .ChartGroups(1)
- .GapWidth = 20
- .VaryByCategories = True
- End With
- .Axes(xlCategory).TickLabels.Font.Size = 8
- .Axes(xlCategoryScale).TickLabels.Font.Size = 8
- End With
- ' Display the Excel chart.
- xlApp.Visible = True
- ExitHere:
- On Error Resume Next
- ' Clean up.
- rst.Close
- Set rst = Nothing
- Set xlSheet = Nothing
- Set xlBook = Nothing
- Set xlApp = Nothing
- Exit Sub
- End Sub