none
Export image from excel to SQL via vba RRS feed

  • Question

  • Hi,

    I am looking for code in Excel VBA for import image from excel to SQL.

    So I need to send image (jpg) from excel sheet to the  SQL database. SQL server 2008R2.

    Thanks


    VALL2

    Friday, September 19, 2014 6:23 AM

Answers

  • Hi VALL2,

    As far as I know, we can insert a picture which on the disk into SQL server. So we can export the shape then we can use ADODB to execute the SQL command.

    However, there is no method to export the shape. As a workaround, we can copy it to an chart, then export the chart as a picture. Here is the code for your reference:

    Sub ExportPic()
    
    Sheet1.Shapes(1).CopyPicture
    
    Set objChart1 = ActiveSheet.ChartObjects.Add(200, 200, 400, 400)
    objChart1.Activate
    ActiveChart.Paste
     
    ActiveChart.Export Filename:="pathname\image.jpg", FilterName:="JPEG"
    ActiveChart.Parent.Delete
    End Sub

    Here are some useful links for your reference:
    How Do I Use the Connection Object in ADO?

    Using ADO with SQL Server Native Client

    query to insert images into table

    If you have any questions about Transact-SQL, I suggest that you get more effective response from

    Transact-SQL.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 22, 2014 2:50 AM
    Moderator

All replies

  • To save to SQL you need to create a binary field e.g. VARBINARY(MAX).

    In VBA you can check Sheet.Shapes with Type msoPicture and take it from there.

    Friday, September 19, 2014 6:34 AM
  • Thanks, I still to try to do thet, but with out a success.

    Could you send me a few lines of code?


    VALL2

    Friday, September 19, 2014 9:04 AM
  • Hi VALL2,

    As far as I know, we can insert a picture which on the disk into SQL server. So we can export the shape then we can use ADODB to execute the SQL command.

    However, there is no method to export the shape. As a workaround, we can copy it to an chart, then export the chart as a picture. Here is the code for your reference:

    Sub ExportPic()
    
    Sheet1.Shapes(1).CopyPicture
    
    Set objChart1 = ActiveSheet.ChartObjects.Add(200, 200, 400, 400)
    objChart1.Activate
    ActiveChart.Paste
     
    ActiveChart.Export Filename:="pathname\image.jpg", FilterName:="JPEG"
    ActiveChart.Parent.Delete
    End Sub

    Here are some useful links for your reference:
    How Do I Use the Connection Object in ADO?

    Using ADO with SQL Server Native Client

    query to insert images into table

    If you have any questions about Transact-SQL, I suggest that you get more effective response from

    Transact-SQL.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 22, 2014 2:50 AM
    Moderator