Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: Export to Excel (charts)

    I currently have the VB code below to export 4 tables to excel. I want to get the data for one of the sheets to make a chart automatically. If you caould give me a general idea of code to use, then i can fill in the blanks.

    Thanks!

    Private Sub Command77_Click()
    On Error GoTo Command77Err

    Dim strFile As String
    Dim strStartDate As String, strEndDate As String

    strFile = "C:\Documents and Settings\cadsaf04\Desktop\PMSI Vendor Scorecard.xls"

    DoCmd.TransferSpreadsheet acExport, 8, "PMSI Query - Active", strFile, True, "Active"
    DoCmd.TransferSpreadsheet acExport, 8, "PMSI Query - Sales", strFile, True, "Sales", A3
    DoCmd.TransferSpreadsheet acExport, 8, "PMSI Query - Red Flag Rollup", strFile, True, "Red Flag"
    DoCmd.TransferSpreadsheet acExport, 8, "PMSI Query - Previous Month Active", strFile, True, "Active Grade"
    DoCmd.TransferSpreadsheet acExport, 8, "PMSI Query - Previous Month Sales", strFile, True, "Sales Grade"

    Command77Exit:
    Exit Sub

    Command77Err:

    If Err.Number = 3010 Then
    MsgBox "Please close """ & strFile & """ and try again.", vbInformation, "Error"
    Else
    MsgBox Err.Description

    End If
    Resume Command77Exit

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try the code-bank sticky post at the top of the forum. there is good excel stuff there from pootle (much nicer than TrasferSpreadsheet).

    if i remember correctly one of his examples includes using an Excel template (thus providing your grafix).

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2008
    Posts
    27
    Ya, i tried using that one, but it was too complicated for me. I am no way a VB master, unfortunetly.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by afors112
    Ya, i tried using that one, but it was too complicated for me. I am no way a VB master, unfortunetly.
    I'm afraid there is no easy way to do this one that I know of. TransferSpreadsheet is very simple but the price for that simplicity is very little flexibility.

    The only alternative I can think of (do not know if it is feasible or possible) is to create an excel spreadsheet, create a chart in it. Define the data source for the chart as the data in one of your transferspreadsheet files. Whenever this is over-written the chart should now show the new data.

    Otherwise you will need to roll up your sleeves and work your way through my example. It is well documented - just use a little bit at a time, test, google anything you don't understand, get it working, move on to the next thing. If you break it down like that it will be easier.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •