Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003

    Unanswered: headings not transferred to excel

    i am using some code to export my query to excel to then create a fancy chart, this was a system setup by someone else who has now left the company.

    the code is below, everything works fine except, i dont get the field headings transfered across, can someone please inform me where and how i should modify my code to take the headings across aswell please.



    Option Compare Database
    Option Explicit

    Dim objExcel As Excel.Application 'This will give an error if no reference set to Excel object

    Const DATE_RANGE = 2 'optRange= 2 for entering in a Date Range

    'constant for columns of list boxes that have data
    ' Const MATERIAL_FILTER = 1

    Const GRAPH_QUERY = 1
    Const GRAPH_TEMPLATE = 2

    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    DoCmd.OpenForm "frmGraphsMenu", acNormal, "", "", , acNormal
    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

    Private Sub cmdPreviewOEE_Click()
    GraphMake False
    End Sub

    Private Sub cmdPrint_Click()
    GraphMake True
    End Sub

    Private Sub optRange_AfterUpdate()
    If optRange = DATE_RANGE Then
    txtYearStartOEE.Enabled = True
    txtYearEndOEE.Enabled = True
    txtYearStartOEE.Enabled = False
    txtYearEndOEE.Enabled = False
    End If
    End Sub

    Sub YearPeriodSet()
    Dim dbs As Database
    Dim PeriodTable As Recordset
    Set dbs = DBEngine.Workspaces(0).Databases(0)
    ' Set PeriodTable = dbs.OpenRecordset("tblSystemDataCurrentPeriod")
    'Purpose: Fill start and end year/periods in txt boxes when we want to see all periods
    'Called From: optRange_AfterUpdate
    txtYearStartOEE = 2000
    txtYearEndOEE = 3000
    End Sub

    Private Function RequiredFieldsOK() As Boolean
    On Error GoTo RequiredFieldsOK_Error

    'Ensure that all required fields have data in them
    ' If IsNull(lstMaterial.Column(MATERIAL_FILTER)) Then
    ' MsgBox "Please choose a Material type"
    ' GoTo RequiredFieldsOK_Exit
    ' End If

    If IsNull(lstGraphs) Then
    MsgBox "Please choose a graph"
    GoTo RequiredFieldsOK_Exit
    End If

    RequiredFieldsOK = True

    Exit Function

    MsgBox "Error number " & Err.Number & ": " & Err.Description, vbOKOnly + vbInformation, "RequiredFieldsOK"
    Resume RequiredFieldsOK_Exit

    End Function

    Sub GraphMake(flgPrint As Boolean)

    Dim rs As Recordset
    Dim qdf As QueryDef
    Dim strTemplate As String
    Dim strQuery As String
    On Error GoTo GraphMake_Error

    If RequiredFieldsOK() Then

    'Create recordset and get values to pass to ExcelDataTransfer
    strQuery = lstGraphs.Column(GRAPH_QUERY)
    strTemplate = lstGraphs.Column(GRAPH_TEMPLATE)

    Set qdf = CurrentDb.QueryDefs(strQuery)
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)

    If ExcelDataTransfer(rs, 2, 1, objExcel, strTemplate, "Data") Then

    'Change Title of Graph
    On Error GoTo GraphMake_Exit
    'Error will occur if there is no graph sheet
    On Error GoTo GraphMake_Error
    With objExcel.ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "OEE Trend for line H6 " '& [CmbGraphYearSelectOEE]
    End With

    If flgPrint Then
    End If

    Set objExcel = Nothing
    End If
    End If

    Exit Sub

    MsgBox "Error number " & Err.Number & ": " & Err.Description, vbOKOnly + vbInformation, "GraphMake"
    Resume GraphMake_Exit

    End Sub
    Private Sub OpenCalOEE_Click()
    On Error GoTo Err_OpenCal_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    CalStartRef = "OEEgraphs"

    stDocName = "frmCalender"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit Sub

    MsgBox Err.Description
    Resume Exit_OpenCal_Click

    End Sub

  2. #2
    Join Date
    Oct 2003
    Provided Answers: 1


    Try something like that in your code:

    You go through the table using the Table of Def. and loop writting the fields names to Excel

    PHP Code:
       Dim tbl As TableDefdb as DataBase
       Dim i 
    As Integer
        Set db
       Set tbl 

    0 To tbl.Fields.Count-1
    ... Steps to write the Header to Excel

  3. #3
    Join Date
    Mar 2003
    thanks for the reply, but i am a little confused,

    are you saying to add this to my code?

    and to me who has very little idea of how to use this code that you are suggesting that this would be a table that will not change.

    i have a table that stores 3 or 4 graphs locations etc, then the user would choose the relevant graph from a form.....

    my code aint to clever to be honest, i was hoping i could just slot something into my code that says has fields yes or something like that.

Posting Permissions

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