Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 961
  • Last Modified:

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

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
vjammy
Asked:
vjammy
  • 8
  • 7
  • 4
3 Solutions
 
rachitkohliCommented:
'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
 
rachitkohliCommented:
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
 
vjammyAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
vjammyAuthor Commented:
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
 
rachitkohliCommented:
vb.net solution. If you dont want to save a picture, you can directly use the bitmap/image object i.e. Clipboard.GetImage
0
 
Wayne Taylor (webtubbs)Commented:
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
 
vjammyAuthor Commented:
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
 
rachitkohliCommented:
If the image is copied successfully to the clipboard, then i dont see any problem in Clipboard.GetImage
0
 
Wayne Taylor (webtubbs)Commented:
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
 
vjammyAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
vjammyAuthor Commented:
This still does not work for me.
i am using VISTA, could that cause the issue.
What OS are you using?
0
 
Wayne Taylor (webtubbs)Commented:
That could be a problem. I'm using XP.

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

Wayne
0
 
vjammyAuthor Commented:
I've looked at that earlier, and that did not work for me as well.
0
 
Wayne Taylor (webtubbs)Commented:
>>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
 
vjammyAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
"I could use a macro in excel" says otherwise.
0
 
Wayne Taylor (webtubbs)Commented:
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
 
vjammyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now