export query result from Access 2003 to specific cells in excel 2007

[max-75]

Hi ,

I'm trying to create a dashboard using Access 2003. Being a newbie I
would request your help.
I found some codes in the net but not fulfilled my needs (I might not
have the correct key words)

here is what I 'm trying to do. Please feel free to give me your
opinion on my strategy.

1/ having passthru queries and create-tables to have a local
subdataset.
2/ Having a set of parametered queries, graphs and cross tab.
3/ export the results to a specific template with a specific
spreadsheet and cell so that my excel template just has to read the
data (keeping safe the references)

Could you please comment and share your experience about reporting
with access.

Could you also provide and more important comment the script where I
specify a specific cell in an existing template so that access export
the data or graph?

Thanks for your help (sorry for this long message)

max

 

[Duane Hookom]

I recently created some functionality to push query field values to a
specific cell in Excel much like a Word Merge. I created a "mapping" table
that identified the field from the query and the cell address in Excel. My
code then opened recordsets of both the mapping and data. I opened the Excel
file and looped through the mapping records to push the data fields to the
appropriate cell:

Private Sub cmdMergeToExcel_Click()
Dim strFile As String
Dim oXLS As Excel.Application
Dim oWkb As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsComp As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT exmFieldName, exmCell FROM
ztblExcelMergeAddresses WHERE exmCell Is Not Null")
Set rsComp = db.OpenRecordset("SELECT * FROM tblData WHERE [ID] = " &
Me.ID)
strFile = "C:\TEMP\MyApp\MyTemplate.xls"
Set oXLS = CreateObject("Excel.Application")
oXLS.Workbooks.Open strFile
Set oWkb = oXLS.Workbooks(1)
With rs
.MoveFirst
Do Until .EOF
oWkb.Worksheets(1).Range(!exmCell) = rsComp(!exmFieldName)
.MoveNext
Loop
.Close
End With
rsComp.Close
Set rsComp = Nothing
Set rs = Nothing
Set db = Nothing
oXLS.Visible = True
Set oXLS = Nothing
Set oWkb = Nothing
End Sub

 

[max-75]

I recently created some functionality to push query field values to a
specific cell in Excel much like a Word Merge. I created a "mapping" table
that identified the field from the query and the cell address in Excel. My
code then opened recordsets of both the mapping and data. I opened the Excel
file and looped through the mapping records to push the data fields to the
appropriate cell:

Private Sub cmdMergeToExcel_Click()
    Dim strFile As String
    Dim oXLS As Excel.Application
    Dim oWkb As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsComp As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT exmFieldName, exmCell FROM
ztblExcelMergeAddresses WHERE exmCell Is Not Null")
    Set rsComp = db.OpenRecordset("SELECT * FROM tblData WHERE [ID]= " &
Me.ID)
    strFile = "C:\TEMP\MyApp\MyTemplate.xls"
    Set oXLS = CreateObject("Excel.Application")
    oXLS.Workbooks.Open strFile
    Set oWkb = oXLS.Workbooks(1)
    With rs
        .MoveFirst
        Do Until .EOF
            oWkb.Worksheets(1).Range(!exmCell) = rsComp(!exmFieldName)
            .MoveNext
        Loop
        .Close
    End With
    rsComp.Close
    Set rsComp = Nothing
    Set rs = Nothing
    Set db = Nothing
    oXLS.Visible = True
    Set oXLS = Nothing
    Set oWkb = Nothing
End Sub

--
Duane Hookom
Microsoft Access MVP

Hi ,

I'm trying to create a dashboard using Access 2003. Being a newbie I
would request your help.
I found some codes in the net but not fulfilled my needs (I might not
have the correct key words)

here is what I 'm trying to do. Please feel free to give me your
opinion on my strategy.

1/ having passthru queries and create-tables to have a local
subdataset.
2/ Having a set of parametered queries, graphs and cross tab.
3/ export the results to a specific template with a specific
spreadsheet and cell so that my excel template just has to read the
data (keeping safe the references)

Could you please comment and share your experience about reporting
with access.

Could you also provide and more important comment the script where I
specify a specific cell in an existing template so that access export
the data or graph?

Thanks for your help (sorry for this long message)

max
.- Hide quoted text -

- Show quoted text -

Thank you Duane,

I feel like this can help me however my limited knowledge in coding
did not allow me to adapt it.
If I understand your explaination, you are using some temp worksheets
right?
Could you please precise where I enter the different query names to
export and the destination of the result (ex: Qr_1 result goes to c
\report\productionRpt.xls, worksheet "tomatoes" upper left cell:
A2...Qr_n result goes to c\report\productionRpt.xls, worksheet
"tomatoes" upper left cell: U2 etc...)
I'd like to be able to manage the worksheet and range per Qry.
Indeed I have a summary worksheet with vllookup which need to read the
date. Creating new tabs results in ref# errors.

thank you again

max

 

[Duane Hookom]

My code doesn't use temporary worksheets. It simply takes field values from
one record in tblData and pushes them to a cell in one Excel file. Don't even
think about taking multiple records and pushing them into multiple files
until you can do this with one.

The first task is to create a table [ztblExcelmergeAddresses] with at least
these text fields: [exmFieldName] and [exmCell]. Since you have multiple
Excel files to push the results, you might want to add a field for the Excel
file name [exmXLSFileName].

Then you enter the field names and the cell addresses to push the field
values.

Modify the code above to use your query/table in place of tblData and your
file in this line:
strFile = "C:\TEMP\MyApp\MyTemplate.xls"

My code runs in the form containing most of the data I want to merge to Excel.
--
Duane Hookom
Microsoft Access MVP

I recently created some functionality to push query field values to a
specific cell in Excel much like a Word Merge. I created a "mapping" table
that identified the field from the query and the cell address in Excel. My
code then opened recordsets of both the mapping and data. I opened the Excel
file and looped through the mapping records to push the data fields to the
appropriate cell:

Private Sub cmdMergeToExcel_Click()
Dim strFile As String
Dim oXLS As Excel.Application
Dim oWkb As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsComp As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT exmFieldName, exmCell FROM
ztblExcelMergeAddresses WHERE exmCell Is Not Null")
Set rsComp = db.OpenRecordset("SELECT * FROM tblData WHERE [ID] = " &
Me.ID)
strFile = "C:\TEMP\MyApp\MyTemplate.xls"
Set oXLS = CreateObject("Excel.Application")
oXLS.Workbooks.Open strFile
Set oWkb = oXLS.Workbooks(1)
With rs
.MoveFirst
Do Until .EOF
oWkb.Worksheets(1).Range(!exmCell) = rsComp(!exmFieldName)
.MoveNext
Loop
.Close
End With
rsComp.Close
Set rsComp = Nothing
Set rs = Nothing
Set db = Nothing
oXLS.Visible = True
Set oXLS = Nothing
Set oWkb = Nothing
End Sub

--
Duane Hookom
Microsoft Access MVP

Hi ,

I'm trying to create a dashboard using Access 2003. Being a newbie I
would request your help.
I found some codes in the net but not fulfilled my needs (I might not
have the correct key words)

here is what I 'm trying to do. Please feel free to give me your
opinion on my strategy.

1/ having passthru queries and create-tables to have a local
subdataset.
2/ Having a set of parametered queries, graphs and cross tab.
3/ export the results to a specific template with a specific
spreadsheet and cell so that my excel template just has to read the
data (keeping safe the references)

Could you please comment and share your experience about reporting
with access.

Could you also provide and more important comment the script where I
specify a specific cell in an existing template so that access export
the data or graph?

Thanks for your help (sorry for this long message)

max
.- Hide quoted text -

- Show quoted text -

Thank you Duane,

I feel like this can help me however my limited knowledge in coding
did not allow me to adapt it.
If I understand your explaination, you are using some temp worksheets
right?
Could you please precise where I enter the different query names to
export and the destination of the result (ex: Qr_1 result goes to c
\report\productionRpt.xls, worksheet "tomatoes" upper left cell:
A2...Qr_n result goes to c\report\productionRpt.xls, worksheet
"tomatoes" upper left cell: U2 etc...)
I'd like to be able to manage the worksheet and range per Qry.
Indeed I have a summary worksheet with vllookup which need to read the
date. Creating new tabs results in ref# errors.

thank you again

max
.

 

[max-75]

My code doesn't use temporary worksheets. It simply takes field values from
one record in tblData and pushes them to a cell in one Excel file. Don't even
think about taking multiple records and pushing them into multiple files
until you can do this with one.

The first task is to create a table [ztblExcelmergeAddresses] with at least
these text fields: [exmFieldName] and [exmCell]. Since you have multiple
Excel files to push the results, you might want to add a field for the Excel
file name [exmXLSFileName].

Then you enter the field names and the cell addresses to push the field
values.

Modify the code above to use your query/table in place of tblData and your
file in this line:
strFile = "C:\TEMP\MyApp\MyTemplate.xls"

My code runs in the form containing most of the data I want to merge to Excel.
--
Duane Hookom
Microsoft Access MVP

I recently created some functionality to push query field values to a
specific cell in Excel much like a Word Merge. I created a "mapping" table
that identified the field from the query and the cell address in Excel. My
code then opened recordsets of both the mapping and data. I opened the Excel
file and looped through the mapping records to push the data fields to the
appropriate cell:
Private Sub cmdMergeToExcel_Click()
    Dim strFile As String
    Dim oXLS As Excel.Application
    Dim oWkb As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsComp As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT exmFieldName, exmCell FROM
ztblExcelMergeAddresses WHERE exmCell Is Not Null")
    Set rsComp = db.OpenRecordset("SELECT * FROM tblData WHERE [ID] = " &
Me.ID)
    strFile = "C:\TEMP\MyApp\MyTemplate.xls"
    Set oXLS = CreateObject("Excel.Application")
    oXLS.Workbooks.Open strFile
    Set oWkb = oXLS.Workbooks(1)
    With rs
        .MoveFirst
        Do Until .EOF
            oWkb.Worksheets(1).Range(!exmCell) = rsComp(!exmFieldName)
            .MoveNext
        Loop
        .Close
    End With
    rsComp.Close
    Set rsComp = Nothing
    Set rs = Nothing
    Set db = Nothing
    oXLS.Visible = True
    Set oXLS = Nothing
    Set oWkb = Nothing
End Sub
--
Duane Hookom
Microsoft Access MVP
:
Hi ,
I'm trying to create a dashboard using Access 2003. Being a newbie I
would request your help.
I found some codes in the net but not fulfilled my needs (I might not
have the correct key words)
here is what I 'm trying to do. Please feel free to give me your
opinion on my strategy.
1/ having passthru queries and create-tables to have a local
subdataset.
2/ Having a set of parametered queries, graphs and cross tab.
3/ export the results to a specific template with a specific
spreadsheet and cell so that my excel template just has to read the
data (keeping safe the references)
Could you please comment and share your experience about reporting
with access.
Could you also provide and more important comment the script where I
specify a specific cell in an existing template so that access export
the data or graph?
Thanks for your help (sorry for this long message)
max
.- Hide quoted text -
- Show quoted text -

Thank you Duane,

I feel like this can help me however my limited knowledge in coding
did not allow me to adapt it.
If I understand your explaination, you are using some temp worksheets
right?
Could you please precise where I enter the different query names to
export and the destination of the result (ex: Qr_1 result goes to c
\report\productionRpt.xls, worksheet "tomatoes" upper left cell:
A2...Qr_n result goes to c\report\productionRpt.xls, worksheet
"tomatoes" upper left cell: U2 etc...)
I'd like to be able to manage the worksheet and range per Qry.
Indeed I have a summary worksheet with vllookup which need to read the
date. Creating new tabs results in ref# errors.

thank you again

max
.- Hide quoted text -

- Show quoted text -

Thx duane, I try later on this week or early next week and keep you
posted!

max

 

[max-75]

My code doesn't use temporary worksheets. It simply takes field values from
one record in tblData and pushes them to a cell in one Excel file. Don't even
think about taking multiple records and pushing them into multiple files
until you can do this with one.

The first task is to create a table [ztblExcelmergeAddresses] with at least
these text fields: [exmFieldName] and [exmCell]. Since you have multiple
Excel files to push the results, you might want to add a field for the Excel
file name [exmXLSFileName].

Then you enter the field names and the cell addresses to push the field
values.

Modify the code above to use your query/table in place of tblData and your
file in this line:
strFile = "C:\TEMP\MyApp\MyTemplate.xls"

My code runs in the form containing most of the data I want to merge toExcel.

On Nov 11, 10:05 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I recently created some functionality to push query field values toa
specific cell in Excel much like a Word Merge. I created a "mapping" table
that identified the field from the query and the cell address in Excel. My
code then opened recordsets of both the mapping and data. I opened the Excel
file and looped through the mapping records to push the data fieldsto the
appropriate cell:
Private Sub cmdMergeToExcel_Click()
    Dim strFile As String
    Dim oXLS As Excel.Application
    Dim oWkb As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsComp As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT exmFieldName, exmCell FROM
ztblExcelMergeAddresses WHERE exmCell Is Not Null")
    Set rsComp = db.OpenRecordset("SELECT * FROM tblData WHERE [ID] = " &
Me.ID)
    strFile = "C:\TEMP\MyApp\MyTemplate.xls"
    Set oXLS = CreateObject("Excel.Application")
    oXLS.Workbooks.Open strFile
    Set oWkb = oXLS.Workbooks(1)
    With rs
        .MoveFirst
        Do Until .EOF
            oWkb.Worksheets(1).Range(!exmCell) = rsComp(!exmFieldName)
            .MoveNext
        Loop
        .Close
    End With
    rsComp.Close
    Set rsComp = Nothing
    Set rs = Nothing
    Set db = Nothing
    oXLS.Visible = True
    Set oXLS = Nothing
    Set oWkb = Nothing
End Sub
--
Duane Hookom
Microsoft Access MVP
:
Hi ,
I'm trying to create a dashboard using Access 2003. Being a newbie I
would request your help.
I found some codes in the net but not fulfilled my needs (I mightnot
have the correct key words)
here is what I 'm trying to do. Please feel free to give me your
opinion on my strategy.
1/ having passthru queries and create-tables to have a local
subdataset.
2/ Having a set of parametered queries, graphs and cross tab.
3/ export the results to a specific template with a specific
spreadsheet and cell so that my excel template just has to read the
data (keeping safe the references)
Could you please comment and share your experience about reporting
with access.
Could you also provide and more important comment the script where I
specify a specific cell in an existing template so that access export
the data or graph?
Thanks for your help (sorry for this long message)
max
.- Hide quoted text -
- Show quoted text -
Thank you Duane,
I feel like this can help me however my limited knowledge in coding
did not allow me to adapt it.
If I understand your explaination, you are using some temp worksheets
right?
Could you please precise where I enter the different query names to
export and the destination of the result (ex: Qr_1 result goes to c
\report\productionRpt.xls, worksheet "tomatoes" upper left cell:
A2...Qr_n result goes to c\report\productionRpt.xls, worksheet
"tomatoes" upper left cell: U2 etc...)
I'd like to be able to manage the worksheet and range per Qry.
Indeed I have a summary worksheet with vllookup which need to read the
date. Creating new tabs results in ref# errors.
thank you again
max
.- Hide quoted text -

- Show quoted text -

Thx duane, I try later on this week or early next week and keep you
posted!

max

Hi Duane,

With a friend of mine I succedded make it work
thanks a lot!
For future reference, I also got this (comments in french but t is
worth reading it): http://groups.google.fr/group/microsoft.public.fr.access/browse_thread/thread/de2b445276347a42?hl=fr