472,119 Members | 1,651 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Access & Excel Interoperability woes

I am trying to dump data from an access query into excel so I can pretify it. I found sample code from an old access 97 book (The version of access in use)and tinkered it into working.

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
  1. Private Const conQuery = "alexpartcountrial"
  2. Private Const conSheetName = "Part Count"
  3.  
  4. Public Sub CreateExcelChart()
  5.  
  6.     Dim rst As ADODB.Recordset
  7.  
  8.     ' Excel object variables
  9.     Dim xlApp As Excel.Application
  10.     Dim xlBook As Excel.Workbook
  11.     Dim xlSheet As Excel.Worksheet
  12.     Dim xlChart As Excel.Chart
  13.  
  14.     Dim i As Integer
  15.  
  16.     ' Create Excel Application object.
  17.     Set xlApp = New Excel.Application
  18.  
  19.     ' Create a new workbook.
  20.     Set xlBook = xlApp.Workbooks.Add
  21.  
  22.     ' Get rid of all but one worksheet.
  23.     xlApp.DisplayAlerts = False
  24.     For i = xlBook.Worksheets.Count To 2 Step -1
  25.         xlBook.Worksheets(i).Delete
  26.     Next i
  27.     xlApp.DisplayAlerts = True
  28.  
  29.     ' Capture reference to first worksheet.
  30.     Set xlSheet = xlBook.ActiveSheet
  31.  
  32.     ' Change the worksheet name.
  33.     xlSheet.Name = conSheetName
  34.  
  35.     ' Create recordset.
  36.     Set rst = New ADODB.Recordset
  37.     rst.Open Source:=conQuery, ActiveConnection:=CurrentProject.Connection
  38.  
  39.     With xlSheet
  40.         ' Copy field names to Excel.
  41.         ' Bold the column headings.
  42.         With .Cells(1, 1)
  43.             .value = rst.Fields(0).Name
  44.             .Font.Bold = True
  45.         End With
  46.         With .Cells(1, 2)
  47.             .value = rst.Fields(1).Name
  48.             .Font.Bold = True
  49.         End With
  50.  
  51.         ' Copy all the data from the recordset
  52.         ' into the spreadsheet.
  53.         .Range("A2").CopyFromRecordset rst
  54.  
  55.         ' Format the data.
  56.         .Columns(1).AutoFit
  57.         .Columns(2).AutoFit
  58.         .Columns(3).AutoFit
  59.  
  60.     End With
  61.  
  62.     ' Create the chart.
  63.     Set xlChart = xlApp.Charts.Add
  64.     With xlChart
  65.         .ChartType = xl3DBarClustered
  66.         .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
  67.         .PlotBy = xlColumns
  68.         .Location _
  69.          Where:=xlLocationAsObject, _
  70.          Name:=conSheetName
  71.     End With
  72.  
  73.     ' Setting the location loses the reference, so you
  74.     ' must retrieve a new reference to the chart.
  75.     With xlBook.ActiveChart
  76.         .HasTitle = True
  77.         .HasLegend = False
  78.         With .ChartTitle
  79.             .Characters.Text = conSheetName & " Chart"
  80.             .Font.Size = 16
  81.             .Shadow = True
  82.             .Border.LineStyle = xlSolid
  83.         End With
  84.         With .ChartGroups(1)
  85.             .GapWidth = 20
  86.             .VaryByCategories = True
  87.         End With
  88.         .Axes(xlCategory).TickLabels.Font.Size = 8
  89.         .Axes(xlCategoryScale).TickLabels.Font.Size = 8
  90.      End With
  91.  
  92.     ' Display the Excel chart.
  93.     xlApp.Visible = True
  94.  
  95. ExitHere:
  96.     On Error Resume Next
  97.     ' Clean up.
  98.     rst.Close
  99.     Set rst = Nothing
  100.     Set xlSheet = Nothing
  101.     Set xlBook = Nothing
  102.     Set xlApp = Nothing
  103.     Exit Sub
  104.  
  105. End Sub
May 11 '07 #1
0 1437

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

38 posts views Thread by Remco Groot Beumer | last post: by
1 post views Thread by u7djo | last post: by
reply views Thread by TheCoder | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.