Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: sending query result(s) to excel and manipulate output

    Dear All
    First off all let me say sorry if you have seen this question on another forum. Unfortunately no one replied to my problem. I hope you can help.

    I have some problems trying to perform the following:

    1.do not start excel every time you run or re-run a query just add a new sheet if excel already open.

    2. can you add a conditional format in excel on last column in for example, if column 1 > than column 3 then change cell fill color to red..



    thanks for your help. Code as follow:

    Function SendToExcelDataChecks(strTableUsing As String, strTitle As String)
    On Error GoTo SendToExcel_Fail

    Dim objWS As Excel.Worksheet
    Dim rstData As ADODB.Recordset
    Dim rstCount As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim intColCount As Integer
    Dim intRowCount As Integer

    Set rstData = New ADODB.Recordset
    rstData.ActiveConnection = CurrentProject.Connection
    Set rstCount = New ADODB.Recordset
    rstCount.ActiveConnection = CurrentProject.Connection

    'Invoke HourGlass
    DoCmd.Hourglass True

    If CreateRecordSet(rstData, rstCount, strTableUsing) Then

    If CreateExcelObj() Then
    'add a workbooks
    With gobjExcel.Workbooks.Add
    'Create a reference to the active sheet
    With .Sheets(1)
    intRowCount = 1
    intColCount = 1

    'Loop through the fields collection
    'make each field name a collumn heading in excel
    For Each fld In rstData.Fields
    If fld.Type <> adLongVarBinary Then
    .Cells(1, intColCount).Value = fld.Name
    intColCount = intColCount + 1
    End If
    Next fld

    'send record set to excel
    .Range("A2").CopyFromRecordset rstData, 16383

    .Range("A1").CurrentRegion.Copy
    End With
    'add a sheet in that LAST tab position
    With .Worksheets.Add(After:=.Sheets(.Worksheets.Count))
    .Range("B5").PasteSpecial Paste:=xlPasteAll, Transpose:=False
    .[C2].Value = strTitle
    .[C2].EntireRow.Font.Bold = True
    .[C2].EntireRow.Font.Size = 12
    .[C2].EntireRow.Font.Name = "Arial"
    .[C2].HorizontalAlignment = xlCenter

    'stretch all the cells to 30 - this maks the auto work better
    '.Cells.EntireColumn.ColumnWidth = 30 'This is for everything if we use cells
    .Cells.EntireColumn.ColumnWidth = 30
    'autofit the columns
    .Cells.EntireColumn.AutoFit


    End With
    End With
    Else
    MsgBox "Excel not Successfully Launched", vbInformation
    End If
    End If

    Exit_SendToExcel:
    DoCmd.Hourglass False
    Set objWS = Nothing
    Set rstCount = Nothing
    Set rstData = Nothing
    Set fld = Nothing


    Exit Function

    SendToExcel_Fail:

    MsgBox "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    Resume Exit_SendToExcel


    End Function


    Function CreateExcelObj() As Boolean
    On Error GoTo CreateExcelObj_Fail

    'Assume a false return value
    CreateExcelObj = False

    'Start Excel
    Set gobjExcel = New Excel.Application

    'if Excel start successfully, return true
    gobjExcel.Visible = True
    CreateExcelObj = True



    Exit_CreateExcelObj:
    Exit Function

    CreateExcelObj_Fail:

    MsgBox "Count not launch Excel.", vbCritical, "Warning"
    CreateExcelObj = False
    Resume Exit_CreateExcelObj

  2. #2
    Join Date
    Dec 2011
    Posts
    3

    Opening exel as a one instance and then add as many sheets as we need based on user r

    I have updated the following queries to test if excel is open if it is add a new sheet as a query is exported to excel. However I still comes back with a new excel so I end up with many excel application instead of one runing and having multiples results SHEETS.

    Function CreateExcelObj() As Boolean
    On Error GoTo CreateExcelObj_Fail


    'Assume a false return value
    CreateExcelObj = False

    'Start Excel
    'Set gobjExcel = New Excel.Application

    Set gobjExcel = GetObject(, "Excel.Application")
    If Err = conAppNotRunning Then Set gobjExcel = New Excel.Application

    'if Excel start successfully, return true
    gobjExcel.Visible = True
    CreateExcelObj = True


    Exit_CreateExcelObj:
    Exit Function

    CreateExcelObj_Fail:

    MsgBox "Count not launch Excel.", vbCritical, "Warning"
    CreateExcelObj = False
    Resume Exit_CreateExcelObj


    End Function

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This code might get a conversation started. I tested it in Office 2003, and
    it might be used if it is necessary to either close Excel or prompt to close
    Excel before running queries in your application. Copy the code to an Access module.
    Maybe someone can suggest a better way, but how about establishing the
    Excel workbook name, then with each new query, open the same workbook,
    add data to a new sheet, save and close.
    Good luck.
    Jerry
    Code:
    Function Test_Funct()
    
        If IsExcelRunning Then
            MsgBox "Excel is running."
        Else
            MsgBox "Excel is not running."
        End If
        
    End Function
    
    Function IsExcelRunning() As Boolean
    Dim sProcessName As String, sComputer As String
    Dim tmpBool As Boolean
    
        sProcessName = "Excel.exe"
        sComputer = "."
        
        Set oWmi = GetObject("winmgmts:" _
            & "{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2")
       
        Set colProcessList = oWmi.ExecQuery _
            ("Select * from Win32_Process Where Name = '" & sProcessName & "'")
        
        For Each oProcess In colProcessList
            tmpBool = True
            'remove the following quote to terminate Excel with no questions asked
            'oProcess.Terminate
        Next
    
        IsExcelRunning = tmpBool
    
    End Function
    Last edited by JerryDal; 12-22-11 at 23:55. Reason: spelling

  4. #4
    Join Date
    Dec 2011
    Posts
    3

    maybe a proper explaination of problem!

    Hi guys

    It really is killing me all I want is to adjust code to stop generating a new instance/runing/opening of excel. it just needs to see if excel is open if open send each query to a new sheet leaving open/existing sheets with results there. hope I explained it properly.

Posting Permissions

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