473,233 Members | 1,441 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 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 1484

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

Similar topics

38
by: Remco Groot Beumer | last post by:
Hello, I'm trying to decide if the following situation would be workable: Generate an MS Access Front End (which will run localy on client computers), which will link to a DBMS (SQL server or...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
1
by: u7djo | last post by:
Hi, I'm currently building a function in Access that creates an Excel spreadsheet but it doesn't look like the Excel object is being destroyed correctly as the Excel module is still showing in the...
0
by: TheCoder | last post by:
I am making a D-base with web conectivity for my class project. I have everything working but the subit button sends the data to the correct fields but afterwards it wants to reproduce new blank...
5
by: David C. Allen | last post by:
I have a vb.net app that is controling excel 2000 thru the com interop interface. I have referenced the excel 9.0 library and have cut down the code in the problem subroutine to this: Dim...
0
by: Pradnya Patil | last post by:
Hi , I need to export some of the reports to MS EXCEL & MS WORD in a WEB APPLICATION.I also need to LOCK some of the Columns in EXCEL-sheet.Right now I need to run the Interoperability...
0
by: Vijay | last post by:
Prep Courses for International Certifications, CSTE & CSQA & ISEB & ISTQB &Business Analyst & SOA Certifications in HYDERABAD. After receiving overwhelming response to our last 50+ batches, ...
4
by: Suzette | last post by:
I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables. I created a macro in Access to perform various necessary...
0
by: Noob | last post by:
Hello, I created an ERP based custom report using Access '03 & Excel '03. I developed an Access query that pulls the required data from the ERP (Progress) database via link tables. I use Excel...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.