Solved

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

Posted on 2015-01-17
12
178 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 34
selection of current record jumps to a non-selected record 8 38
append to an ms access field 6 26
How do I refer to a session variable in a query? 4 24
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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now