[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2015-01-17
12
Medium Priority
?
189 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Industry Leaders: 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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar 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.

865 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