Solved

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

Posted on 2015-01-17
12
181 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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