Wondering if there is Code for exporting images to Excel file with multiple records

[Jo..]

Hello Experts!

I am totally stuck when it comes to inserting images with Access VBA/Macro. Is there any way to do so?

Below is my code so far. It is to export query data to Excel Template. I looking for a way to insert the ProductPicture from every record to Excel. The product pictures are stored in the Access database as Path at the moment.

Option Explicit

'Enter Location of your Template Here
Const ExcelTemplate = "\\nas\Database\Product Development\MasterList\QuoteSheetTemplate.xlsx"
'Enter the Folder Directory to save results to
Const SaveResutsFldr = "\\nas\Database\Product Development\MasterList"


Sub CreateWorkbook()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "SELECT * FROM QuoteItems WHERE Lognumber='" & Forms![QuoteLog]![LogNumber] & "';"


    Set dbs = CurrentDb
    With dbs
        .QueryDefs.Delete ("myQuery")
        Set qdf = dbs.CreateQueryDef("myQuery", strSQL)
        .Close
    End With
    
    Application.RefreshDatabaseWindow


    Dim SaveAsStr As String
    Dim ExcelApp, WB As Object
    Dim qry As QueryDef
    Dim i As Integer
    Dim c As Integer


    c = DCount("*", "myQuery") + 9


    'Create Reference to Run Excel
    Set qry = CurrentDb.QueryDefs("myQuery")
    Set ExcelApp = CreateObject("Excel.Application")


    'Create Reference to your Table
    Dim T As Recordset
    Set T = qry.OpenRecordset


    'Loop through all Record on Table
    If Not (T.BOF And T.EOF) Then
    T.MoveFirst
    End If
    Do While Not T.EOF
    'Open Your Excel Template
    Set WB = ExcelApp.Workbooks.Open(ExcelTemplate)


    For i = 10 To c
    'Enter your data from your table here to the required cells
    WB.Worksheets("sheet1").Cells(3, 41).Value = Forms![QuoteLog]![LogNumber]
    WB.Worksheets("sheet1").Cells(3, 2).Value = Forms![QuoteLog]![CustomerName]
    WB.Worksheets("sheet1").Cells(4, 41).Value = Forms![QuoteLog]![SentDate]
    WB.Worksheets("sheet1").Range("B" & i) = T("LogNumber")
    WB.Worksheets("sheet1").Range("C" & i) = T("ProductSpec")


    Dim strExcel As String
    Dim n As Integer
    strExcel = "=IF(A" & i & " = """"," & """EMPTY""" & "," & """FILLED""" & ") "
    WB.Worksheets("sheet1").Range("D" & i).Formula = strExcel




    'Repeat this line for each piece of data you need entered
    'Changing the Sheet name, cell range, a field name as per your requirements
    'WB.Wor...
    'WB.Wor...


    T.MoveNext
    Next i
    i = i + 1
    Loop
    'Save and Close the Workbook
    SaveAsStr = SaveResutsFldr & "\" & [Forms]![QuoteLog].LogNumber & "_" & [Forms]![QuoteLog].CustomerName & "_" & Format(Now(), "yymmdd") & ".xlsx"
    WB.SaveAs SaveAsStr
    WB.Close
    Set WB = Nothing


    'Move to the Next Record




    'Close down the Excel Application
    ExcelApp.Quit
    Set ExcelApp = Nothing
    
    Shell "EXCEL.EXE """ & SaveAsStr & "", vbNormalFocus
End Sub

And all I could find was a code for the excel, which I have no idea how to put into Access.

shapes.AddPicture(Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height)

Any help would be much appreciated!!

Thank you in advance,
Jo

[nick404]

Are you exporting a report that contains images? Or a form?

I do not know much about what you are trying to do, but as no one has responded I will try and help you. Here is a link I found that sounds similar to you... perhaps it can be of assistance. http://www.nullskull.com/q/10220386/...om-access.aspx

[ItsMe]

I have a couple of things I need to take care of at the moment. But ...

What you can do is Dim another object for shapes and instantiate it using the Worksheet object as its parent. I can't remember if I have created pictures in Excel or extracted pictures that already existed. I have an example floating around somewhere that uses a chart object, though. The key is to use Early Binding by referencing the Microsoft Excel Object library and using intellisense.

[Jo..]

Are you exporting a report that contains images? Or a form?

I do not know much about what you are trying to do, but as no one has responded I will try and help you. Here is a link I found that sounds similar to you... perhaps it can be of assistance. http://www.nullskull.com/q/10220386/...om-access.aspx

Hi Nick404,
Thank you for your reply. I had looked at this website before. But it does not really fit my need. I need a different picture for every single one of the records. So I should just keep searching google for answers.

Anyway, your suggestion was great! Thank you for trying!

[Jo..]

I have a couple of things I need to take care of at the moment. But ...

What you can do is Dim another object for shapes and instantiate it using the Worksheet object as its parent. I can't remember if I have created pictures in Excel or extracted pictures that already existed. I have an example floating around somewhere that uses a chart object, though. The key is to use Early Binding by referencing the Microsoft Excel Object library and using intellisense.

Hi there, I have tried setting the workbook as excel.workbook, and also tried to use excel.application, but I could not get the code running. Also tried to dim the Piclocation as string and use T("productpicture"). Doesn't seem to be working also. The problem is that T("productpicture") is access reference whereas the code I found and tried to use is a excel object reference.

I am very green on exporting excel. Your help is needed. 😂

Thank you for replying and thank you for the suggestions

[June7]

I've never used VBA to insert images into Excel. I wonder how inserting a different image with each record can be managed on the Excel sheet? How would the image remain associated with the record? Don't images just 'float' freely on Excel sheet?

So what is purpose of exporting data to Excel?

[Jo..]

I've never used VBA to insert images into Excel. I wonder how inserting a different image with each record can be managed on the Excel sheet? How would the image remain associated with the record? Don't images just 'float' freely on Excel sheet?

So what is purpose of exporting data to Excel?

Could I maybe insert many images according to in the record order and at the same time limit the spacing of between each picture and the height of the rows. Yes, the images will still be floating freely. But I m wondering if anyway of inserting images according to the large number of records is possible.

This is actually a interesting topic to work on.

I am trying to store the product specification with a product picture as a quote sheet, and also with the ability to use formulas on the excel for quick calculations. It is weird, but this is what I need!

Thank you for replying as always. Your suggestions have always been very helpful

[June7]

I suppose it is possible but sounds very complicated. Code would have to insert each image to specific coordinates on the sheet. This means code would increment the coordinate values and hold in variables.

What 'quick calculations'? Why not have a table and form for these data and calcs?

[Jo..]

I suppose it is possible but sounds very complicated. Code would have to insert each image to specific coordinates on the sheet. This means code would increment the coordinate values and hold in variables.

What 'quick calculations'? Why not have a table and form for these data and calcs?

The excel is for sales who are not allowed to access the database, that's why I need to export the quote sheet for them to do calculations. Trying to limit the most stupid human error. I need to make sure to get the right set of formulas and the most updated information on the excel.

Updating and adding records will be done on the forms.

[June7]

Okay, Excel is one way. Attempt code for the image inserts and when you have specific issue, post thread.

For great examples on import/export between Access/Excel, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Another is to build an Access file that sales can use. Is this a split design db? Either copy the database files or export. I have a process that does this. I built another split db (smaller and simpler frontend and limited set of tables in backend). Then a procedure in my main db deletes data from tables in other backend and exports data into tables so the dataset is cumulative then I email the new backend to user who already has the frontend. They replace old backend with the new emailed file and db continues to work for them.
https://www.accessforums.net/macros/...010-49934.html
https://www.accessforums.net/access/...nto-24454.html

[Jo..]

Okay, Excel is one way. Attempt code for the image inserts and when you have specific issue, post thread.

For great examples on import/export between Access/Excel, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Another is to build an Access file that sales can use. Is this a split design db? Either copy the database files or export. I have a process that does this. I built another split db (smaller and simpler frontend and limited set of tables in backend). Then a procedure in my main db deletes tables in other backend and exports data into re-created tables so the dataset is cumulative then I email the new backend to user who already has the frontend. Replaces old backend with the new emailed file and db continues to work for them.

The biggest problem is that the sales dont even have licensed access software. So, I guess I have to do it the hard way.

Thank you for your replies. I will get back to you when I work it out.

[ItsMe]

Did not read all of the other posts yet. As for looping and linking images and stuff, shapes is the way to go. This code creates an excel file, adds a worksheet, adds a shape, links a picture to the shape, saves the file. So you would have to integrate it to your loop thing. I looked at different approaches but yeah, shapes is where it is at.

You have to make references in order for this code to work. I referenced Microsoft Office XX.0 Object Library and Microsoft Excel XX.0 Object Library. Probably only need to reference Excel Library.

Dim xlApp As New Excel.Application
Dim myBook As Workbook
Dim mySheet As Worksheet
Dim myShape As Shape
 
Set myBook = xlApp.Workbooks.Add
Set mySheet = myBook.Worksheets(1)
mySheet.Name = "ShapeExample"
Set myShape = mySheet.Shapes.AddPicture("C:\Test\Pictures\samplePhoto.jpg", msoTrue, msoFalse, 1, 1, 500, 500)
myBook.SaveAs ("C:\Test\ExcelFiles\TestFile.xlsx")
MsgBox "Complete"
myBook.Close
xlApp.Quit
 
Set myShape = Nothing
Set mySheet = Nothing
Set xlApp = Nothing

[June7]

What about position of the shape/image on the sheet in relation to the row with associated exported data?

They could possibly use Access run-time version - it is a free download.

[ItsMe]

What about position of the shape/image on the sheet in relation to the row with associated exported data?

Sounds like a challenge.

I don't know. I was working on some other stuff and then thought I would try to get the image import thing. It did cross my mind when I was hard coding the x and y pos.

Do you know if each cell has a coordinate? I imagine it must. Like you mentioned earlier, complicated code. If the coordinates for a given cell is available, I am sure it could be accomplished.

Might not be too bad to nest in the shape, though.

[June7]

I don't think cells would have coordinates other than the ROW and COLUMN reference. No idea if that can be used programmatically to position shape/image.