Viewing a different excel workbook in a userform

[dyayroos]

Can this be done? I have googled this question, and most of the solutions show I need to process the spreadsheet data into a listbox. But what I want is a simple viewer within the userform to show a different excel file. Are there 3rd party controls that I can use, or any other way besides the listbox thingy?

 

[NdNoviceHlp]

Hi dyayroos and Welcome this Board! I recently resolved a similar request for viewing a range in the same wb at the following link. I'm guessing it wouldn't take much to adjust the code to view a range in a different wb. HTH. Dave
Snapshot as a popup | MrExcel Message Board

 

[NdNoviceHlp]

Here's some improved code that will do what U want. Add a large userform and put a large frame control on it. You must change the file path to the full file path of the wb you want to view. HTH. Dave
Module code...

Public TempA As Double
Public TempB As Double
Public Sub createJpg(SheetName As String, xRgAddrss As Range)
'creates temp JPG file of range (xRgAddrss) by creating temp chart
'uses current wb sheet (sheetname) to locate temp chart
'kills temp JPG file after UF display
Dim xRgPic As Range
Worksheets(SheetName).Activate
Set xRgPic = xRgAddrss
xRgPic.CopyPicture
TempA = xRgPic.Width
TempB = xRgPic.Height
With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "" & "TempChart.jpg", "JPG"
End With
Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
End Sub

Sub test()
Dim FilDir As Object
On Error GoTo ErFix
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'*** change file path to suit
Set FilDir = Workbooks.Open(ThisWorkbook.Path & "\" & "test.xlsm")
'createJpg(this wb sheet, range address to display)
Call createJpg("Sheet1", FilDir.Worksheets("Sheet2").Range("A1:o22"))
Workbooks(FilDir.Name).Close SaveChanges:=False
ErFix:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set FilDir = Nothing
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
Exit Sub
End If
UserForm1.Show
End Sub

Userform code...

Private Sub UserForm_Initialize()
With UserForm1.Frame1
.BorderStyle = fmBorderStyleNone
.Caption = vbNullString
.ScrollBars = fmScrollBarsBoth
.ScrollHeight = TempB
.ScrollWidth = TempA
.Picture = LoadPicture(Environ$("temp") & "" & "TempChart.jpg")
.PictureSizeMode = fmPictureSizeModeClip
End With
Kill (Environ$("temp") & "" & "TempChart.jpg")
End Sub

To operate run the test sub

 

[dyayroos]

So

Hi dyayroos and Welcome this Board! I recently resolved a similar request for viewing a range in the same wb at the following link. I'm guessing it wouldn't take much to adjust the code to view a range in a different wb. HTH. Dave
Snapshot as a popup | MrExcel Message Board

Yup I've encountered this option too. It's like converting the sheet into an image and then showing it in a frame, right?

I have a few questions with this solution:
1) Can I make the output picture higher-resolution? I tried a variation of this solution from another forum and it gave a blurred image, the data was almost unreadable.
2) If my out-sourced workbook has a lot of columns, can I place a zoom-in zoom out function in the frame so I can view the image better?

Sorry for the noob questions, I am really new to vba (one week in only. hahaha)

 

[NdNoviceHlp]

Have you trialed the code? The image is as clear as the original range. The frame has scroll bars to allow you to navigate the whole range. Dave

 

[Jaafar Tribak]

If you copy the target range as a metafile format (wmf), the resulting image will be much crisper.
Do a search for OleCreatePictureIndirect and CopyPicture xlScreen, xlPicture

 

[dyayroos]

If you copy the target range as a metafile format (wmf), the resulting image will be much crisper.
Do a search for OleCreatePictureIndirect and CopyPicture xlScreen, xlPicture

Thanks. I will be trying this out.

 

[NdNoviceHlp]

Hi Jaafar. I hate to disagree, but have you trialed the code? I'm not sure how you get clearer than clear? Dave
ps. I am quite familiar with your API solution and use it often to place pictures of ranges in frames on userforms. This method is a lot simpler and I can't really tell the difference. Also, omitting the xlScreen, xlPicture part of the CopyPicture doesn't seem to make a difference. In your API code, across versions, I have found that's it's required to actually just copy the range and let XL decide what format to use...

Objtargetrange.Copy 'Picture Appearance:=xlScreen, Format:=CF_BITMAP

 

[Jaafar Tribak]

Hi Jaafar. I hate to disagree, but have you trialed the code? I'm not sure how you get clearer than clear? Dave
ps. I am quite familiar with your API solution and use it often to place pictures of ranges in frames on userforms. This method is a lot simpler and I can't really tell the difference. Also, omitting the xlScreen, xlPicture part of the CopyPicture doesn't seem to make a difference. In your API code, across versions, I have found that's it's required to actually just copy the range and let XL decide what format to use...

Objtargetrange.Copy 'Picture Appearance:=xlScreen, Format:=CF_BITMAP

Raster images lose quality when scaled to large sizes as opposed to vector (wmf) images.

You probably don't see any difference in the resulting image when you copy it as a bitmap or export it as a jpg because the copied range is not scaled up enough.

Try copying a sheet range one as a bitmap and the other one as a wmf .
CopyPicture xlBitmap vs CopyPicture xlPicture
Once copied, paste each image back onto the sheet (as a shape) and start increasing their respective sizes by dragging them .
You should see that the wmf image retains its quality better than the bitmap image after being scaled up.

 

[NdNoviceHlp]

Thanks for the info Jaafar and the illustration. In this usage, however, it doesn't seem to matter as the physical range size is maintained and navigated by the frame's scrollbars. Apparently, it has worked for dyayroos, as he/she has marked the code as a solution. Have a nice day and be safe. Dave