Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to capture an image of a range in excel using vb.net or maybe a  excel macro

Posted on 2008-06-20
19
Medium Priority
?
874 Views
Last Modified: 2013-11-26
I need to capture an image of an excel range using VB.net, and store this in the database.
This raange would have tables and charts, and i want the whole thing to be exported out. I could use a macro in excel, if there is no solution in vb.net, but will prefer vb.net as a solution.

Found a few pointers but nothing concrete:
http://www.mrexcel.com/forum/showthread.php?t=233108&highlight=save+gif
http://www.mrexcel.com/forum/showthread.php?t=291235
http://www.ozgrid.com/forum/showthread.php?t=61921
http://www.eggheadcafe.com/software/aspnet/31843046/best-format-to-export-exc.aspx
http://www.mvps.org/dmcritchie/excel/xl2gif.htm

Please help me with the solution.
0
Comment
Question by:vjammy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
19 Comments
 
LVL 14

Assisted Solution

by:rachitkohli
rachitkohli earned 750 total points
ID: 21829370
'You need to first copy the range, This will copy the picture in clipboard. For example:
WorksheetObject.Range("A1:D10").CopyPicture, 1,2

'Then by following function, it will save as an image.
    Private Sub RangeToImage(ByVal strImgPath As String)
        Dim bmp As Bitmap
        bmp = New Bitmap(Clipboard.GetImage)
        bmp.Save(strImgPath, Drawing.Imaging.ImageFormat.Jpeg)
    End Sub
0
 
LVL 14

Assisted Solution

by:rachitkohli
rachitkohli earned 750 total points
ID: 21829388
Following is the code for saving an image file to the database,
i have the table structure as
    'New Table Information =================================================
    'Table Name : FileInfo
    'Columns : FileName varchar(50), FileData varbinary(MAX)
    '===================================================================
Modify it as per your needs.


 Private Sub SaveFileToDB(ByVal strFilePath As String)
        'SUB to Save the entire File to the Database..
        Me.Cursor = Cursors.WaitCursor
        Dim fs As FileStream
        Dim info As FileInfo
        Dim byt As BinaryReader
        Dim filByte() As Byte
 
        Dim cmd As SqlCommand
        Dim sqlStr As String
 
        Try
            '==== Preparing the File to SAve
            fs = New FileStream(strFilePath, FileMode.Open, FileAccess.Read)
            info = New FileInfo(strFilePath)
 
            ReDim filByte(info.Length)
 
            byt = New BinaryReader(fs)
            filByte = byt.ReadBytes(info.Length)
 
            fs.Close()
 
            '====================================
 
 
            '== Saving to DB now
            sqlStr = "Insert Into FileInfo(FileName, FileData) Values(@FileName, @FileData)"
            cmd = New SqlCommand(sqlStr, GetCon)
            cmd.Parameters.AddWithValue("@FileName", info.Name)
            cmd.Parameters.AddWithValue("@FileData", filByte)
            If cmd.ExecuteNonQuery > 0 Then
                MsgBox("Updated Successfully..!!", MsgBoxStyle.Information)
            Else
                MsgBox("Cannot save..!!", MsgBoxStyle.Critical)
            End If
 
 
        Catch ex As Exception
            MsgBox("Error encountered while saving the File in the Database..!!", MsgBoxStyle.Critical)
        Finally
            fs = Nothing
            info = Nothing
            byt = Nothing
            filByte = Nothing
            cmd = Nothing
            Me.Cursor = Cursors.Default
        End Try
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:vjammy
ID: 21829459
Hi rachit, thanks for your answer.

I am more interested in caputuring the image from an excel range, rather than saving the image to the database.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Author Comment

by:vjammy
ID: 21829471
rachit, your first solution - 'You need to first copy the range, This will copy the picture in clipboard. For example:
WorksheetObject.Range("A1:D10").CopyPicture, 1,2

'Then by following function, it will save as an image.
    Private Sub RangeToImage(ByVal strImgPath As String)
        Dim bmp As Bitmap
        bmp = New Bitmap(Clipboard.GetImage)
        bmp.Save(strImgPath, Drawing.Imaging.ImageFormat.Jpeg)
    End Sub

Is this a vb macro, or a vb.net solution?
0
 
LVL 14

Expert Comment

by:rachitkohli
ID: 21829568
vb.net solution. If you dont want to save a picture, you can directly use the bitmap/image object i.e. Clipboard.GetImage
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21840373
The code in http://www.mvps.org/dmcritchie/excel/xl2gif.htm is exactly what you want. Simply run the GIF_Snapshot() routine, select the range, choose the location to save the GIF, and you're done.

Wayne
0
 
LVL 1

Author Comment

by:vjammy
ID: 21844249
WorksheetObject.Range("A1:D10").CopyPicture, 1,2

'Then by following function, it will save as an image.
    Private Sub RangeToImage(ByVal strImgPath As String)
        Dim bmp As Bitmap
        bmp = New Bitmap(Clipboard.GetImage)
        bmp.Save(strImgPath, Drawing.Imaging.ImageFormat.Jpeg)
    End Sub

I tried doing this, but this fails at bmp = New Bitmap(Clipboard.GetImage) , is there any reason why? Can VB.net access memory directly?
0
 
LVL 14

Expert Comment

by:rachitkohli
ID: 21844496
If the image is copied successfully to the clipboard, then i dont see any problem in Clipboard.GetImage
0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 750 total points
ID: 21844568
Try this....
    ''' <summary>Exports an excel range to the specified image file</summary>
    ''' <param name="wbPath">The Path to the Excel Workbook to open</param>
    ''' <param name="wsName">The name of the Worksheet the selected range is contained within</param>
    ''' <param name="rngAddress">The Range Address (eg, A1:C10)</param>
    ''' <param name="ExportFilePath">The path to save the exported image file</param>
    ''' <param name="format">The required image format</param>
    Private Sub ExcelRangeToImage(ByVal wbPath As String, _
                                  ByVal wsName As String, _
                                  ByVal rngAddress As String, _
                                  ByVal ExportFilePath As String, _
                                  ByVal format As Drawing.Imaging.ImageFormat)
        Dim xlApp As Object = CreateObject("Excel.Application")
        Dim xlWB As Object = xlApp.Workbooks.Open(wbPath)
        Dim xlWS As Object = xlWB.Worksheets(wsName)
        Try
            xlWS.Range(rngAddress).CopyPicture(1, 2)
            Dim img As Image = Clipboard.GetImage
            img.Save(ExportFilePath, format)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Finally
            xlWS = Nothing
            xlWB.Close(False)
            xlWB = Nothing
            xlApp.quit()
            xlApp = Nothing
        End Try
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:vjammy
ID: 21844585
I get the following error:

"Object reference not set to an instance of an object."

on the line:
img.Save(ExportFilePath, format)

What could go wrong? I am writing this code in a windows forms application. Any help is appreciated.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21844605
It worked fine for me when I use it like this....

    ExcelRangeToImage("C:\Book1.xls", "Sheet1", "A1:C10", "C:\ExcelRange.jpg", Drawing.Imaging.ImageFormat.Jpeg)

Wayne
0
 
LVL 1

Author Comment

by:vjammy
ID: 21844639
This still does not work for me.
i am using VISTA, could that cause the issue.
What OS are you using?
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21844654
That could be a problem. I'm using XP.

Did you try my suggestion in http:#a21840373 ??

Wayne
0
 
LVL 1

Author Comment

by:vjammy
ID: 21844665
I've looked at that earlier, and that did not work for me as well.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21844677
>>and that did not work for me as well

Why? It's worked perfect for me first time. If you want to avoid selecting the ranges or the save path, hard code the values.

Wayne
0
 
LVL 1

Author Comment

by:vjammy
ID: 21844711
Maybe it is the vistathing as well, but i wanted a VB.net solution, and did not want a macro to do the job.

0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21844723
"I could use a macro in excel" says otherwise.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21844734
BTW - I doubt the problem you have with the VBA code (which you haven't even mentioned what it is) has anything to do with Vista, as all commands in the VBA code are native to Excel VBA. It simply copies the picture of the range to a chart object, which provides a handy Export method to export the chart object to an image file.

Wayne
0
 
LVL 1

Author Comment

by:vjammy
ID: 21915181
I tried the code in xp, and it worked. Vista does not allow you access to the Clipboard.Getimage.
Rachit and Wayne, Thanks for your help. I will divide the points among both of you.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

610 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