I need to save a cell range as an image

[Normondo]

Hi,

I am writing a program that the user needs access to information without having to navigate for it.

Simply, I need to save a small range B1:C14 on sheet "User" to a local drive. Use whichever format is best. Text only, no image.

I have gotten close, but the image is blank. When I step through it, it works fine. I am unable now to find on my own.

My current skill level is an "experienced noob"

Thanks for your help peoples!

I am running Excel 2016 on Windows 10 home

[MarvinP]

Hi Normondo and welcome to the forum,

Because when you "step through it, it works fine", I'd suggest it is a timing problem. After you save the text or picture to your local drive, put a "DoEvents" statement after that line in your VBA. Put another one after you try to retrieve the stuff from your local drive. The idea is that "DoEvents" will wait until the OS is done before trying to run the next line of your VBA code.

https://support.microsoft.com/en-us/kb/118468

[Normondo]

Thanks MarvinP,

I tried this fix, and no joy.

Here is what I have currently, with some "doevents" added.

Set source = Range("B1:C14")
source.CopyPicture xlScreen, xlPicture
Nom = Range("C1")
Workbooks.Add
DoEvents
With ActiveSheet.ChartObjects.Add(0, 0, source.Width, source.Height).Chart
.ChartArea.Border.LineStyle = 0
DoEvents
.Paste
DoEvents
.Export "C:\Users\Norm\Documents\" & Nom & ".png"
DoEvents
End With
DoEvents
ActiveWorkbook.Close False

Norm

[Normondo]

added a couple more, and it seems to work now. still testing, but looking up!

Thanks MarvinP

[MarvinP]

Hi,

Great to hear the DoEvents solved the problem. Now, which one (or two) are really needed to save that picture and pull it back in your VBA code?

[Normondo]

The randomness of this is driving me nuts. I can't figure out why it will work only sometimes and not others.

If I weren't bald, I would be pulling my hair out!

I am at home now, and on windows 10 home, same as work, and it seemed to work more there.

How can code be random, if random is not programmed in?

Stressed

[MarvinP]

Hey Norm,

Sometimes faster machines will work and slower won't. When you write to the local drive it takes time. The VBA code just keeps firing away and won't wait for other things unless you tell it to stop and wait. My guess is that this is what is happening.

Although, are you using the same versions of Excel on both machines? That might cause some differences. Do you have the same OS (XP vs Windows 10) on both machines? Is one machine a lot faster than the other? Is one on a network and the other not? Do you save to OneDrive on one and not the other? There are a lot of questions to deal with. ALSO are you using Beta Windows 10 on one and not the other? The latest Windows 10 insider version corrected a problem I was having...

[Normondo]

Here is some bizarre code...

I have inserted several msgbox to watch what is happening. I still never do see the copied image!

What is a guy to do?

Set Gina = Range("B1:C14")
DoEvents
Gina.CopyPicture xlScreen, xlPicture
DoEvents
Nom = Range("C1")
DoEvents
Workbooks.Add
DoEvents
With ActiveSheet.ChartObjects.Add(0, 0, Gina.Width, Gina.Height).Chart
DoEvents
i = msgbox("Hi there", vbOKOnly + vbCritical)
.ChartArea.Border.LineStyle = 0
DoEvents
i = msgbox("Hi there", vbOKOnly + vbCritical)
.Paste
DoEvents
i = msgbox("should see it now", vbOKOnly + vbCritical)
.Export "C:\Users\Norm Bourgeois\Documents\" & Nom & ".png"
DoEvents
i = msgbox("Hi there", vbOKOnly + vbCritical)
End With
DoEvents
i = msgbox("just before workbook close", vbOKOnly + vbCritical)
ActiveWorkbook.Close False
DoEvents

[MarvinP]

What happened to the "it seems to work now" from above?

Instead of message boxes, set breakpoints in your code and step through it.

http://analystcave.com/how-to-debug-vba/

[Normondo]

After I put in the doevents, the code worked a couple times. I then moved to another part of the code and continued to work. When I got to trying the two macros in order of function, it got random. Now, it will only work in step mode. However after doing the msgbox and step mode, I see a difference as soon as the chart is created. In the step mode, the canvas is plain white and in the msgbox mode, it has a graphic inside of it. Of course I have no idea why this would be, and with the msgbox, and the long pauses, I don't know what to say.

I have the copy/past window open to the left, and as soon as the copy line is executed, the data is there to be pasted, and after the macro is complete, I can indeed paste this image.

[Normondo]

After many hours, searching and trying, I have figured out this problem.

Not cleaned up yet, but this works.

Dim gina As Range
'
Set gina = Range("B1:C14")
gina.CopyPicture xlScreen, xlPicture
nom = Range("C1")
Workbooks.Add
With ActiveSheet.ChartObjects.Add(1, 1, gina.Width, gina.Height).Chart
.ChartArea.Border.LineStyle = 0
.Paste
'.Export "C:\Users\Norm\Desktop\Truck Rental Data\" & nom & ".png"
End With

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Paste

ActiveChart.Export "C:\users\norm\desktop\truck rental data\" & nom & ".png"


ActiveWorkbook.Close False



Thank you MarvinP for your assistance.