1

I am getting completely crazy about this issue, please help me.

I have made a shell script that writes in a text file the path of some images that are stored in a folder. Then I use an excel code to read each path and write it in an excel cell. I have then made a code that should take that path and use it to insert the picture. I have tried with Pictures.insert(path) and shapes.addpictures "path", but I have the same issue every time, the picture can't be loaded. What's weird, is that, if I manually insert the picture before and then delete it, the code will perfectly load the picture. But if it's the first time then no. The paths that I'm using look like that: "/Users/theodorebedos/Documents/code_tilly/new_pict_tilly/IMG_9040.jpg"

I'm using a mac, maybe that matters?

Private Sub Convert_Img()

Dim myPict As Picture
Dim PictureLoc As String
Dim EndPictRow, i As Integer
Dim StartPath As String


If Worksheets("Main").Cells(3, 1).Value <> "" Then
    EndPictRow = Worksheets("Main").Range("A2").End(xlDown).Row

    For i = 3 To EndPictRow
        PictureLoc = Worksheets("Main").Cells(i, 1).Value
        Worksheets("Main").Cells(i, 1).ClearContents
        Worksheets("Main").Cells(i, 1).ColumnWidth = 30
        Worksheets("Main").Cells(i, 1).RowHeight = 150
           ActiveSheet.Shapes.AddPicture PictureLoc, False, True, Worksheets("Main").Cells(i, 1).Left, Worksheets("Main").Cells(i, 1).Top, Worksheets("Main").Cells(i, 1).Width, Worksheets("Main").Cells(i, 1).Height

    Next i
End If

End Sub

Edit: When I use "Pictures.insert" or "shapes.addpicture path, true, true " I have no error message in VBA but I have in excel instead of my picture, a blank image with inside an error message like this: image

If I use "shapes.addpicture path, FALSE, true" then I have an error message like this but no image at all is loaded: image 2

And then an error 1004 like that: image3

And if I do the process to have image 1, then I save the document, reopen it, I'll have this directly: image 4

Thanks for you help. It will be much appreciated.

Théodore Bedos
  • 11
  • 4
  • Is the `ActiveSheet` different from `Worksheets("Main")`? The use of `ActiveSheet` is never advisable unless you know exactly which one it is. (My question proves that you don't lol:). BTW `Dim EndPictRow` specifies a variant. If you want it to be an Integer you must specify (but it should be Long). From your description I suspect a issue with screen updating. Try to save your file after running the script and see if the picture shows when you re-open. – Variatus Mar 22 '20 at 01:24
  • Are you getting a run-time error or does code inserts an empty image in the worksheet? – DecimalTurn Mar 22 '20 at 03:27
  • Hi, thanks for your answers. @Variatus, There is only one sheet in my project so I know exactly which one is the activesheet (but everything is good to take, I tried to change it, but no change). I have saved my filed and then reopen it as you said and then I get the image4 in my description.(I have edited my question to be more precise). – Théodore Bedos Mar 22 '20 at 08:55
  • @DecimalTurn, can you check the images that I have just added to my description. It shows that with shapes.addpictures false, true, I have an error 1004. In other cases, I have no error. – Théodore Bedos Mar 22 '20 at 08:56

3 Answers3

0

I streamlined your code so it becomes possible to see what it's doing. Note that I avoided reading values from the cell's properties which were just set or are obvious.

  1. Once the column width has been set, the width of all cells in it are the same.
  2. The Left property of all cells in a column is always the same.
  3. If the column is column A, the Left is always 0.

Of course, what you tried to achieve is to enter a value only once. That is good practice but to read properties from the sheet is slow. The faster way - less code volume and better readable, too - is to declare a constant at the top and use that name in the code.

So you end up with this code.

Private Sub Convert_Img()

    Const ClmWidth As Single = 30
    Const RowHight As Single = 150

    Dim EndPictRow As Long, R As Long
    ' sorry, I recommend i for arrays and R for rows (C for columns)


    With Worksheets("Main")
        ' End(xlDown) will find the first blank cell below the base cell.
        '   There might be more filled cells further down.
        ' End(xlUp) will find the first used cell above the base cell
        '   disregarding more blanks above that one.
        EndPictRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        ' no need to set the column width multiple times in the loop
        .Columns(1).ColumnWidth = ClmWidth

        ' this loop will never run if R < EndPicRow
        For R = 3 To EndPictRow
            With .Cells(R, 1)
                .RowHeight = RowHight
                ' this will work only once:-
                .Worksheet.Shapes.AddPicture CStr(.Value), False, True, 0, .Top, ClmWidth, RowHight
            End With
        Next R
    End With
End Sub

The reason why it works only once becomes quite obvious. The new picture takes its source path from the cell's Value. Of course, once you insert a picture (image) in the cell that value can't be the path (string) anymore. If you run the code a second time it will fail. However, if that is your need, it should be possible to extract the path from the formula that defines the picture in the cell. Given that the picture itself isn't present at that location the formula should either hold the path or a reference to a location within the workbook's background data, depending upon how it was loaded.

Variatus
  • 14,092
  • 2
  • 12
  • 29
  • Hello, Thanks for your help. But I think that you didn't understand my problem. You said: "the reason it works only once becomes...", but that's the problem it doesn't work at all, never. Even with your code, I tried and I get the same message than on my image3 (link in my question). The only way it could work, is if I manually insert the picture before. If a picture as already been inserted manually once on the sheet then I can delete it and it will be inserted succesfully if I use my code. – Théodore Bedos Mar 22 '20 at 12:16
  • But yes, I agree, my code is full of little error that slow it down and it's not perfect, but I really don't think the error comes from that. – Théodore Bedos Mar 22 '20 at 12:21
  • Please focus on the `Value` property. Initially, if it is a file name with a full path, it should work. If you are saving the picture into the workbook (you can try to just save the reference for external storage), Excel may remember the location. Look at the cell `Value` after the macro has run. It doesn't contain a path string anymore. I don't know what you are trying to do but it's the content of that cell which is causing the malfunction you complain about. Try keeping the path in another cell, reinstate the `ClearContents` for the target cell and try running the code several times. – Variatus Mar 22 '20 at 13:12
  • Sorry but I don't understand. I have tried to write my paths on another column thta I don't clear and I have excately the same issue. If I keep running my code again and agian, I just keep having error 1004 if I use addpictures, and if I use Pictures.insert I just add more blank pictures like my "image1" on top of each other. No matter if I save the document or not in between. – Théodore Bedos Mar 22 '20 at 17:21
  • The code reads the path from the cell and then inserts the picture in its place. Therefore on second run it shouldn't work. The question shifts from why it doesn't work to why it does. However, if there is a path in the cell it should work. You now state for the first time that you get a blank picture. Before you said you get none. That points the finger at the path. The requirement is for a file name with full path. – Variatus Mar 23 '20 at 01:21
  • 1
    To ensure progress, please run the code on a single cell (disable the loop). Make sure that you have a complete path, starting with the drive letter, in the cell. Test the path and file name using `Dir()` to make sure it has no typos in it. Run the code only once. Let us know the result. – Variatus Mar 23 '20 at 01:26
0

EDIT: Based on you comment I looked at how file paths have to be specified while running VBA on a Mac and found this article by Ron de Bruin. Turns out you have to use " : " as your path separator instead of " / ".

This should fix the problem you had with the .AddPicture method, but I'll still leave my original answer below just in case.

The issue you're describing seems to be the same as this question. Unfortunately, it doesn't seem like the answer provided there really solves your problem either.

I don't have a Mac, so I'm unable to test this, but I would suggest you to try this potential workaround: Instead of inserting a picture, you could insert a rectangular shape and then fill it with the picture you want.

The code for this would look like this:

Sub AddFilledRectangle()

    Dim FullFileName As String
    FullFileName = "Users:File:Path:img.jpg"

    Dim MyShapeTop As Double, MyShapeLeft As Double, MyShapeWidth As Double, MyShapeHeight As Double
    MyShapeTop = 100
    MyShapeLeft = 100
    MyShapeWidth = 100
    MyShapeHeight = 100

    'Create a new rectangular Shape
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, MyShapeLeft, MyShapeTop, MyShapeWidth, MyShapeHeight)

    'Change the filling options
    With shp.Fill
        .Visible = msoTrue
        .UserPicture FullFileName
        .TextureTile = msoFalse
        .RotateWithObject = msoTrue
    End With

End Sub

Hopefully this works on the Mac version of Excel.

DecimalTurn
  • 2,480
  • 2
  • 14
  • 27
  • Hi, thanks for your answer. Interestingly, it didn't work but I received an error message that said "error70 access denied". Maybe it would mean so that the problem comes from my settings? Do you have an idea of what I should look at? – Théodore Bedos Mar 22 '20 at 22:10
  • @ThéodoreBedos - Unless you don't have permission to access the file/folder (which I doubt), it's most likely a problem with the file path (`FullFileName` variable). Can you try to put the image in the same folder as the workbook and use the following to see if it works? `FullFileName = ThisWorkbook.Path & Application.PathSeparator & "IMG_9040.jpg"` – DecimalTurn Mar 22 '20 at 22:38
  • Thanks again for your efforts. Unfortunately if I try to use FullFileName = "Macintosh HD:" & Replace(Worksheets("Main").Cells(i, 1).Value, "/", ":"), I then have an error message: error <<2147022894- (80070002)>> "the method <> of Object <> has failed". – Théodore Bedos Mar 23 '20 at 08:16
  • But when I did your other proposition, FullFileName = ThisWorkbook.Path & Application.PathSeparator & "IMG_9040.jpg" after moving the picture in the same folder as my excel file. I received again "error70 access denied". So I tried to change the write of the folder from drwx-wxr-x to drwxrwxrwx and still no difference. I am starting to fill deseperate... By the way, even if do Thisworkbook.path, it gives me a path with some "/". – Théodore Bedos Mar 23 '20 at 08:23
  • @ThéodoreBedos, have you tried using the Replace function on Thisworkbook.Path to switch from "/" to ":" ? Might want to try the original `.AddPicture` as well while we're at it. – DecimalTurn Mar 23 '20 at 08:29
  • Yes I have tried and it didn't work. I don't know if you have seen my comment below, but it is actually an access problem. The problem is now that I need to give manually the authorisation every time. – Théodore Bedos Mar 23 '20 at 09:31
  • @Théodore Bedos, you could make this permission problem into another question. You'll have more chance of getting an answer. – DecimalTurn Mar 23 '20 at 10:02
0

Ok, so it's not perfect yet, but I put the loop off and I used Dir() as you said @Variatus.A pop-up window like this opened when I executed the command Dir(). It asked me the authorisation to access the file. I pressed yes and then it worked. It worked but only for that file. So I guess, I am on the right way now and I need to find how to give access to all the files in that folder before running the code. I can't do it for all of them. Thank you very much.

Théodore Bedos
  • 11
  • 4
  • I noticed you deleted you other answer, personally I would have suggested to combine the two in order to show to whole reasoning process towards the solution. That would be very helpful if someone with the same problem find this question later. – DecimalTurn Mar 23 '20 at 22:26