Solved

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

Posted on 2015-01-17
12
180 Views
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
J--Projects--CM-OwnerRep-ProjMngmtDataBa
0
Comment
Question by:rogerdjr
  • 7
  • 5
12 Comments
 
LVL 74

Expert Comment

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

Author Comment

by:rogerdjr
ID: 40555858
Tried to upload again - maybe you can open this one
J--Projects--CM-OwnerRep-ProjMngmtDataBa
0
 

Author Comment

by:rogerdjr
ID: 40555934
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
0
 

Author Comment

by:rogerdjr
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
J--Projects--CM-OwnerRep-ProjMngmtDataBa
C--Users-Roger-Documents-Database1.accdb
J--Projects--CM-OwnerRep-ProjMngmtDataBa
0
 
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...
0
 

Author Comment

by:rogerdjr
ID: 40561011
Yes

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
0
 
LVL 74

Expert Comment

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

Author Comment

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

Expert Comment

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

Accepted Solution

by:
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.

Thanks
0
 

Author Closing Comment

by:rogerdjr
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
     
      wb.Save
      'Close the workbook
      wb.Close
      Set wb = Nothing
      Set objExcelApp = Nothing
End Sub
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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