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
809 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
  • 8
  • 7
  • 4
19 Comments
 
LVL 14

Assisted Solution

by:rachitkohli
rachitkohli earned 250 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 250 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
 
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 47

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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 47

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 47

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 47

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 47

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now