Camera tool to view a (Changable) range of cells

D Marshall · Jan 21, 2020

I have a workbook set up as a team calendar, the first sheet "Programme" contains all the calendar data with a second sheet "dashboard" containing some calculations.

Within "Programme" columns D to NI represent every day of the year and rows 3-32 represent staff, columns A-C holds a list of staff names and various qualifications whilst row one is the days of the week and row 2 is the date (dd/mm/yyyy) starting at 28/12/2019 in 'D'.

Twice every week I want to take a snapshot of the upcoming week, is there a way to utilise the camera tool to automatically display an image of the upcoming week that i can then Ctrl+C into an e-mail?

The 'image' would always cover the same area of cells, i.e. seven columns wide by 32 rows. By way of example next weeks range would be in AA1:AG32 and the following weeks would be AH1:AN32 etc.

I'd like an image of the relevant range in "Programme" shown in "dashboard" of the seven columns corresponding to Monday to Sunday of the following week (derived from today's date). The row numbers for the range would not change, only the column numbers which would be derived from an existing formulas as shown below;

.

I've tried inserting an image and then changing the cell reference to indirect or index formulas referencing cells B7:D7 but without any joy.

Does anyone have any idea how this could be achieved?

thanks,

Dave

Legacy 456129 · Jan 21, 2020

You could even automate the emailing. Though I use the camera function extensively, a CPU hog, to get live pics in other parts of the workbook of AOI, I'm actually using a routine that puts the image into paint and paint puts a copy on the drive.

The Call you put into the scheduling function that Excel possesses, (WhenNext is a time field in the sheet.)

VBA Code:

110        Application.OnTime WhenNext, "YourCallProcedure", Schedule:=True

which then calls the actual copy/save routine.

VBA Code:

Call OpenPaint(Range(BaseP) & "ActiveData.Png")

Public Function OpenPaint(strFile As String) 'ver 18.06.2017 need to figure out how to catch error if file does not exist yet

10  Dim paintID As String, WshShell As Object: Set WshShell = CreateObject("WScript.Shell")
20  Dim ObjFso As Object, CheckExists As Boolean
30  Set ObjFso = CreateObject("Scripting.FileSystemObject")
40  CheckExists = ObjFso.FileExists(strFile)
50  Select Case CheckExists
        Case True
60          paintID = Shell("mspaint.exe " & Chr(34) & strFile & Chr(34), vbNormalFocus) 'open app through Shell call, _
    concatenation of app + path in quotes (needed due spaces in path), use only 1 or 9, newly opened, existing opened.
70      Case False
80          paintID = Shell("mspaint.exe ", vbNormalFocus) 'code 1
90      End Select
100 Application.Wait Now + TimeValue("00:00:02") ' VBA does all timing in whole seconds
110 With WshShell 'Call Windows Scripting and pass keystrokes per below, with delays
120     .SendKeys "^(v)" 'paste
130     Application.Wait Now + TimeValue("00:00:01") ' VBA does all timing in whole seconds
140 Select Case CheckExists
        Case True
150         .SendKeys "^(s)" 'save file - needs to cater for language of interface as set by user
160     Case False
170         .SendKeys "%Fav" 'save file as - needs to cater for language of interface as set by user
180         .SendKeys strFile 'paste filename
190         .SendKeys "%S"
200 End Select
210     Application.Wait Second(Now) + 0 'A different way to set a wait-state, DoEvents would do here too.
220     .SendKeys "%Fx" 'open file menu, combined %(F) and eXit into %Fx, no waits needed - 18-9-2016
230 End With 'WshShell

End Function 'EoS OpenPaint

More ways to skin a cat, a bit archaic to simulate keystrokes, but it's been working without a hitch for years.

D Marshall · Jan 21, 2020

Hi, thanks for the reply, I'd rather avoid calling an external programme, there are a few people who will eventually use the sheet and it will also be shared (locked) across a google drive.

Is there a way to set a target range within the excel camera tool controlled by vba or formulas?

Dave

Legacy 456129 · Jan 21, 2020

Yes, that is really not a problem. The snapshot you can associate with a range in the formula bar and name the snapshot at that. By example one I first attached to a regular named range then in the Name manager changed the range reference to a formula that switches the image briefly on and off to stop the CPU time usage until a macro calls for a next refresh.

=PicActDat

This range name contains the formula

=IF(PicsOn=1,ActiveData,0)

Where ActiveData is the true range the camera is looking at. When the sheet value is 1, the image refreshes, if 0 it's a still. The macro writes to a field called PicsOn. Camera is a Volatile, thus when it sees 1 = True it gets to work at every activity in the workbook, when 0 = False, it goes dormant. Latest Excel 365 has a very easy image selection copy called "Save as Picture". Not had the urge yet to see how it can be used in a macro other than knowing that if doing a macro record, you get Sub Macro1().... End Sub with nothing in between i.e. it's still the old coding method. Example I found at Chart export saves a watermark instead of image when picture inserted into Worksheet using VBA

D Marshall · Jan 22, 2020

Hi, thanks for coming back again, noted regarding the trigger to reduce the CPU overhead, at the moment I'm still struggling to get the dynamic range to function within the camera tool, I've created a camera window and linked this to a named range (ActiveData), and updated the Refers to: section via Name Manager to the following;

=CONCATENATE("(",ADDRESS(1,MATCH(TODAY()+(7-WEEKDAY(TODAY(),2)+1),Programme!2:2,0),1,1,"Programme"),")",":","(",ADDRESS(162,MATCH(TODAY()+(7-WEEKDAY(TODAY(),2)+6),Programme!2:2,0),1,1,"Programme"),")")

the formula displays the following when placed in a cell,

(Programme!$AH$1)

Programme!$AM$162)

but i can't get the picture to display the correct range, I get a Reference isn't valid pop-up.

Any ideas what i'm doing wrong?

thanks,

Dave

Legacy 456129 · Jan 22, 2020

Yes, you found the exact problem I learned about... the sequence is important.

1) Take the snapshot and put it somewhere near your Dynamic range. When you click on it, you see it refers to a range, any range of your choice.
2) Name that range with something meaningful in Name Manager
3) Select the picture again and edit the =addressrange and replace by the range name

First hurdle passed, your camera knows A name now.

Go to the Name Manager and edit the name, put the true formula in that reference below, which determines the range.

Second hurdle passed. The Camera Pic is now fed a dynamic range image.

The dirty tricks part is to name that dynamic image range a second time. Put your same formula in the reference box and save.

Name a third single field PicsOn and put a zero in there, either sheet assigned or workbook assigned (I use sheet restricted as I have many in different sheets).

Go back to the first Named Range in Name Manager that's linked to the Camera Pic (the one you see in the formula bar) i.e. as how your picture knows what to look at. Put in that switch formula of mine, making sure it refers to Second Named Range which has the Dyn. Formula.

You should be good to go. Put the data range and Camera pic in the same view. Make a change in the DynRange of the sheet and watch the Camera. Nothing should happen there. Put a 1 in the PicsOn field and the picture refreshes since it's a 'volatile' again. Put a zero back on and it frame-freezes.

Now this is off the top of me head. Steps probably could be made more efficient, but I stick to this routine whenever the need arises.

Let me know if this turns out 'like' worthy. ;o)

D Marshall · Jan 22, 2020

Hi, I've followed the above but still no joy, step 1 is fine, I can create the snapshot and have this update in real time any changes within the data range,

I can complete the operations in step two but i'm left with a static image, changes to the cells within the range do not appear within the snapshot - i still have the snapshot displayed as before the change, but it just doesn't update.

I can't see if the range is changing based on the date but it looks like i'm failing to even keep the snapshot "live"

any suggestions?

thanks,

Dave

Legacy 456129 · Jan 22, 2020

Attach a piece of the file to a reply and I'll have a try.

Meanwhile, not even a refresh when hitting F9 or Crtl+Shft+Alt+F9? You have an Address put together, but maybe it needs an Indirect(Concatenate.......) around the whole string formula, else it's just text.

D Marshall · Jan 22, 2020

Thanks, that's much appreciated, my basic file is attached below, F9 or Crtl+Shft+Alt+F9 don't have any effect,

Just tried it with an INDIRECT but again, no effect.

thanks,

Dave

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

28/12/2019

29/12/2019

30/12/2019

31/12/2019

01/01/2020

02/01/2020

03/01/2020

04/01/2020

05/01/2020

06/01/2020

07/01/2020

08/01/2020

09/01/2020

10/01/2020

11/01/2020

12/01/2020

13/01/2020

14/01/2020

15/01/2020

16/01/2020

17/01/2020

18/01/2020

19/01/2020

20/01/2020

21/01/2020

22/01/2020

23/01/2020

24/01/2020

25/01/2020

26/01/2020

27/01/2020

28/01/2020

29/01/2020

30/01/2020

31/01/2020

01/02/2020

02/02/2020

03/02/2020

04/02/2020

05/02/2020

06/02/2020

07/02/2020

08/02/2020

09/02/2020

10/02/2020

11/02/2020

12/02/2020

13/02/2020

14/02/2020

15/02/2020

A

Job description / Site code

Contract No. / CM / SE

Vehicel

-

Gas box / PID

- / -

Comment

Comments

B

-

- / -

Comment

C

-

- / -

Comment

D

-

- / -

Comment

E

-

- / -

Comment

F

-

- / -

Comment

G

-

- / -

Comment

H

-

- / -

Comment

I

-

- / -

Comment

J

-

- / -

Comment

K

-

- / -

Comment

L

-

- / -

Comment

M

-

- / -

Comment

N

-

- / -

Comment

O

-

- / -

Comment

P

-

- / -

Comment

Q

-

- / -

Comment

R

-

- / -

Comment

S

-

- / -

Comment

T

-

- / -

Comment

U

-

- / -

Comment

V

-

- / -

Comment

W

-

- / -

Comment

X

-

- / -

Comment

Y

-

- / -

Comment

Z

-

- / -

Comment

Legacy 456129 · Jan 22, 2020

Right now I see a big picture with lots of closed eyes - / - but no actual attached Excel file

Camera tool to view a (Changable) range of cells

D Marshall

New Member

Excel Facts

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

Similar threads

Forum statistics

Share this page

Camera tool to view a (Changable) range of cells

D Marshall

New Member

Excel Facts

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

D Marshall

New Member

Legacy 456129

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock