Access/Excel graph

Why don't you use Access's charting on  a form in Access?  Why ge Excel involved?

(It would be nice if you made a paragraph or two, every now and again.  One giant block of text is hard to read...)

Brian

I don't blame you for using Excel... Access graphing is not very easy to interface with.  Also, Excel is probably easier for people to play with & tweak the graph's appearance.
(Ditto the huge paragraph of info.)

As for synching your data...
You should be able to live-link data from Access into Excel.  (I'm no Excel guru, so I can't tell you how to get it done :( )  When Excel opens, it should be looking at the current Access data (even a query view).  Base the chart off the "interactive" data.  Or, worst case, write VBA code in Excel to fetch the data when the sheet is opened.  Then you'll be *pulling* data into Excel instead of *pushing* it from Access.

Hello jay,
I will attempt an answer based on my understanding of the question, you may need to clarify a few points for me. As others have mentioned, charting in Access can be somewhat confusing and you seem to have knowledge of Excel’s VBA. You are actually close to a solution. All you lack is the code to automate the Excel chart-making step. It appears that your charts are temporary. That is, old charts aren't needed. If so, you could automate the daily "chart update" process after you’ve run the necessary update code, queries, etc.: First, delete all charts that need updating. Then, after passing the data to Excel, select the passed data and programmatically create, name and save the excel chart(s). One important programming task is to decide where (in Access or Excel) to identify the rows and/or columns of data in order to tell Excel where the selection “is” on the worksheet. I have numerous routines which pass data to Excel from Access to make charts and the process works reasonably well.

jvf,

It is customary in the Access Topic area to post answers as 'Comments,' instead of proposing them as 'Answers.'  This prevents the question from being locked, encouraging more robust exchanges, and avoids making the person who asked the question reject one answer so that they can accept a different one.  Would you please convert your 'Answer' to a 'Comment?'

Brian

jvf changed the proposed answer to a comment

BrianWren: Somehow I *knew* you'd be jumping on that...lol!  In fact, I decided to leave it up to you because you do such a good job (seriously!) of getting the point accross gently.  BTW: Do you have that stored for cut&paste? :^)

sorry guys, i'm new here. Where's the instruction book?

No problem.  At least you got a gentler nudge than I did my first day. (We won't go into that.)
Instruction book?  hahaha!  Nope, only OJT here... Gee, just like the real world - trial by fire!

Gee, until now I thought the gaming community was the only rowdy bunch.

What, you think a bunch of us aren't also members of that gaming community?  You should be severely fragged!  Long live Quake II and GameSpy!!!!!!!!!!

You should be able to link Access data from Excel and refresh from Excel or access as you wish. If this would help let me know.
m3

Well I have little to no knowledge of VBA in Excel...Access is something different...Does anyone have any code that I could test out for this problem?

As I do most tasks by writing code and you seem to have accomplished much of your job by using wizards and keystroke macros it's hard to figure what condition your condition is in after you are "done". For instance how does the data get to Excel? Are you using the tools/office links/Analyze it with MS Excel option? How does your Access form open the Excel chart? The "dynamic link" from access to excel that you seem to be looking for is available but I don't see how it would help. I could be mistaken but I don't know of any way that an excel chart can be updated with additional data by simply passing new data to it's corresponding worksheet. The new data has to be copied and pasted to the chart somehow. If the data range of the chart remained the same but some values changed, these changes would be automatically reflected but additional data would not. So, if your Access form opens to the same chart every time, even if you update it manually, the user should see the updated version. What part(s) of your puzzle am I missing?  

Well I had the data from the Access reports being passed to Excel for charting through macros. I set up a macro for each report that needed to be charted in Excel and in doing this the data was transfered into the spreadsheet. After this I created the chart in Excel based on the transfered data.

Once the chart is set up I save it and exited the spreadsheet. Now this data changes from day to day, therefore the charts need to stay current. Although, I encounter a problem once I execute one of the macros again. It copies over the old data used to populate the chart(which is fine), but also copies over the chart so all the users see is the new data in cells and no chart.This is what I need to remedy.

Ok, I'm getting closer to an understanding of the problem. Here are my questions and thoughts for the day. Which of the the following scenarios is correct: Each report is saved in a separate workbook which ends up containing two sheets (a worksheet and a chart). -or- Each report is saved in a separate workbook which contains one sheet showing both data and the chart. Also I need to know how the Access buttons open the Excel workbook. If the workbook only contains one sheet, opening the workbook will suffice. If it contains more than one sheet we must select and activate the desired sheet. Your statement that the data copies over the chart seems to imply that you are placing the chart on the worksheet(scenario #2) instead of a separate sheet and then Access just opens the workbook. In any case, if you are transferring data by the aforementioned tools/office links/Analyze with Excel method (yes?) you WILL lose the chart and only see data. As stated before, some coding will be necessary if you want to automate this process. Once I have a COMPLETE understanding of your current process I can provide some code for you to try.

Once the reports are designed in access the macro transfers the data in the report to an excel spreadsheet...Once the charts have been created in excel there are two sheets the data sheet (which is the data from the access report) and the chart which is created from the data sheet...

So I do have two spreadsheets one data and the other is the graph. In access I set up cmd buttons that open up the specified charts (in excel) from the macros that I have created. Although after the initial creation of the charts if you exit Excel and try to open the same chart again from access the data sheet is written over, but there is no chart. This is a problem because I don't want my users to see just a data sheet without a chart.

hello jay,
I'm suffering through a problematic Win2K install so things are a bit scattered. Your last response indicates that when you re-open Excel, the data sheet is written over but there is no chart (so now the book contains only one page). How are you re-opening Excel? If you are using your “open excel” command buttons, the chart should still be there. This chart disappearance should only occur when you update the data. We must sharpen our communication skills in order to solve your problem. I am getting the impression that you (like most of us) are just trying to use these programs to carry out a task and have not had to delve into their inner workings. So, when modifying them to suit your needs you aren’t writing “code” in a code window but using keystroke type macros. This approach is very helpful for many situations but as you can see it has its limitations. My background in getting computers to work for me has been to write code. As ridiculous as it sounds, I have very little knowledge of how to write and use “macros” in Access.  Therefore you need to help me by explaining some things about your macros. Let’s do it by the numbers
1) I have been asking how the data is transferred to Excel and your latest response indicates that it is by macro. I went to the macro tab and “wrote” a macro to transfer a report to Excel. There is an auto start feature, which will open the file after it is transferred. Do you use it?
2) Do you transfer all the files with one macro or do you have a macro for each report? If the auto feature is Yes for all “output to” actions it seems that Excel opens all the workbooks and the last report transferred is the one that shows.
3) I need to know EXACTLY what your Access buttons do to open the chart. Using the command button wizard or macros I can’t find any way to open an Excel chart so please explain.
The simpler your charts are, the easier it will be to implement a solution so here are some chart questions:
4) What type of chart do you use?
5) Are the data series in rows or columns?
6) Do you use the field names for descriptions?
7) Do you use all the data?
8) Do you name the chart or let Excel use the default “Chart1” name?

1. Yes I do use the auto start feature when I make the macros its set up like this:
Object Type: Report
Object name: (name of report in access being transferred to excel)
Output Format: Microsoft Excel
Output File: C:\DAC_Test.xls(location)
AutoStart: Yes

2.I write a separte macro for each report being transferred.

3. Well, I just create a cmd button in access that has the macro I created in the On Click event. This in turn opens a spreadsheet in excel with the data from the report. I then create a chart in excel from this data.

4. I use bar charts.

5. The data is in columns.

6.Yes the field names are the names used in the charts.

7. Yes all of the data transferred is used.

8. No I rename the charts before saving.

All of these processes work fine, I have a data sheet and a separate chart created from the data sheet and saved. Once you try to access the chart again from the database the new information from the report writes over the old(which is fine), but it also writes over the chart. So all I have is a spreadsheet with data from the reports but no chart.

thanks jay,
I believe I have enough info for now. I'll try some things and get back to you Mon.

OK-I've worked out how to automatically create and open a chart starting with your macro. Let's ignore for a moment that the chart might need further formatting (to look right) and get on to the grisly task of determining how to modify your process to implement this solution. First we must determine the exact nature of your process. So- the floodgate of questions will reopen. Your original statements indicate that your database is stored on a server and users have Access on their machines. This allows them to open this remote database, find your form and click on one of the report buttons. Is this correct? You stated that you "created a chart" and "saved it to the server" so "users could view the chart". There are some glaring logical inconstancies. For instance, your macro path for saving the excel file points to C drive. This implies that you are working ON the server, not later transferring the excel workbooks TO the server. Also, if as stated, the Access buttons all use the same macro code (which points to C drive) then users are actually storing separate copies of the outputted Workbook to their respective local drives, not opening the workbook first created by you. If true, this is not necessarily a bad thing. Also not clear is if the users actually use the database at all or are just using the Access interface to view the charts. Here are some scenarios to consider and then the question list will follow. I wonder how the process takes place? Scenario #1: Total anarchy---users enter data at random times and whenever a report is needed they press the Access button and expect the latest chart. Scenario #2: users enter data at random or present data by hand to one person for data entry. This person enters the data and /or updates the reports at certain intervals. When users view the charts they see the last update. Any data entered without rerunning the reports is not available. Scenario #3: Users don't use the database at all, they just need to see reports.
1) Which scenario best describes what you do now (or what you'd like to do if you want to change it)
2) Is your network the usual type with a server and remotely connected machines
3) How many users/machines need to view the charts
4) How many different reports are there
5) Do all the users have a copy of the Office suite on their machines
6) Does the complete database (forms with tables) reside on the server and everyone assesses it remotely as described above
7) Concerning the chart names. Do they need to be different each time or can they be the same? If they can be the same do you have specific names that you use or can we incorporate the report name in the chart name for identification purposes. As this process automates itself it needs to prompt for chart names, get them from a table or it can make one up using some criteria such as the report name, date, or something else that we can code in. Prompting for chart names or making up a form to enter different names, etc. will be quite awkward if you are using the Total anarchy method as everyone will be trying to supply a chart name every time he runs a report. Even if only one person does it, entering names in each time could be a drag. Nonetheless if you need to do it that way, we can.
8) When creating your command buttons have you ever investigated the On Click_  property and looked at the code that the button wizard has created
9) Last but not least-which machine stores the charts that you create. Do you really work on the server machine or do you later transfer the worksheets from your machine to the server

P.S.-concerning question #8--As stated, I don't work with macros much. Playing around in design view I notice that the button wizard just places the macro name in the On click area (seen on the event tab when the button is selected) or you select a macro name from the dropdown arrow and don't use the wizard. So the real question is do you know how to get into the code windows? If not, I'll show you how when we're ready to start pasting code into your application.

Sorry the path I stated earlier was just to give you an example of what I was doing, but the path presently being used points to the copies on the server.(not my c: drive)

I have scenario #1..users enter info at random times and every time they open a report they expect to see the most updated information.

2. Yes we have the typical network with romotely connected machines.

3. All 30 of the users need to have access to the charts. But there wont be many instances where more than one user will view the charts at the same time.

4. I have seven reports.

5. Yes everyone has a copy of the office suite.

6.Yes everything is on the server.

7.The chart names can stay the same and yes we can incorparate the chart names in the report names for identification purposes. If you rather use code to call the charts that would be fine, but I need the updated information in the reports to be passed to the charts everytime the user clicks the specific chart button in the database. For every chart in excel there is a report in access which contains the most updated info.

8. Yeah I know how to get into the code windows.

9. Everything is on the server.

Ok! We’re getting close. Hopefully you have a common path and/or folder for all reports. If not, can we set it up this way? Also, If possible I’d like you to name the Access buttons the same as the report name. Here is my idea: We will add a module to the database, name the buttons the same as the report and have a common path for the outputted reports. Instead of running a macro when the button is clicked we will run a function (you will be able to see the line of code which replaces the macro when you check out the code). As you may know, all we have to do is type the function name in the On Click property window (where your macro name is located now)-we don’t need to go into the code window for the button. The function will use the button name to get things going and eventually the chart will magically appear (we hope). If you make new reports that have the same format all you do is add a new button (just copy an existing report button and rename it--the on click function will also copy over automatically). Is this a good idea for your needs? If the paths have to be different we can still accommodate that. If the buttons can’t be named with the report name we can work around that also. So-if we can have a common path, please supply it and I’ll put it in. I will make it easy for you to change the path later if you need to. I’m more than a little stumped on how Excel makes a column chart (I don’t work with them much). My idea of data in columns doesn’t seem to match what Excel’s idea is. If another record was added to the report should the data show as a new column group or should another column be added to each existing group? To me, each field should be a group of columns and adding a record adds a column to each existing group. This makes me think the data is in columns to make the chart. However, the opposite seems true. If you leave the default choice (data is in rows) and graph in this way, a new record added would have a new column in each group. So-what option do you use when graphing? Not mentioned was the need for formatting such as x and y titles, main chart title, etc. Hopefully none are needed and the field names represent groups of columns with the name on chart tab being enough of an identifier. We may have to work on this a bit but it can get complicated in a hurry. By the way, what version of Office are you using?

Code works for me..I don't usually use Macros either I just learned how to use them...I beleive the data should be charted in rows now that I think about it...Your request of renaming the buttons to match the report names is fine. Everything resides on the server, the common path would be Va-dfs/shr/DS_Doc/Release5.0/Phase2/Test. As for the titling of the x and y axises all these functions work fine and the feild names will be the same as they appear in the data spreadsheet. Oh and we are using Office 97 here.

We're ready! But-I'm swamped today. I'll send the code and explanations tonite or in the morning. I didn't notice a drive letter in your pathway. If you get this note before my next post, send me a drive letter. Otherwise you can correct it there.

The drive letter is F:

Thanks.

Hello Jay,
      Geez, nothing like an experts-exchange server meltdown at the big moment. This missive contains the whole enchilada so you probably want to copy it to your favorite word processor and save it. Sorry it has taken this long to get you some code. I occasionally work with a general contractor friend. The first thing we tell a new client is that we might be slow, but we’re expensive. Anyway, my first hunch appears correct-you just need to automate the chart making process and make a new chart every time the report is run.
      One of microsoft’s many dirty little secrets is that the process of automation is fraught with bugs and difficulties. One error that you will VERY likely encounter with code when trying to reference Excel is error 429: ActiveX component can’t create object. Error 429 is my personal Freddie Kruger-everytime I think I’ve finished it off it appears again. In order to work with Excel we have to make a reference to it by using either of two functions: GetObject or CreateObject. Using CreateObject is easy. Using GetObject is a pain in the a… Unfortunately for me, using GetObject is my preference. CreateObject always creates a new instance of the application. This means that multiple versions of Excel could be opened on the machine if the newly created Excel object is not terminated properly. If you use CreateObject and expose the instance of Excel to the user, you effectively lose control of it. So, if the user doesn’t close it when he’s done it will hang around. This can create confusion for the user if he frequently tabs between applications and/or launches Excel from somewhere else (Hey, I know that workbook was open, where did it go?) and, confusion with code that attempts to reference Excel objects because it will randomly pick one of the opened instances to work with. GetObject works by referencing an already open instance of the application. If there are none, we have to open Excel. This is the catch 22. The usual way to launch an application (other than using CreateObject) is to use the shell function (which is what I do). One problem is that it is an asynchronous function-meaning that your next lines of code will start to execute while the app is launching. So-if you try to reference it too soon you’ll crash. This is the reason for my delay code. O if that were the only problem. The real p… off  is that depending on the windows version, office version, moon position (and maybe even processor) the GetObject function can fail. Win95 seems especially vulnerable. In their explanations of this unruly behavior, microsoft also claims that it is “by design” (yeah, right) and offers workarounds (none that work). So, if GetObject fails we have two options (I’ve sent them both). We can just use CreateObject and trust the user to neatly close the chart when he’s done. Or, we can look for Excel and, if it’s not running, present a message box telling the user to please start Excel and press the start report button again. If the user launches Excel and returns to Access, the GetObject function will work. Confused? Send comments to billgates@fryinhell.com. Error 429 can also occur if Access has lost a reference to the dao350.dll file. It can be re-registered by opening it using regsvr32.exe, which is on your machine. To check this, run this code from somewhere: Dim db as database:Set db=currentdb. If you don’t get Error 429 the DLL is prob. OK. Are you still awake? If you’ve read this far, you probably want to try it so here goes: If you haven’t already done so, I strongly suggest that you check the Require Variable Declaration box on the Tools-Options-Module tab. This will save your butt many times over by setting  'Option Explicit'  in all the code modules. We actually have two modules. If you already have code modules you can of course fit this stuff in wherever you want. Otherwise, you need to create and save two modules to paste the code into (paste it below the Option Compare Database, Option Explicit statements). The first module is a small API declarations module (I call it API Declarations). I try to avoid using APIs but I can’t find a way to do certain things without them. The second module (name it as you wish) contains the code. There is some stuff there you won’t use but I copied most of this from existing code and didn’t want to try and redo everything.

IMPORTANT: While in a code module, check the Tools-References tab. 1) Be sure you have a DAO object library checked (or Freddie 429 can appear). If you have Microsoft DAO 3.51 Object Library, use it. Otherwise you probably have Microsoft DAO 3.50 Object Library.    2) Also we need to check the reference to the Excel object library (Microsoft Excel 8.0 Object Library for office 97).

In  my code module declarations section you will find some constants for setting the report path, delay for GetObject  and chart size. The delay is set for 2 seconds (some of my machines work with 1 second, my dual processor celery hot rod doesn’t need any delay). Before giving up, try 5 seconds. If it still crashes you’ll have to try the other "StartReport" functions. Now, name each button for its respective report and type the starting function's name followed by () on the button’s event tab On Click property like so:  =StartReport(). [Remember, if you're going to create new buttons, create one first and add the function. Then all you have to do is rename the duplicate buttons because the function copies automatically when the button is duplicated]. Clicking the button will call the StartReport function and we’re rolling. If the infernal Error 429 occurs and it’s not DLL related here are your options. I’ve included two alternative StartReport Functions, StartReportC and StartReportO. StartReportC uses the Createobject function and the user doesn't have to respond to a message box but may end up with more than one instance of Excel running. If the users just need to glance at the chart (and/or print it) and will most likely close it shortly thereafter this could be your best choice. It also has the advantage of  saving the time overhead required for the Shell function. You might decide to use it even if the original StartReport function (using GetObject) works. StartReportO prompts the user to open Excel if Excel is closed and then uses GetObject. Take into account how your people use Excel and pick one. Let me know which one works for you. As you might imagine, a complete error checking routine is beyond the scope of this project. We check for misspelled or missing report/button names, open reports, and the report’s save to path (I have some code on a machine downstairs. Every 6 or 8 weeks it loses it’s “map” and can’t figure what to do). I have no idea of the consequences of users trying to run the same report concurrently. If it’s a problem, you might try putting a folder on the user’s local drive, change the path constant to "C:\folder\" and save the reports there. If having done all this and we actually make a chart-Hooray! But-how does it look? As stated, we may have to tweak the format. If you look at the MakeChart function you will see that I put “blank” titles for x and y axis by using a space (“ “). You could type in something there but it will be the same for all charts. I also deleted the legend and you could rem out those statements if you want. Let me know how it goes. If any instructions are unclear I'll try again. I just created a new db, set the references, copied my test form ,table and report, cut and pasted the code from word, tried all 3 StartReport Options and things seemed to work. Here is the code:
''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''API declaration module'''''------------------------------:

Declare Function GetTickCount Lib "kernel32" () As Long
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpclassname As Any, ByVal lpWindowName As Any) As Long
Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesA" (ByVal lpfilename As String) As Long

'_____file constants
'---
Public Const SW_NORMAL = 1
Public Const SW_MINIMIZE = 2
Public Const SW_MAXIMIZE = 3
Public Const SW_SHOW = 9

''''end API declarations    code-------------------------------------------------------------------

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'working code module-------:

'--some constants to make changes easier:----

'path for saved report output:
Public Const constMyPath As String = "F:\Va-dfs\shr\DS_Doc\Release5.0\Phase2\Test\"
'delay (seconds) for shell function to finish before calling
'GetObject. If this value is too low we'll get error 429 and crash
'trying to reference Excel:
Public Const constWaitForExcel As Integer = 2
'chart size in percent. Sometimes Excel is stingy and opens
'with a very small chart:
Public Const constChartSize As Integer = 50

Public Function openXL()
'look for Excel
Dim x As Long
'use API
x = FindWindow("XLMain", 0&)

'0 means excel closed so open it
If x = 0 Then
    Dim retval As Long
      'open as an icon
        retval = Shell("C:\Program Files\Microsoft Office\Office\EXCEL.EXE", vbMinimizedNoFocus)      ' Run excel
End If

'need delay so getobject won't crash
'delay can be increased by resetting constWaitForExcel in the
'declarations section of this module

Dim mark
Dim checktime

mark = Now()

Do
DoEvents
checktime = Now()

If DateDiff("s", mark, checktime) >= constWaitForExcel Then
Exit Do
End If
Loop


End Function




Public Function StartReport()
 On Error GoTo MyErr
 'check for good path
 If GetFileAttributes(constMyPath) = -1 Then
    MsgBox "Can't find report path " & constMyPath
    Exit Function
End If
 
 
 'using the status bar, Set statusRtn to sysCmd as necc
 Dim MyStatus As Boolean, StatusRtn As Long
 'save the current state of status bar
 MyStatus = GetOption("Show Status Bar")
 'show it in case it's hidden
 SetOption ("show status bar"), True

 Dim MyReportName As String
 Dim MyOutput As String
 Dim msg As String
 'button name should be report name
 MyReportName = Screen.ActiveControl.Name
MyOutput = constMyPath & MyReportName & ".xls"


StatusRtn = SysCmd(acSysCmdSetStatus, "Output Report to Excel")

'output report to excel but don't open it

DoCmd.OutputTo acOutputReport, MyReportName, "Microsoft Excel", MyOutput
'make sure Excel is open
StatusRtn = SysCmd(acSysCmdSetStatus, "Finding Excel (takes a while)...")

Call openXL
'reference Excel
Dim XL As Object
Set XL = GetObject(, "Excel.application")
'make the chart
'StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart--Please wait...")

Call MakeChart(XL, MyOutput, MyReportName)
'show the chart

Call ShowApp(XL, "Excel")
Set XL = Nothing
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

Exit Function

MyErr:
Select Case Err.number
Case Is = 2103
    'button name doesn't match any report name
    msg = MyReportName & " does not exist" & vbNewLine
    msg = msg & "check to see if the buttton name matches a current report"
Case Is = 2302
    'report open
    msg = MyReportName & " is open. Please close it and rerun"

Case Else

msg = Err.number & vbNewLine & Err.description


End Select
MsgBox msg
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

End Function

Public Function MakeChart(XL As Object, MyChart As String, MyReportName As String)
Dim MyChartName As String
Dim MySheetName As String
Dim MySelection As String
'using status bar here as well so dim rtn value
Dim StatusRtn As Long
MyChartName = MyReportName
With XL
        StatusRtn = SysCmd(acSysCmdSetStatus, "Getting Report")

        .Workbooks.Open MyChart
        .Sheets(1).Select
        MySheetName = XL.Sheets(1).Name
        .Range("A1").Select
         StatusRtn = SysCmd(acSysCmdSetStatus, "Selecting Data")

        .ActiveCell.CurrentRegion.Select
        MySelection = XL.ActiveWindow.RangeSelection.Address
        StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart-- Please wait...")

        .Charts.Add
        .ActiveChart.ChartType = xlColumnClustered
        .ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlRows
        XL.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=MyChartName
        With XL.ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = MyChartName
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = " "
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = " "
             .Legend.Select
             XL.Selection.Delete
            .Deselect
   
        End With
    .ActiveWindow.Zoom = constChartSize
End With
End Function
Public Function ShowApp(MyObj As Object, MyApp As String)
Dim Temp, hwnd As Long
Dim lpclassname As String
Select Case MyApp
Case Is = "Excel"
lpclassname = "XLMain"
Case Is = "Word"
lpclassname = "OpusApp"
End Select
hwnd = FindWindow(lpclassname, 0&)
Temp = SetForegroundWindow(hwnd)
    Temp = ShowWindow(hwnd, SW_MAXIMIZE)
MyObj.Visible = True
End Function


Public Function StartReportO()
 Dim x As Long
'use API
x = FindWindow("XLMain", 0&)

    If x = 0 Then
    MsgBox "Excel is closed. Please open Excel and Start again"
    Exit Function
End If
If GetFileAttributes(constMyPath) = -1 Then
    MsgBox "Can't find report path " & constMyPath
Exit Function
End If
 
 On Error GoTo MyErr
 'using the status bar, Set statusRtn to sysCmd as necc
 Dim MyStatus As Boolean, StatusRtn As Long
 'save the current state of status bar
 MyStatus = GetOption("Show Status Bar")
 'show it in case it's hidden
 SetOption ("show status bar"), True

 Dim MyReportName As String
 Dim MyOutput As String
 Dim msg As String
 'button name should be report name
 MyReportName = Screen.ActiveControl.Name
MyOutput = constMyPath & MyReportName & ".xls"


StatusRtn = SysCmd(acSysCmdSetStatus, "Output Report to Excel")

'output report to excel but don't open it

DoCmd.OutputTo acOutputReport, MyReportName, "Microsoft Excel", MyOutput
'make sure Excel is open
StatusRtn = SysCmd(acSysCmdSetStatus, "Finding Excel (takes a while)...")
'Call openXL
'reference Excel

Dim XL As Object
Set XL = GetObject(, "Excel.application")
'make the chart
'StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart--Please wait...")

Call MakeChart(XL, MyOutput, MyReportName)
'show the chart

Call ShowApp(XL, "Excel")
Set XL = Nothing
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

Exit Function

MyErr:
Select Case Err.number
Case Is = 2103
    'button name doesn't match any report name
    msg = MyReportName & " does not exist" & vbNewLine
    msg = msg & "check to see if the buttton name matches a current report"
Case Is = 2302
    'report open
    msg = MyReportName & " is open. Please close it and rerun"

Case Else

msg = Err.number & vbNewLine & Err.description


End Select
MsgBox msg
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

End Function


Public Function StartReportC()
 Dim x As Long
If GetFileAttributes(constMyPath) = -1 Then
    MsgBox "Can't find report path " & constMyPath
Exit Function
End If
 
 On Error GoTo MyErr
 'using the status bar, Set statusRtn to sysCmd as necc
 Dim MyStatus As Boolean, StatusRtn As Long
 'save the current state of status bar
 MyStatus = GetOption("Show Status Bar")
 'show it in case it's hidden
 SetOption ("show status bar"), True

 Dim MyReportName As String
 Dim MyOutput As String
 Dim msg As String
 'button name should be report name
 MyReportName = Screen.ActiveControl.Name
MyOutput = constMyPath & MyReportName & ".xls"


StatusRtn = SysCmd(acSysCmdSetStatus, "Output Report to Excel")

'output report to excel but don't open it

DoCmd.OutputTo acOutputReport, MyReportName, "Microsoft Excel", MyOutput
'make sure Excel is open
StatusRtn = SysCmd(acSysCmdSetStatus, "Finding Excel (takes a while)...")
'Call openXL
'reference Excel

Dim XL As Object
Set XL = CreateObject("Excel.application")
'make the chart
'StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart--Please wait...")

Call MakeChart(XL, MyOutput, MyReportName)
'show the chart

Call ShowApp(XL, "Excel")
Set XL = Nothing
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

Exit Function

MyErr:
Select Case Err.number
Case Is = 2103
    'button name doesn't match any report name
    msg = MyReportName & " does not exist" & vbNewLine
    msg = msg & "check to see if the buttton name matches a current report"
Case Is = 2302
    'report open
    msg = MyReportName & " is open. Please close it and rerun"

Case Else

msg = Err.number & vbNewLine & Err.description


End Select
MsgBox msg
'clear status bar
StatusRtn = SysCmd(acSysCmdSetStatus, " ")
'reset status bar to original state
SetOption ("show status bar"), MyStatus

End Function

''''end code module

hello out there-did you get the code yet or are you still having problems with the site?

Thanks jvf...I am currently trying to incorporate the code and I will keep you posted...If I have questions I'll definetly let you know...

Ok jvf the code is working and I ended up using the createobject function to display the data...You are right MicroSoft as a company is a bunch as s*** ..How can you release a suite with applications that don't compliment each other and make Billions...But enough about slick willie...I am having a formating problem and I was wondering if you had any suggestions...The function is creating the chart, but its grabing the data row by row..when this is done the graph format is wrong and the column title that appear on the data sheet are not used in the legend on the chart..But if the data was grabed by column this would produce the desired result. Are there any changes in the code that can be done to produce this result?

jvf,

One other thing..the code in one the modules instructs the graph to be displayed in bar code layout. What would be the command word to change the graph format the line?

Hello jay, in the MakeChart function you will see a line starting with: .Charts.Add
The next line specifies the chart type. In your case it says:.ActiveChart.ChartType = xlColumnClustered

There are some important differences between a “line” chart and an “Xy (Statter)” chart and the way Excel graphs them. Also there are some bugs in the process (some of which I’ve discovered and had published in the microsoft KB). These bugs affect the way Excel interprets the x axis in a line chart and can display unwanted results. Rather than try and explain them for now,  I’ll just have you try each way. I’d suggest starting with the XY chart. Just substitute as necessary and let me know how it turns out.


‘the one I use:
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
‘same but with markers at data points
ActiveChart.ChartType = xlXYScatterLines


‘line chart with markers:
ActiveChart.ChartType = xlLine

‘plain:
ActiveChart.ChartType = xlLineMarkers

Jay, I almost forgot-with a line graph, the column headers can be used as the series legend and you may want to show the legend. Near the end of the MakeChart function you will see where the legend (not appropriate for your column chart) is selected and deleted with two lines of code: .Legend.Select
             XL.Selection.Delete

You may want to add the single apostrophe(‘) in front of these two lines so they will not be processed and the legend will appear.

To restate the problems that you may encounter with the x axis: The first column of data on the spreadsheet is going to try and be the x axis in the Xyscatter and, upon rellection, this is probably not what you want. In a line graph the first column can show up as a series however the x axis will have equal increments. Maybe this is OK for you..

Also you may want to change whether the data is in rows or columns. Underneath the line of code setting the chart type there is this line: .ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlRows

Just change xlRows to xlColumns

Ok just trying to get through some subtle formatting problems, but you definetly have the points because this was definetly grade A code that you gave me. I just have a couple more questions..When the data is transfered from the access report to the excel chart the column heads are scrabled  slightly. For example in the Access report my first field is the date field which the data is grouped by...But when the data appears in the excel chart data sheet the first column title is the totalTestCase field which is my secound field in the Access report. This is causing formatting problems, do you know any ways around this problem. I need the columns to stay in the same order in which they appear in the access report. One other small problem the dates along the x axis are displayed from up to down therefore they appear sideways. Is there anyway to make them display from left to right.  

Thanks.

hi jay, good guess on my part that the chart formatting would be the worst of it, huh? I'll study this later today or over the weekend and see what can be done.

Whew! Trying to guess what your data looks like by telepathy is a brain twister. I am perplexed by one thing. Are the column heads also scrambled by your original macro or just the new code? In either case I have no explanation. However, if it's consistent we can deal with it. If you have dates in the first column of the spreadsheet and they appear as the x axis then you must be plotting by xlColumns (PlotBy:= _
            xlColumns in the code module). This would be true with either a line or column graph. The rest of the spreadsheet column headers (field names) will appear in the legend. So, my guess is that your spreadsheet data wants to look like this:

aaa      totalTestCase      ccc
1/1/2000      3      2
1/8/2000      6      5
2/2/2000      9      8

Used as the x axis, aaa won't show anywhere with totalTestCase and ccc showing in the legend.

If I understand you, the spreadsheet data actually appears like this:

totalTestCase      aaa      ccc
1/1/2000      3      2
1/8/2000      6      5
2/2/2000      9      8

Column heads a,b scrambled but data correct (which seems totally whacked).

As to the appearance of the dates on the x axis your chart may be suffering from what Sting calls "too much information". If you have lots of data groups then the dates get squeezed together and Excel starts to try and tilt them so they don't run over each other. This is a constant problem for me especially when I use the full date/time of day format. You can set the font way small but then nobody can read it. In case I've interpreted your format problem correctly, I'm sending a slightly revised version of the MakeChart code. You can rename the current function to Old_MakeChart and create a new MakeChart function or just save the old code somewhere and copy this version over it. This function switches the a,b headers, makes a column chart (easily changed to a line chart as explained earlier), shows the legend and formats the x axis font to Arial Narrow. Arial Narrow does a good job of squeezing letters close together. You will see this in the code and you can change the font size there also. I had font size 10 at first but commented it out and so Excel picks the size per its usual manner. I also included 2 lines that put the label at each data point of the first series. If the series value changes enough between data points, this will stagger the dates and perhaps make them easier to read. If this sucks just erase or rem them out.  If my guess as to what's going on is not correct please send me another explanation of the problem, perhaps by using a small table like I've done above. Here goes:


'''''revised code for MakeChart  function'''''''''''''''''''''''''
On Error GoTo MyErr:
Dim MyChartName As String
Dim MySheetName As String
Dim MySelection As String
'using status bar here as well so dim rtn value
Dim StatusRtn As Long
MyChartName = MyReportName
With XL
        StatusRtn = SysCmd(acSysCmdSetStatus, "Getting Report")

        .Workbooks.Open MyChart
        .Sheets(1).Select
        MySheetName = XL.Sheets(1).Name
        '--transpose column a and column b headers
        Dim a, b As Variant
        a = .Range("A1")
        b = .Range("B1")
        .Range("A1") = b
        .Range("B1") = a
        '''----------------------------------
           
        .Range("A1").Select
         StatusRtn = SysCmd(acSysCmdSetStatus, "Selecting Data")

        .ActiveCell.CurrentRegion.Select
        MySelection = XL.ActiveWindow.RangeSelection.Address
        StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart-- Please wait...")

        .Charts.Add
        .ActiveChart.ChartType = xlColumnClustered
        .ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlColumns
        XL.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=MyChartName
        With XL.ActiveChart
            .HasTitle = True
                .ChartTitle.Characters.Text = MyChartName
                .Axes(xlCategory, xlPrimary).HasTitle = True
                .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = " "
                .Axes(xlValue, xlPrimary).HasTitle = True
                .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = " "
         '-----adds data labels to first series
            .SeriesCollection(1).Select
            .SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowLabel
         '---------------------------
               
                .Axes(xlCategory).Select
         
        With XL.Selection.TickLabels.Font
            .Name = "Arial Narrow"
            .FontStyle = "Regular"
            '.size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
       
        .Deselect
   
    End With
    .ActiveWindow.Zoom = constChartSize
End With
Exit Function
MyErr:
MsgBox Err.number & vbNewLine & Err.description
'''''''end MakeChart function code'''''''''''''''

Sorry about the last two days it got real hectic around here..The code is great but still have one problem..I tried using the new code and it switched the column headers , but thats not what I needed...Dag it's hard describing this database...But I'll give more examples..The code tranfers the data from the Access report completely and thats great...And the code builds the chart for the user as well (so close). But the chart does not plot all the data that appears in the data sheet of the excel file. For example. In my Access Report these are the fields (PlannedStart, TotalCompleted, TotalPlanned, Total1stPassed, TotalAttempted) in this order...These fields are the column titles in the spreadsheet and the fields in the legend of the chart. When the data is transfered the order is switched to this (TotalAttempted, PlannedStart, TotalCompleted, TotalPlanned, Total1stPassed) this presents a problem because the only data plotted are the columns after PlannedStart. So (TotalCompleted, TotalPlanned, Total1stPassed) are the only fields that appear in the legend and the only fields represented on the chart. I need TotalAttempted to be repesented as well..Do you have any ideals...

hi there, It's about to get hectic for me also. I'm about to install a new HD which means  W2K + programs AGAIN. I gather that (among other things) whole columns are transposed, not just the headers. The first thing I need to know if your original macro transfered the data correctly. If not, you must have transposed them yourself and then made the chart, yes? My small report transfers its columns correctly either way. Also send me the info on each field's data type (text,number, etc.). Are any fields calculated fields or do they all come from a table? If you can make a chart manually we can automate it. Try and describe your manual chart making process, especially if you move things around. If It's involved and I don't quite get it (or even if I do), I may have you turn on the Excel macro recorder, make the chart, and send me the resulting macro for inspection. Armed with this information, I'll ponder a solution.

All of the fields being transposed are numbers with the exception of one date field. Four fields which include (TotalCompleted, TotalPlanned, Total1stPassed, TotalAttempted) are calculated results from a running sum setting that I did in the Access report. The PlannedStart field is the lone date field being transposed. If I was to use the Macro to transpose the data from the reports it would transfer the data but it also jumbles the data a little as well. All I have to do then is make sure the PlannedStart field is the first column on the data sheet. Every column that resides to the right is represented on the chart and in the legend. I don't particularly care about the order of columns to the right of the PlannedStart field, I just need the PlannedStart field to be the first field on the left when viewing the data sheet view in the excel file.

hey! I'm up and running again and just found your last message, I'll study this and return soon.

OK, I've done some investigating. Before changing the code (other than to have you erase the stuff that transposed the column names) I want to discuss something. Are your reports based on querys? Mine is. I have found that If I scramble the query grid's fields around (after saving the query) the original left-right field order is displayed in the query' datasheet (and the report) even though design view will show a different left-right order. HOWEVER, the outputted report (to excel) has a scrambled field order. Perhaps you have switched some query fields around. If so, pick a query, rebuild it (without moving fields around), make a new report and run the code on the new report.
      I am embarrassingly ignorant of report making and so haven’t yet made a report with some calculated fields to see if that could be the cause of this scrambling business. By the way, I’m still not clear if the macro scrambles the data sheet as much as the code. You mentioned that the macro scrambled it “some”, which implies that the two methods differ in the “scrambling” effect. Please clarify this. Do all of the outputted reports exhibit the same behavior? Again, even if we can’t explain this behavior we can deal with it if it’s consistent.  

Sorry for the delay..these stupid deadlines around here...Well I tried writing the queries over and still have the same results. Yes my reports are based on queries, let me explain I just pulled each field I needed in the queries and designated them as count fields grouped by the plannedstart date field. These queries are what my reports are based on, athough in the reports I used the running sum property setting to give me running sums of the count fields being pulled from the queries. After this is done I erase the old fields and the only fields represented are the running sum fields which are based on the fields from the queries. So when new number are entered into the database for these fields the count is reflected in the queries which adds to the running sums in the reports. Now in the queries the fields are in the proper order and in the reports the fields are in the proper order. But once the fields are transposed to the excel data spreadsheet the fields are scrambled. I did a test using the macro and it has the same exact result. I can't figure out why the transfer changes the order of the fields.

Help..

boy, contacting this site lately has been problematic for me. I've copied your last message and will study it. Don't give up! In anticipation of just "going around" this problem are all the reports scrambled in the same way?

Yes they are all scrambled in the same way..

Thanks

P.S. I have been having trouble lately as well

OK, I'll work on it over the holidays. I'll post something as soon as it's avail. I'm going to try and figure out how and why the report scrambles the data. I think it might be the running sums. If I don't get anywhere, I'll send some code that unscrambles the columns based on my understanding of how they are twisted and we'll try that.

I can't duplicate the scrambling yet so let's try some code which may "unscramble" things. Here is another MakeChart function. It's based on my interpretation of how the data is scrambled. As usual, rename the existing MakeChart and paste this in for a test. Try it and see how close we are. If it's still off, do your best to explain how.

''''new code

Public Function MakeChart(XL As Object, MyChart As String, MyReportName As String)
Dim MyChartName As String
Dim MySheetName As String
Dim MySelection As String
Dim a, b As Variant

'using status bar here as well so dim rtn value
Dim StatusRtn As Long
MyChartName = MyReportName
With XL
        StatusRtn = SysCmd(acSysCmdSetStatus, "Getting Report")

        .Workbooks.Open MyChart
        .Sheets(1).Select
        MySheetName = XL.Sheets(1).Name
       
     '----------------------------------
       
       .Columns("A:A").Select
    .Selection.Cut
   
    .Columns("F:F").Select
    .ActiveSheet.Paste
   
    .Columns("A:A").Select
    .Selection.Delete Shift:=xlToLeft
   
    '-----------------------------------
   
       
       
       
       
         .Range("A1").Select
         StatusRtn = SysCmd(acSysCmdSetStatus, "Selecting Data")

        .ActiveCell.CurrentRegion.Select
        MySelection = XL.ActiveWindow.RangeSelection.Address
        StatusRtn = SysCmd(acSysCmdSetStatus, "Making Chart-- Please wait...")

        .Charts.Add
        '.ActiveChart.ChartType = xlColumnClustered
        '.ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlRows
       
         .ActiveChart.ChartType = xlColumnClustered
        .ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlColumns

       
       
       
       
       
       
        '.ActiveChart.ChartType = xlLine
        '.ActiveChart.SetSourceData Source:=XL.Sheets(MySheetName).Range(MySelection), PlotBy:= _
            xlColumns

       
        XL.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=MyChartName
        With XL.ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = MyChartName
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = " "
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = " "
             '.Legend.Select
             'XL.Selection.Delete
            .Deselect
   
        End With
    .ActiveWindow.Zoom = constChartSize
End With
End Function

'''end of code

This code is perfect!!!!!!

Thanks....

I just have one more question dealing with petty formatting. When the chart appears each bar is given a default color on the chart. Is there any way to change the colors for the charted data in the code?

Last and final question.

Thanks man..Top rate code...I look like a genus...