Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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