Exporting to an Excel Template Question

[Sydney1]

Hi,

How would I go about exporting a report with a subreport (or 2 different queries) from Access to an Excel Template where all of the Access fields need to go into certain Excel cell fields? Making the template is no problem, but I am at a loss of how to write the exporting code.

Thanks in advance for your help.

Sydney

 

[SkipVought]

Hi,

First export to a blank sheet.

On your Excel sheet where you want the data, reference each of the cells on the export sheet.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Sydney1]

Hi,

Thanks for the reply. Sorry I didn't mention that this method may not work, because the users need to copy some of the Excel sheets and put them into one large Excel file that may include 50 sheets. So, I envision that the Access fields need to be hard coded into specific fields and not linked to another sheet. This way they can be easily copied and put into another file.

Thanks for your help

Sydney

 

[SkipVought]

It would still work. Might be best to Copy 'n' Paste Special - VALUES.

Other than that, it would take some code. I'd still do the export to a blank sheet and then write each value to the "form".

If you NAME each cell in the form consistent with the Field Name from the table or report, then the logic becomes a bit simpler.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Sydney1]

Hi,

Cool, Paste Special took care of that problem. Thanks for the lesson and your help.

Sydney

 

[jimmythegeek]

Sydney, here is some sample code (from a long time ago) that I used to populate an Excel template with data from Access. If you can use some of the example, great, if not then keep it for future reference.

You can be pretty specific as to what sheets and cells you want to work with in your code. Plus, you can actually watch the Excel template open and populate.

I have newer code, but I can't seem to find it.

==========================================================

Private qry As String
Private db As Database
Private rs As Recordset
Private xl As Object
Private x As Integer
Private Const mydate = "9/1/98"
---------------------------------------------
Public Function FillExcelSheet()
Dim a As Integer
Dim List(1 To 11) As String
Dim Data(1 To 30) As String

List(1) = GetPCP("t_pcmaster98", ".[PCP Name]")
List(2) = GetPCP("t_qmmaster98", ".pcpname")
List(3) = GetPCP("t_ncmaster98", ".[Provider Name]")
List(4) = GetPCP("t_nspcmaster98", ".PCPName")
List(5) = GetPCP("t_nsfcmaster98", ".pcpname")
List(6) = GetPCP("t_fcmaster98", ".pcpname")
List(7) = GetPCP("t_hmowpsimaster98", ".pcpname")
List(8) = GetPCP("t_hmowpspamaster98", ".pcpname")
List(9) = GetPCP("t_rbslcmsomaster98", ".pcpname")
List(10) = GetPCP("t_rbspsimaster98", ".pcpname")
List(11) = GetPCP("t_rbspspamaster98", ".pcpname")

Data(1) = GetStuff("PSPA", "Pacificare", "Commercial")
Data(2) = GetStuff("PSPA", "Pacificare", "Point of Service")
Data(3) = GetStuff("PSPA", "Pacificare", "Public Employees Benefits Board")
Data(4) = GetStuff("PSPA", "Pacificare", "Secure Horizons")
Data(5) = GetStuff("PSPA", "Qual-Med", "Commercial")
Data(6) = GetStuff("PSPA", "Qual-Med", "BHP Subsidized")
Data(7) = GetStuff("PSPA", "Qual-Med", "BHP Non-Subsidized")
Data(8) = GetStuff("PSPA", "Qual-Med", "BHP Plus")
Data(9) = GetStuff("PSPA", "Qual-Med", "Healthy Options")
Data(10) = GetStuff("PSPA", "Qual-Med", "Senior Security")
Data(11) = GetStuff("PSPA", "NYLCare", "Commercial")
Data(12) = GetStuff("PSPA", "NYLCare", "BHP Subsidized")
Data(13) = GetStuff("PSPA", "NYLCare", "BHP Subsidized (Preg)")
Data(14) = GetStuff("PSPA", "NYLCare", "BHP Non-Subsidized")
Data(15) = GetStuff("PSPA", "NYLCARE", "BHP Non-Subsidized (Over 18)")
Data(16) = GetStuff("PSPA", "NYLCare", "BHP Plus")
Data(17) = GetStuff("PSPA", "NYLCare", "BHP Plus (S Med)")
Data(18) = GetStuff("PSPA", "NYLCare", "Healthy Options")
Data(19) = GetStuff("PSPA", "NYLCare", "Public Employees Benefits Board")
Data(20) = GetStuff("NSCHS", "Pacificare", "Commercial")
Data(21) = GetStuff("NSCHS", "Pacificare", "Point of Service")
Data(22) = GetStuff("NSCHS", "Pacificare", "Public Employees Benefits Board")
Data(23) = GetStuff("NSCHS", "Pacificare", "Secure Horizons")
Data(24) = GetStuff("NSCHS", "First Choice", "Commercial")
Data(25) = GetStuff("PSPA", "First Choice", "Commercial")
Data(26) = GetStuff("PSI", "RegenceCare", "Commercial")
Data(27) = GetStuff("PSPA", "RegenceCare", "Commercial")
Data(28) = GetStuff("LCMSO", "Regence Blue Shield", "Healthy Options")
Data(29) = GetStuff("PSI", "Regence Blue Shield", "Healthy Options")
Data(30) = GetStuff("PSPA", "Regence Blue Shield", "Healthy Options")

Set db = CurrentDb()
Set xl = GetObject("J:\Eligibility\EligByPCP_Template98.xlt")
xl.Application.Visible = True
xl.Parent.Windows(1).Visible = True

a = 8
For i = 1 To 11
Set rs = db.OpenRecordset(List(i), DB_OPEN_SNAPSHOT)
xl.worksheets(a).Activate
x = 1
Do Until rs.EOF
xl.Activesheet.Cells(x, 1).Value = rs!pcp
x = x + 1
rs.MoveNext
Loop
a = a + 1
Next i

rs.Close

a = 19
For i = 1 To 30
Set rs = db.OpenRecordset(Data(i), DB_OPEN_SNAPSHOT)
xl.worksheets(a).Activate
x = 1
Do Until rs.EOF
xl.Activesheet.Cells(x, 1).Value = rs!pcp
xl.Activesheet.Cells(x, 2).Value = rs!total
x = x + 1
rs.MoveNext
Loop
a = a + 1
Next i

rs.Close
db.Close

End Function
----------------------------------------------
Function GetStuff(ByVal myipa, myplan, mylob) As String
qry = "SELECT [Lname] & ', ' & [Fname] AS PCP, Count(u_eligcount98_3.memno) AS total "
qry = qry & "FROM u_eligcount98_3 INNER JOIN t_pcpmaster ON u_eligcount98_3.pcpno = t_pcpmaster.ProvID "
qry = qry & "GROUP BY [Lname] & ', ' & [Fname], u_eligcount98_3.eligmo, u_eligcount98_3.ipa, u_eligcount98_3.plan, u_eligcount98_3.lob "
qry = qry & "HAVING (((u_eligcount98_3.eligmo)=#" & mydate & "#) AND ((u_eligcount98_3.ipa)='" & myipa & "') "
qry = qry & "AND ((u_eligcount98_3.plan)='" & myplan & "') AND ((u_eligcount98_3.lob)='" & mylob & "')) "
qry = qry & "ORDER BY [Lname] & ', ' & [Fname];"
GetStuff = qry
End Function
---------------------------------------------
Function GetPCP(ByVal mytable, myfield) As String
qry = "SELECT [Lname] & ', ' & [Fname] AS PCP "
qry = qry & "FROM (" & mytable & " INNER JOIN t_pcpnames ON " & mytable & myfield & " = t_pcpnames.pcpname) "
qry = qry & "INNER JOIN t_pcpmaster ON t_pcpnames.pcpid = t_pcpmaster.ProvID "
qry = qry & "GROUP BY [Lname] & ', ' & [Fname] "
qry = qry & "ORDER BY [Lname] & ', ' & [Fname];"
GetPCP = qry
End Function

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?