Using CopyPicture for a Picture control does not copy over cell data, just shape data Topic is solved

[stretchymantis]

Hello AutoHotkey world!

I have a problem I can't seem figure out. I've been unable to find a solution after several days. I have an Excel file that is essentially a layout map of a building. The map is comprised of cells, cell borders, and shapes. I have a script which copies the map via UsedRange.CopyPicture. If I then display it via a SplashImage, the map looks fine. However, when displaying as a Picture control, it only shows the shapes, not the cells.

If anyone knows why it does this and/or some workaround, it would really be helpful. My only stipulation is I don't want to have to create and actual file as other co-workers will be using this on work laptops and if it writes a file to the drive it will send out red flags to our IT department.

Thank you much!

Here's my sample code:

Code: Select all

oXl := ComObjCreate("Excel.Application")
clipboard :=
Wrkbk := oxl.Workbooks.Open("C:\Excel_File.xlsx")
oRange := oxl.sheets("Layout").UsedRange.CopyPicture(,2)
if DllCall("OpenClipboard", "ptr", 0) {
	if DllCall("IsClipboardFormatAvailable", "uint", 2) {
		hBitmap := DllCall("GetClipboardData", "uint", 2, "ptr")
	}
	DllCall("CloseClipboard")
}
SplashImage, % "HBITMAP:*" hBitmap ;Show as SplashImage
Gui, Add, Picture, vLayoutMap +Border, % "HBITMAP:*" hBitmap ;Show as picture control
Gui, Show
Return

[BoBo]

is I don't want to have to create and actual file as other co-workers will be using this on work laptops and if it writes a file to the drive it will send out red flags to our IT department

How you actually work at all if writing files to the disc is alerting your IT?

[flyingDman]

@BoBo I had the same thought!

Using GDIP, this seems to work: (viewtopic.php?t=6517)

Code: Select all

oxl 		:= ComObjActive("excel.application")
oRange 		:= oxl.activesheet.Range("A1:Q16").CopyPicture(1,2)
pToken 		:= Gdip_Startup()
pBitmapX 	:= Gdip_CreateBitmapFromClipboard()
hBitmapX 	:= Gdip_CreatehBitmapFromBitmap(pBitmapX)
gui, margin, 0,0
gui, -caption -dpiscale -border -ToolWindow
gui, color, black
Gui, Add, Picture, w500 h-1, % "hBitmap:*" hBitmapX
Gui, Show
Return

esc::
Gdip_DisposeImage(pBitmapX)
Gdip_Shutdown(pToken)
exitapp

[stretchymantis]

is I don't want to have to create and actual file as other co-workers will be using this on work laptops and if it writes a file to the drive it will send out red flags to our IT department

How you actually work at all if writing files to the disc is alerting your IT?

Lol sorry, re-reading what I typed I realize I didn't explain that very well. So, our company has been expanding and thus our IT department has been implementing a lot of new restrictions on company-issued devices (can't change the time on laptops, need access code to install programs, etc.). I'm creating a small utility for everyone that provides quick access to some useful information. But I want to avoid any potential hesitation of our IT dept accepting the utility. So a strong defense of mine has been that it doesn't add/delete/modify any files on user's computers. Also, I don't know what other restrictions they will put in place in the future and want to minimize any chance of it conflicting with the code.

[BoBo]

So you're restricted to a level below local admin rights, what isn't uncommon. But your main obstacle will be that AHK is using an interpreter to run your scripts. While those may be OK for your IT, the interpreter bares the risk to run 'third party' code. So you’ve to ensure beforehand that AHK itself gets approved by your IT, so you won't get in conflict with any IT/company policy. Good luck.

[stretchymantis]

@BoBo I had the same thought!

Using GDIP, this seems to work: (viewtopic.php?t=6517)

Code: Select all

oxl 		:= ComObjActive("excel.application")
oRange 		:= oxl.activesheet.Range("A1:Q16").CopyPicture(1,2)
pToken 		:= Gdip_Startup()
pBitmapX 	:= Gdip_CreateBitmapFromClipboard()
hBitmapX 	:= Gdip_CreatehBitmapFromBitmap(pBitmapX)
gui, margin, 0,0
gui, -caption -dpiscale -border -ToolWindow
gui, color, black
Gui, Add, Picture, w500 h-1, % "hBitmap:*" hBitmapX
Gui, Show
Return

esc::
Gdip_DisposeImage(pBitmapX)
Gdip_Shutdown(pToken)
exitapp

Yep, that does it! I haven't ever used Gdip, but just downloaded it and your code worked flawlessly. Thank you Thank you. It actually seems quite powerful and useful, I'm going to read up on it a bit more.

You all are so awesome on here, someday I'll learn enough about Autohotkey and actually be able to contribute!

Thanks again!
-Jason

[flyingDman]

You're welcome!