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
806 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

13 Experts available now in Live!

Get 1:1 Help Now