Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Problem with data being sent to excel from access

I have an access database that copies the results of a query (multiple ones) to Excel using VBA.  One of the queries takes a long time to run (maybe 5 minutes).  About 1/2 way through the process I start getting a popup message in excel that says "Microsoft Excel is waiting for another applicatino to complete an OLE action"

What can I do to eliminate that message so that it won't popup.  The query is running properly and sending the data to excel.

Any ideas on how to suppress this message???

Thanks!
0
ajslentz
Asked:
ajslentz
  • 9
  • 8
  • 2
  • +1
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Try this in Excel:

Application.DisplayAlerts = False
0
 
ajslentzAuthor Commented:
Where would I do that in Excel???  I am not too sharp on working with excel at all.  I'm thinking it's easy but not for me.
0
 
Jeffrey CoachmanMIS LiasonCommented:
And don't forget:

Application.DisplayAlerts = True



When done, to reset

Hope this helps
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ajslentzAuthor Commented:
All of the VBA is coming from Access, by the way...  I'm not using VBA in excel at all...
0
 
Jeffrey CoachmanMIS LiasonCommented:
Post the code and we can take a look,

But it should be inserted right after Excel is loaded an turned off after the copying is done
0
 
ajslentzAuthor Commented:
This is a bunch of access vba stuff but everything goes to excel.  Sorry it's a little messy but I'm working on it.  I have the application.displayalerts=false in there but it isn't making any difference in excel.

Dim db As Database
   
    Dim rsRpt As DAO.Recordset, rsNtwk As DAO.Recordset
    Dim objxl As Excel.Application             'Excel application
    Dim objwb As Excel.Workbook                'Excel workbook
    Dim objWS As Excel.Worksheet               'Excel Worksheet
    Excel.Application.DisplayAlerts = False    'Suppress alerts
    Dim path As String, sel As String, frm As String, grp As String
    Dim obj
    Dim i As Integer

    Set db = CurrentDb
   
    'Populate provider details worksheet
        sel = "SELECT  * "
        frm = "FROM " & Me.Combo1.Value & " "
        grp = " "
   
        Set rsRpt = db.OpenRecordset(sel + frm + grp)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        'Open Excel Application
        Set objxl = New Excel.Application
        objxl.Workbooks.Open path
        Set objwb = objxl.Application.ActiveWorkbook
   
        Set objWS = objwb.Worksheets("Detail Results")
   
        i = 1
        For Each obj In rsRpt.Fields
            objWS.Cells(6, i) = obj.Name
            i = i + 1
        Next
   
        objWS.Range("a7").CopyFromRecordset rsRpt
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlEdgeLeft).LineStyle = xlContinuous
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlEdgeTop).LineStyle = xlContinuous
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlEdgeBottom).LineStyle = xlContinuous
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlEdgeRight).LineStyle = xlContinuous
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlInsideVertical).LineStyle = xlContinuous
        objWS.Range("A6", objWS.Cells.SpecialCells(xlCellTypeLastCell)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
   
        'objwb.Worksheets("match detail").Range("A5", objWS.Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter
        objxl.Visible = True
    'End populate provider details worksheet
   
    'Populate Company Name on all sheets
        Set objWS = objwb.Worksheets("Overall Summary")
        objWS.Range("C2") = Text29.Value
        Set objWS = objwb.Worksheets("Summary By Market - ISA")
        objWS.Range("A1") = Text29.Value
        Set objWS = objwb.Worksheets("Summary By State - ISA")
        objWS.Range("A1") = Text29.Value
        Set objWS = objwb.Worksheets("Summary By State")
        objWS.Range("A1") = Text29.Value
        Set objWS = objwb.Worksheets("Detail Results")
        objWS.Range("A1") = Text29.Value
    'End Populate company name on summary sheet
   
    'Populate Network Name on Summary Sheet
        Set objWS = objwb.Worksheets("Overall Summary")
        objWS.Range("A7") = Combo3.Value
        Set objWS = objwb.Worksheets("Summary By Market - ISA")
        objWS.Range("A3") = Combo3.Value
        Set objWS = objwb.Worksheets("Summary By State - ISA")
        objWS.Range("A3") = Combo3.Value
        Set objWS = objwb.Worksheets("Summary By State")
        objWS.Range("A3") = Combo3.Value
        Set objWS = objwb.Worksheets("Detail Results")
        objWS.Range("A3") = Combo3.Value
    'End populate network name on Summary Sheet
   
    'Populate Available Data Elements on Summary Sheet
        Set objWS = objwb.Worksheets("Overall Summary")
        objWS.Range("C3") = Text37.Value
       
    'End populate Available Data Elements on Summary Sheet
   
    'Populate total count 1
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " """
        grp = " "

        'Set db = CurrentDb
        Set rsRpt = db.OpenRecordset(sel + frm + grp)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        Set objWS = objwb.Worksheets("Overall Summary")
   
        objWS.Range("B30").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total count 1

    'Populate total count 2
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " """
        grp = " "
       
        Set rsRpt = db.OpenRecordset(sel + frm + grp)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        Set objWS = objwb.Worksheets("Overall Summary")
   
        objWS.Range("D30").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total count 2
   
    'Populate total count in SA
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo39.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("C30").CopyFromRecordset rsRpt
        objxl.Visible = True
        'End populate total count in SA
   
    'Populate total count in SA 2
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo39.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("E30").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total count in SA 2
   
    'Populate match count
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo31.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("B31").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match count
   
    'Populate match count 2
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo33.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("D31").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match count 2

    'Populate match count in SA
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo31.Value & "= 'y'" & _
        " and " & Me.Combo39.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("C31").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match count in SA
   
    'Populate match count in SA 2
        sel = "SELECT  count(*) "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo33.Value & "= 'y'" & _
        " and " & Me.Combo39.Value & "= 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("E31").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match count in SA 2
   
    'Populate total paid $
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " "

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("B33").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total paid $
   
    'Populate total paid $ 2
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " "

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("D33").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total paid $ 2
   
    'Populate total paid $ in SA
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo39.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("C33").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total paid $ in SA
   
    'Populate total paid $ in SA 2
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo39.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("E33").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate total paid $ in SA 2
   
    'Populate match paid $
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo31.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("B34").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match paid $
   
    'Populate match paid $ 2
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo33.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("D34").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match paid $ 2
   
    'Populate match paid $ in SA
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo31.Value & "='y' and " & Me.Combo39.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("C34").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match paid $ IN SA
   
    'Populate match paid $ in SA 2
        sel = "SELECT  sum(" & Me.Combo41.Value & ") "
        frm = "FROM " & Me.Combo1.Value & " where " & Me.Combo33.Value & "='y' and " & Me.Combo39.Value & "='y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("E34").CopyFromRecordset rsRpt
        objxl.Visible = True
    'End populate match paid $ IN SA 2
   
    'TESTING K1- TESTING K1- TESTING K1 -WORKS
        sel = "SELECT Count(*) AS [Total Count], " & _
        "(SELECT Count(*) FROM master  WHERE  final_all_humchc='Y') AS [FINAL_ALL Match Count], " & _
        "(SELECT Count(*) FROM master) AS [FINAL_ALL Total Count], " & _
        "(SELECT Count(*) FROM master  WHERE final_all_humchc='Y')/(SELECT Count(*) FROM master) AS [FINAL_ALL Match %], " & _
        "(SELECT sum(master.claims_paid) FROM master  WHERE  final_all_humchc='Y') AS [FINAL_ALL Par Claims Paid], " & _
        "(SELECT sum(master.claims_paid) FROM master) AS [FINAL_ALL Total Claims Paid], " & _
        "(SELECT sum(master.claims_paid) FROM master  WHERE final_all_humchc='Y')/" & _
        "(SELECT sum(master.claims_paid) FROM master) AS [FINAL_ALL % Par Claims Paid], " & _
        "(SELECT Count(*) FROM master  WHERE  FINAL_COMBINED_HUMCHC='Y') AS [FINAL_COMBINED Match Count], " & _
        "(SELECT Count(*) FROM master) AS [FINAL_COMBINED Total Count], " & _
        "(SELECT Count(*) FROM master  WHERE FINAL_COMBINED_HUMCHC='Y')/" & _
        "(SELECT Count(*) FROM master) AS [FINAL_COMBINED Match %], " & _
        "(SELECT sum(master.claims_paid) FROM master  WHERE  FINAL_COMBINED_HUMCHC='Y') AS [FINAL_COMBINED Par Claims Paid], " & _
        "(SELECT sum(master.claims_paid) FROM master) AS [FINAL_COMBINED Total Claims Paid], " & _
        "(SELECT sum(master.claims_paid) FROM master  WHERE FINAL_COMBINED_HUMCHC='Y')/" & _
        "(SELECT sum(master.claims_paid) FROM master) AS [FINAL_COMBINED % Par Claims Paid] "
       
        frm = "FROM Master"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a56").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K1
   
        'TESTING K3- TESTING K3- TESTING K3
        sel = "SELECT master.LEVEL_1 AS LEVEL_1, " & _
        "master.LEVEL_2 AS LEVEL_2, " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND " & _
            "FINAL_ALL_HUMCHC='Y') AS [HUMCHC Match Count Final All], " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS " & _
            "[HUMCHC Total Count Final All], " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y')/" & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS [HUMCHC Count % Match Final All], " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y') " & _
            "AS [HUMCHC CLAIMS MATCH $ Final All], " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS " & _
            "[HUMCHC TOTAL CLAIMS $ Final All], " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND " & _
            "FINAL_ALL_HUMCHC='Y')/ " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS " & _
            "[HUMCHC % Match CLAIMS $ Final All], " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND " & _
            "final_combined_humchc='Y') AS [HUMCHC Match Count Final Combined], " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS " & _
            "[HUMCHC Total Count Final Combined], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND " & _
            "final_combined_humchc='Y')/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS " & _
            "[HUMCHC Count % Match Final Combined], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 " & _
            "AND final_combined_humchc='Y') AS [HUMCHC CLAIMS MATCH $ FINAL COMBINED], " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS [HUMCHC TOTAL CLAIMS $ FINAL COMBINED], " & _
        "(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND final_combined_humchc='Y')/(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2) AS [HUMCHC % Match CLAIMS $ FINAL COMBINED] "
       
       
        frm = "FROM Master GROUP BY MASTER.LEVEL_1, MASTER.LEVEL_2"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a59").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K3
   
    'TESTING K2- TESTING K2- TESTING K2
        sel = "SELECT master.LEVEL_1 AS LEVEL_1, (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y') AS [HUMCHC Match Count Final All], " & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC Total Count Final All], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y')/" & _
        "(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC Count % Match Final All], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y') AS " & _
        "[HUMCHC CLAIMS MATCH $ Final All], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC TOTAL CLAIMS $ Final All], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND " & _
        "final_all_humchc='Y')/(SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC % Match CLAIMS $ Final All], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND " & _
        "final_combined_humchc='Y') AS [HUMCHC Match Count Final Combined], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC Total Count Final Combined], (SELECT Count(*) FROM master As " & _
        "A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS [HUMCHC Count % Match Final Combined], (SELECT sum(claims_paid) FROM master " & _
        "As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y') AS [HUMCHC CLAIMS MATCH $ FINAL COMBINED], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) AS " & _
        "[HUMCHC TOTAL CLAIMS $ FINAL COMBINED], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')/(SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1) " & _
        "AS [HUMCHC % Match CLAIMS $ FINAL COMBINED] "
       
        frm = "FROM MASTER GROUP BY MASTER.LEVEL_1"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a68").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K2

    'TESTING K4- TESTING K4- TESTING K4
        sel = "SELECT Count(*) AS [Total Count]," & _
"(SELECT Count(*) FROM master  WHERE  final_all_humchc='Y' and in_sa_humchc='y') AS [FINAL_ALL Match Count], (SELECT Count(*) FROM master where in_sa_humchc='y') AS [FINAL_ALL Total Count], (SELECT Count(*) FROM master  WHERE final_all_humchc='Y' and in_sa_humchc='y')/(SELECT Count(*) FROM master where in_sa_humchc='y') AS [FINAL_ALL Match %], (SELECT sum(master.claims_paid) FROM master  WHERE  final_all_humchc='Y' and in_sa_humchc='y') AS [FINAL_ALL Par Claims Paid], (SELECT sum(master.claims_paid) FROM master where in_sa_humchc='y') AS [FINAL_ALL Total Claims Paid], (SELECT sum(master.claims_paid) FROM master  WHERE final_all_humchc='Y' and in_sa_humchc='y')/(SELECT sum(master.claims_paid) FROM master where in_sa_humchc='y') AS " & _
"[FINAL_ALL % Par Claims Paid], (SELECT Count(*) FROM master  WHERE  FINAL_COMBINED_HUMCHC='Y' and in_sa_humchc='y') AS [FINAL_COMBINED Match Count], (SELECT Count(*) FROM master where in_sa_humchc='y') AS [FINAL_COMBINED Total Count], (SELECT Count(*) " & _
"FROM master  WHERE FINAL_COMBINED_HUMCHC='Y' and in_sa_humchc='y')/(SELECT Count(*) FROM master where in_sa_humchc='y') AS [FINAL_COMBINED Match %], (SELECT sum(master.claims_paid) FROM master  WHERE  FINAL_COMBINED_HUMCHC='Y' and in_sa_humchc='y') AS [FINAL_COMBINED Par Claims Paid], (SELECT sum(master.claims_paid) FROM master where in_sa_humchc='y') AS [FINAL_COMBINED Total Claims Paid], (SELECT sum(master.claims_paid) FROM master  WHERE FINAL_COMBINED_HUMCHC='Y' and in_sa_humchc='y')/(SELECT sum(master.claims_paid) FROM master where in_sa_humchc='y') " & _
"AS [FINAL_COMBINED % Par Claims Paid] "
       
        frm = "FROM master WHERE in_sa_humchc = 'y'"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a71").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K4
   
    'TESTING K5- TESTING K5- TESTING K5
        sel = "SELECT master.LEVEL_1 AS LEVEL_1, (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y' " & _
        "and in_sa_humchc = 'y') AS [HUMCHC Match Count Final All In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') " & _
        "AS [HUMCHC Total Count Final All in SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y' and in_sa_humchc = 'y')" & _
        "/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') AS [HUMCHC Count % Match Final All In SA], (SELECT sum(claims_paid) FROM " & _
        "master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y' and in_sa_humchc = 'y') AS [HUMCHC CLAIMS MATCH $ Final All In SA], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') " & _
        "AS [HUMCHC TOTAL CLAIMS $ Final All In SA], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_all_humchc='Y' and in_sa_humchc = 'y')/" & _
        "(SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') AS [HUMCHC % Match CLAIMS $ Final All In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y' and in_sa_humchc = 'y') AS [HUMCHC Match Count Final Combined In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') AS [HUMCHC Total Count Final Combined In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y' and in_sa_humchc = 'y')" & _
        "/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and  in_sa_humchc = 'y') AS [HUMCHC Count % Match Final Combined In SA], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y' " & _
        "and in_sa_humchc = 'y') AS [HUMCHC CLAIMS MATCH $ FINAL COMBINED], (SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') AS [HUMCHC TOTAL CLAIMS $ FINAL COMBINED], (SELECT sum(claims_paid) FROM master As A " & _
        "WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y' and in_sa_humchc = 'y')/(SELECT sum(claims_paid) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 and in_sa_humchc = 'y') AS [HUMCHC % Match CLAIMS $ FINAL COMBINED] "
             
        frm = "FROM master GROUP BY MASTER.LEVEL_1"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a74").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K5
   
    'TESTING K6- TESTING K6- TESTING K6
        sel = "SELECT master.LEVEL_1 AS LEVEL_1, master.LEVEL_2 AS LEVEL_2, (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y' " & _
        "and in_sa_humchc = 'y') AS [HUMCHC Match Count Final All In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC Total Count Final All In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y' and in_sa_humchc = 'y')" & _
        "/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC Count % Match Final All In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y' and in_sa_humchc = 'y') AS [HUMCHC CLAIMS MATCH $ Final All In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and  in_sa_humchc= 'y') " & _
        "AS [HUMCHC TOTAL CLAIMS $ Final All In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND FINAL_ALL_HUMCHC='Y' and in_sa_humchc = 'y')/(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC % Match CLAIMS $ Final All In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND final_combined_humchc='Y' " & _
        "and in_sa_humchc = 'y') AS [HUMCHC Match Count Final Combined In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC Total Count Final Combined In SA], (SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND final_combined_humchc='Y' and in_sa_humchc = 'y')/(SELECT Count(*) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS " & _
        "[HUMCHC Count % Match Final Combined In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 AND final_combined_humchc='Y' and in_sa_humchc = 'y') AS [HUMCHC CLAIMS MATCH $ FINAL COMBINED In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC TOTAL CLAIMS $ FINAL COMBINED In SA], (SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 " & _
        "AND final_combined_humchc='Y' and in_sa_humchc = 'y')/(SELECT SUM(CLAIMS_PAID) FROM master As A WHERE A.LEVEL_1=master.LEVEL_1 AND A.LEVEL_2=master.LEVEL_2 and in_sa_humchc = 'y') AS [HUMCHC % Match CLAIMS $ FINAL COMBINED In SA] "
             
        frm = "FROM master GROUP BY MASTER.LEVEL_1, MASTER.LEVEL_2;"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        objWS.Range("a77").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing K6

    'TESTING Summary by Market In SA - Likely needs work on query
        sel = "SELECT master.STATE AS State, master.HumCHC_MARKET AS [Market Area], " & _
        "(SELECT Count(*) FROM master As A WHERE A.State=master.State AND A.HumCHC_market=master.HumCHC_market " & _
        "AND Final_combined_HumCHC='Y') AS [Total Count of Y], (SELECT Count(*) FROM master As A WHERE A.State=master.State " & _
        "AND A.HumCHC_market=master.HumCHC_market) AS [TOTAL], (SELECT Count(*) FROM master As A WHERE A.State=master.State " & _
        "AND A.HumCHC_market=master.HumCHC_market AND Final_combined_HumCHC='Y')/Count(*) " & _
        "AS [humchc_ Y to Total Percent] "
       
        frm = "FROM master WHERE (((master.HumCHC_MARKET) <> 'x ')) GROUP BY master.STATE, master.HumCHC_MARKET"

        'Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")

        Set objWS = objwb.Worksheets("Summary By Market - ISA")
   
        objWS.Range("a8").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing Summary by Market in SA
   
    'TESTING Summary by State
        sel = "SELECT master.STATE AS State, (SELECT Count(*) FROM master As A WHERE A.State=master.State AND " & _
        "Final_combined_humchc='Y') AS [Total Count of Y], (SELECT Count(*) FROM master As A WHERE A.State=master.State ) " & _
        "AS [GRAND TOTAL], (SELECT Count(*) FROM master As A WHERE A.State=master.State AND Final_combined_humchc='Y')/" & _
        "(SELECT Count(*) FROM master As A WHERE A.State=master.State) AS [humchc_ Y to Total Percent] "
       
        frm = "FROM master GROUP BY master.STATE"

        Set rsRpt = db.OpenRecordset(sel + frm)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")

        Set objWS = objwb.Worksheets("Summary By State")
   
        objWS.Range("a8").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing Summary by State

    'Clear all variables @ end
        Set objWS = Nothing
        Set objwb = Nothing
        Set objxl = Nothing
        Set rsRpt = Nothing
        Set rsNtwk = Nothing
       
    MsgBox ("Summaries Complete")

End Sub
0
 
Jeffrey CoachmanMIS LiasonCommented:
I would try it here: ...See the Outdented line

Dim db As Database
   
    Dim rsRpt As DAO.Recordset, rsNtwk As DAO.Recordset
    Dim objxl As Excel.Application             'Excel application
    Dim objwb As Excel.Workbook                'Excel workbook
    Dim objWS As Excel.Worksheet               'Excel Worksheet
    Dim path As String, sel As String, frm As String, grp As String
    Dim obj
    Dim i As Integer

    Set db = CurrentDb
   
    'Populate provider details worksheet
        sel = "SELECT  * "
        frm = "FROM " & Me.Combo1.Value & " "
        grp = " "
   
        Set rsRpt = db.OpenRecordset(sel + frm + grp)
        'Uses the "DA Summary.xlt" file from the desktop
        path = Replace(CurrentProject.FullName, CurrentProject.Name, "DA Summary.xlt")
   
        'Open Excel Application
        Set objxl = New Excel.Application
        objxl.Workbooks.Open path
        Set objwb = objxl.Application.ActiveWorkbook
   
        Set objWS = objwb.Worksheets("Detail Results")
Excel.Application.DisplayAlerts = False    'Suppress alerts
        i = 1
        For Each obj In rsRpt.Fields
            objWS.Cells(6, i) = obj.Name
            i = i + 1
        Next
0
 
Jeffrey CoachmanMIS LiasonCommented:
and turn it off here:

    objWS.Range("a8").CopyFromRecordset rsRpt
        objxl.Visible = True
    'end testing Summary by State
Excel.Application.DisplayAlerts = True    'Turn Alerts Back on
    'Clear all variables @ end
        Set objWS = Nothing
        Set objwb = Nothing
        Set objxl = Nothing
        Set rsRpt = Nothing
        Set rsNtwk = Nothing
       
    MsgBox ("Summaries Complete")

End Sub
0
 
ajslentzAuthor Commented:
I am testing it now. The query that's causing all the problems takes something like 5 minutes to run.
0
 
ajslentzAuthor Commented:
It's still there...  The error message that is...  Is there some way to go into excel and enter the statement to suppress the messages???  I'm using a template (.xlt) file so if I can put it in there maybe that will take care of it.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Do you know what section of code (Which query) generates the error?

Also when you get the message, can you click OK and continue without any problems?
0
 
ajslentzAuthor Commented:
I went into the excel template file and then the VB Editor and put "Excel.Application.DisplayAlerts = False    'Suppress alerts" under "this worksheet" and still got the popup.
0
 
ajslentzAuthor Commented:
I put the displayalerts right before the query that was causing the problem and it didn't help.  I can click ok on the popup and Excel works fine.
0
 
netcoolCommented:
Hi

try this in the beginning of the code put this

DoCmd.SetWarnings False

try it
0
 
Jeffrey CoachmanMIS LiasonCommented:
Go into the Excel template In VB and Double click "This Workbook" (Should be on the right hand side)

There should be two dropdowns across the top, From the first one (The "Object" Box) select "Workbook"

The default code for the Workbook open Event should appear, try it here:

Private Sub Workbook_Open()
    Excel.Application.DisplayAlerts = False    'Suppress alerts
End Sub


Let me know
0
 
Jeffrey CoachmanMIS LiasonCommented:
Try netcool's suggestion

(It turns off the "Access" warnings)

Thanks netcool!
0
 
Jeffrey CoachmanMIS LiasonCommented:
ajslentz,

Been looking around,

Another company had the same problem as you. They felt that since the error message could be dismissed by clicking OK and the code runs fine, they would not worry about it. (Makes sense)

They have a solution where they press OK "Programmatically" every few minutes or so, until the code stops.

Here is the link and useful part of the article, perhaps you and your programmers will find it useful:
http://www.bioresearchers.com/2/5/bioscreen_c_how_to.htm

A message "Microsoft Excel is waiting for another application to complete an OLE action" (OLE message) is displayed by a software MS Excel (not by a software Research Express) at moments when MS Excel is busy with recalculations or with self-refreshment of MS Excel workbook and MS Excel cannot take remote commands (from a human person or from a software Research Express).
Unfortunately a Microsoft Corp. has designed a MS Excel in a way that this message "Microsoft Excel is waiting for another application to complete an OLE action" does not disappear by itself but needs a pressing of OK. Transgalactic Ltd has solved a message non-disappearing problem by pressing OK of this message programmatically whenever this message is found by software Research Express. A software Research Express looks for this message several times per minute as a mean (once per 2 minutes or so if a computer is very busy). At time when MS Windows or MS Excel is busy (because of recalculations or refreshments of windows or sheets) then all other software including Research Express are paused and a Research Express cannot look for OLE messages. After MS Windows finishes internal refreshments a MS Windows gives again a permission to other software (to Research Express) to continue a run.
One should not worry when seeing an OLE message ("Microsoft Excel is waiting for another application to complete an OLE action") on screen for a short time. With today's quick computers these OLE messages are on screen only for a few seconds. With older and slower computers or with new computers (when using too large xls files i.e. too many workbook sheets or too many kinetic measurement points) or when to use continuous shaking feature or when running other software simultaneously (network cards or virus scanners) the OLE messages can appear and stay on screen for a longer time. Eventually the OLE messages should disappear when using healthy computers.
In case that an OLE message does not disappear a computer quality is bad for a long kinetic run. Approximately 2-5% of all computers (new or old) of any brand name are not suitable for kinetic run via COM ports and will stop randomly by freezing the whole computer. Here the only solution is to replace a computer to another one. An OLE message itself does not stop a run but if a computer was stopped at time when an OLE message was displayed the OLE message will remain on screen (screen is frozen).
Transgalactic Ltd is testing a software with freshly installed MS Windows where are no other software than a MS Office and a Research Express. We cannot guarantee the work together with other virus scanning or e-mailing etc software installed on same computer (several other computer software can take system resources even if these are not started). Also, MS Windows users know that a MS Windows itself gets bad after a long time of operation (windows registry size and overall size grows continuously). It is recommended that MS Windows drive will be restored from a fresh disk-image file once per year or even more often. To repair a a damaged MS Windows in case that a disk image is not taken after installation of MS Windows, a fresh installation of windows together with a formatting of hard disk is needed - a repair install often is not enough to repair a damaged MS Windows or MS Excel.
0
 
Bat17Commented:
If you know which query is causing the problem you could try running a make table query first to create a tempoary table and pass that to Excel.

peter
0
 
ajslentzAuthor Commented:
I have tried everything that everyone's suggested and that annoying error won't quit.  How would I has the contents of a table to excel???  Should I run a maketable query and then another query to load the contents of the table into a recordset and then take the recordset and copy it to excel???
0
 
Bat17Commented:
Yes, make the table before you call Excel, I assume the delay is from the query pulling out the data not passing it to Excel

Peter
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now