ajslentz
asked on
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!
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!
ASKER
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.
And don't forget:
Application.DisplayAlerts = True
When done, to reset
Hope this helps
Application.DisplayAlerts = True
When done, to reset
Hope this helps
ASKER
All of the VBA is coming from Access, by the way... I'm not using VBA in excel at all...
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
But it should be inserted right after Excel is loaded an turned off after the copying is done
ASKER
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=
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.DisplayA
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.Ful
'Open Excel Application
Set objxl = New Excel.Application
objxl.Workbooks.Open path
Set objwb = objxl.Application.ActiveWo
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").CopyFrom
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
'objwb.Worksheets("match detail").Range("A5", objWS.Cells.SpecialCells(x
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.Ful
Set objWS = objwb.Worksheets("Overall Summary")
objWS.Range("B30").CopyFro
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.Ful
Set objWS = objwb.Worksheets("Overall Summary")
objWS.Range("D30").CopyFro
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.Ful
objWS.Range("C30").CopyFro
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.Ful
objWS.Range("E30").CopyFro
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.Ful
objWS.Range("B31").CopyFro
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.Ful
objWS.Range("D31").CopyFro
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.Ful
objWS.Range("C31").CopyFro
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.Ful
objWS.Range("E31").CopyFro
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.Ful
objWS.Range("B33").CopyFro
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.Ful
objWS.Range("D33").CopyFro
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.Ful
objWS.Range("C33").CopyFro
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.Ful
objWS.Range("E33").CopyFro
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.Ful
objWS.Range("B34").CopyFro
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.Ful
objWS.Range("D34").CopyFro
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.Ful
objWS.Range("C34").CopyFro
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.Ful
objWS.Range("E34").CopyFro
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')/(SEL
"(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')
"(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')
"(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.Ful
objWS.Range("a56").CopyFro
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'
"(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'
"[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')
"(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')
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.Ful
objWS.Range("a59").CopyFro
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')/(SE
"final_combined_humchc='Y'
"A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')
"As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')
"[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')
"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.Ful
objWS.Range("a68").CopyFro
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.Ful
objWS.Range("a71").CopyFro
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.Ful
objWS.Range("a74").CopyFro
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.Ful
objWS.Range("a77").CopyFro
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.Hum
"AND Final_combined_HumCHC='Y')
"AND A.HumCHC_market=master.Hum
"AND A.HumCHC_market=master.Hum
"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.Ful
Set objWS = objwb.Worksheets("Summary By Market - ISA")
objWS.Range("a8").CopyFrom
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 [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.Ful
Set objWS = objwb.Worksheets("Summary By State")
objWS.Range("a8").CopyFrom
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
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.DisplayA
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.Ful
'Open Excel Application
Set objxl = New Excel.Application
objxl.Workbooks.Open path
Set objwb = objxl.Application.ActiveWo
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").CopyFrom
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
objWS.Range("A6", objWS.Cells.SpecialCells(x
'objwb.Worksheets("match detail").Range("A5", objWS.Cells.SpecialCells(x
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.Ful
Set objWS = objwb.Worksheets("Overall Summary")
objWS.Range("B30").CopyFro
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.Ful
Set objWS = objwb.Worksheets("Overall Summary")
objWS.Range("D30").CopyFro
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.Ful
objWS.Range("C30").CopyFro
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.Ful
objWS.Range("E30").CopyFro
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.Ful
objWS.Range("B31").CopyFro
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.Ful
objWS.Range("D31").CopyFro
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.Ful
objWS.Range("C31").CopyFro
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.Ful
objWS.Range("E31").CopyFro
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.Ful
objWS.Range("B33").CopyFro
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.Ful
objWS.Range("D33").CopyFro
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.Ful
objWS.Range("C33").CopyFro
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.Ful
objWS.Range("E33").CopyFro
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.Ful
objWS.Range("B34").CopyFro
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.Ful
objWS.Range("D34").CopyFro
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.Ful
objWS.Range("C34").CopyFro
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.Ful
objWS.Range("E34").CopyFro
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')/(SEL
"(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')
"(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')
"(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.Ful
objWS.Range("a56").CopyFro
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'
"(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'
"[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')
"(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')
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.Ful
objWS.Range("a59").CopyFro
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')/(SE
"final_combined_humchc='Y'
"A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')
"As A WHERE A.LEVEL_1=master.LEVEL_1 AND final_combined_humchc='Y')
"[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')
"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.Ful
objWS.Range("a68").CopyFro
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.Ful
objWS.Range("a71").CopyFro
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.Ful
objWS.Range("a74").CopyFro
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.Ful
objWS.Range("a77").CopyFro
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.Hum
"AND Final_combined_HumCHC='Y')
"AND A.HumCHC_market=master.Hum
"AND A.HumCHC_market=master.Hum
"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.Ful
Set objWS = objwb.Worksheets("Summary By Market - ISA")
objWS.Range("a8").CopyFrom
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 [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.Ful
Set objWS = objwb.Worksheets("Summary By State")
objWS.Range("a8").CopyFrom
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
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.Ful
'Open Excel Application
Set objxl = New Excel.Application
objxl.Workbooks.Open path
Set objwb = objxl.Application.ActiveWo
Set objWS = objwb.Worksheets("Detail Results")
Excel.Application.DisplayA
i = 1
For Each obj In rsRpt.Fields
objWS.Cells(6, i) = obj.Name
i = i + 1
Next
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.Ful
'Open Excel Application
Set objxl = New Excel.Application
objxl.Workbooks.Open path
Set objwb = objxl.Application.ActiveWo
Set objWS = objwb.Worksheets("Detail Results")
Excel.Application.DisplayA
i = 1
For Each obj In rsRpt.Fields
objWS.Cells(6, i) = obj.Name
i = i + 1
Next
and turn it off here:
objWS.Range("a8").CopyFrom
objxl.Visible = True
'end testing Summary by State
Excel.Application.DisplayA
'Clear all variables @ end
Set objWS = Nothing
Set objwb = Nothing
Set objxl = Nothing
Set rsRpt = Nothing
Set rsNtwk = Nothing
MsgBox ("Summaries Complete")
End Sub
objWS.Range("a8").CopyFrom
objxl.Visible = True
'end testing Summary by State
Excel.Application.DisplayA
'Clear all variables @ end
Set objWS = Nothing
Set objwb = Nothing
Set objxl = Nothing
Set rsRpt = Nothing
Set rsNtwk = Nothing
MsgBox ("Summaries Complete")
End Sub
ASKER
I am testing it now. The query that's causing all the problems takes something like 5 minutes to run.
ASKER
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.
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?
Also when you get the message, can you click OK and continue without any problems?
ASKER
I went into the excel template file and then the VB Editor and put "Excel.Application.Display
ASKER
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.
Hi
try this in the beginning of the code put this
DoCmd.SetWarnings False
try it
try this in the beginning of the code put this
DoCmd.SetWarnings False
try it
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.DisplayA
End Sub
Let me know
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.DisplayA
End Sub
Let me know
Try netcool's suggestion
(It turns off the "Access" warnings)
Thanks netcool!
(It turns off the "Access" warnings)
Thanks netcool!
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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???
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
Peter
Application.DisplayAlerts = False