Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2007

    Unhappy Unanswered: editting report generated from DoCmd.OutputTo problem


    Here's my problem. I use DoCmd.OutputTo to generate a report from my query and I want to do a bit of editting of the report format (such as borders,etc) through codes.

    However, after finishing the coding and compilation, I found that the excel file generated was exactly the same with the one before editting. It's as if it didn't run through my codes at all. There's no error showing up during the execution of the codes.

    this is the code that outputs my query to a file.
    Private Sub Cashflow_Click()
     On Error GoTo Err_Cashflow_Click
        Dim strExcelFilter As String, strExcelPath As String
        Dim dbCurrent As Database, rstUser As Recordset
        strExcelFilter = fileIO.ahtAddFilterItem(strExcelFilter, _
            "Excel Files (*.XLS)", "*.XLS")
        strExcelPath = fileIO.ahtCommonFileOpenSave( _
            Filter:=strExcelFilter, OpenFile:=False, _
            FileName:="Cashflow forecast.xls", _
            DialogTitle:="Please select an output file...", _
        Set rstUser = Nothing
        Set dbCurrent = Nothing
        DoCmd.OutputTo acOutputQuery, "qryCashFlow", acFormatXLS, strExcelPath
        excelFormat.CashFlowFormat strExcelPath
        Exit Sub
        MsgBox Err.Description
        Resume Exit_Cashflow_Click
    End Sub
    this is the code where I do the editting. I changed the entire editting codes into

    ObjXLWS.Cells(1,1).value = "hello"

    Because this simple line also isn't executed as well.
    Public Sub CashFlowFormat(ByVal strExcelPath As String)
        Dim objXL As Excel.Application, ObjXLWB As Excel.Workbook, ObjXLWS As Excel.Worksheet
        Dim longMaxRow As Long, intMaxCol As Integer
        Dim rngCells As Range
        Dim qryCashFlowQuery As QueryDef
        Dim strDateStart As String, strDateEnd As String
        Dim strFileName As String
        Dim recLength As Integer, k As Integer
        Dim monthDate$, yearDate$
        On Error GoTo Err_CashFlowFormat
        'open workbook
        Set objXL = New Excel.Application
        Set ObjXLWB = objXL.Workbooks.Open(strExcelPath)
        Set ObjXLWS = ObjXLWB.Worksheets(1)
        objXL.DisplayAlerts = False
        objXL.Visible = False
        ObjXLWB.Windows(1).Zoom = 75
        If ObjXLWS.Cells(1, 1).Value = "" Then
            MsgBox "there isn't any invoices between dates"
            GoTo Exit_CashFlowFormat
        End If
        ObjXLWS.Cells(1,1).value = "hello"
        Set objXL = Nothing
        Set ObjXLWS = Nothing
        Set ObjXLWB = Nothing
        Exit Sub
        Debug.Print Err.Number
        Debug.Print Err.Description
        MsgBox Err.Description
        resume exit_CashFlowFormat
    End Sub
    I've been looking at these codes for weeks, but still can't figure it out. Anyone know how to solve this problem?

    I'm using Access 2000.

    thanks in advance,
    Last edited by olimin; 04-02-07 at 06:16.

Posting Permissions

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