Embedding a spreadsheet object into a report and linking it to a field in the report

Posted on 2015-01-17
Last Modified: 2015-02-09
I have created the attach calendar that I used to use in a an excel Project report.

I am migrating the report into access and want to embed the calendar in the report, linking it to the report date

It is easy to embed the excel object into the report but I can't figure out how to link it to the report date and have it update for the report automatically each time I print it

Thanks for any help
Question by:rogerdjr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40555848
could not open your file...

Author Comment

ID: 40555858
Tried to upload again - maybe you can open this one
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40556473
Still cannot open you database file...

Perhaps you can explain to me in simple terms what you are trying to do:
embed the calendar in the report, linking it to the report date
link it to the report date and have it update for the report automatically each time I print it

Author Comment

ID: 40556748
I have attached a pdf of what a finished report looks like.

What I had done in excel was create a calendar that keys off the report date - shows the current month in the center, and the calendar for last month and one for next month. The spread sheet colors all of the days prior to the report date in pink, the week starting with the report date in yellow and two weeks beyond this week in green. I also had a version with a list of holidays & work days that provided added color coding in the calendar. See attached excel file.

Creating the excel spreadsheet and calendar is not hard for me - I understand how to make it work.

Now that the report is being generated in access, I want to figure out how to embed the calendar in the report and set the "key" date in the spreadsheet to update to the report date. Attached is an excerpt from my working database with the report table. Note that both the table and the report are a "work in progress"

Thanks for your help
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40560773
What I had done in excel was create a calendar that keys off the report date
...The sample you posted uses the Now() function which returns the current date...
Is there a difference between the Key date and the current date?

So I am still unclear how you are determining the "key" date...

Author Comment

ID: 40561011

The spreadsheet uses now as the date, I will set the report date as an access field (may print the agenda 2 or 3 days before meeting).

I would like the calendar to work of the report date field instead of now()

as stated above this was an excel process that Imam "migrating" to access in excel I simply used a cell reference to the report date cell instead of now()

Thanks for your help
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40561224
So this cell reference will be the key date for the access report as well?

Author Comment

ID: 40561278
Not exactly - I set the report date in access and it drives the excel cell reference, which in turn creates the calendar
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40563349
where/how are you setting it in access

Accepted Solution

rogerdjr earned 0 total points
ID: 40564551
I enter the report date value in the table that has other content for the report and then use a button to print and distribute the agenda - using either the button or the on-print command, I would like the spreadsheet to update.

What I am thinking I will have to do is:
1) in the Report activation button or in the Report onprint command the process would capture the date from the report data table.
2) Open the spreadsheet (filename and path will always be the same or could be a field in the report data)
3) Copy the date to a cell in the spreadsheet
4) Recalculate the spreadsheet
5) save and close the spreadsheet with the same name.


Author Closing Comment

ID: 40597949
Went online and found code to open the spreadsheet, insert data and update the spreadsheet

Private Sub OpenExcelAndInsertAvaluebtn_Click()
    Dim objExcelApp As Object
    Dim wb As Object
    Dim ws As Object
     Set objExcelApp = CreateObject("Excel.Application")

     Set wb = objExcelApp.Workbooks.Open("J:\Projects\_CM-OwnerRep\ProjMngmtDataBase\DatabaseReferences\Reference Calendar - Copy.xlsx")
     Set ws = wb.Sheets(1)
      ws.Cells(11, 8).Value = "Hello"
      ws.Cells(11, 9).Value = "World"
      ws.Cells(11, 2).Value = Now() + 30
      'Close the workbook
      Set wb = Nothing
      Set objExcelApp = Nothing
End Sub

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question