Use code to insert multiple pictures from list based on a call value

Hi Adam,
Will all the relevant pictures be in the 'Flowers-List' sheet? You know you could even have a list of picture files on the system and import those so that it keeps the file-size down to a minimum.
I'll read you explanation wit more attention and i'll take a look tomorrow during the day since it's almost midnight here and see if I can come up with a quick and simple solution.

Hi, I suggest you take a look at the post by MyVB_polly
More or less the same
I'll try later today to see what I can offer, I workd on this one too

It's a variable that is set in one of the pages, I nevre had-code things like printers, file locations, filenames in code since these are prone to change.
Basic rule in programming is to NEVER hard-code values that are system or user dependent
Like I said, I'll work with the file you attached and use the idea of the file I worked on.
I won't be able to start on it 'til after lunch (here)

You cal so check out a posy by anilpatni1234 in May

I’m testing my modifications and will post soon

I've attached a modified file, I'll have to explain the idea but it's late.
For now, I added two new worksheets, 'Admin Sheet' and 'Catalog - Flowers'
When the file is opened it will take you to the Catalog - Flowers sheet and since the image folder is unknown on your system it will prompt you to select or create a folder and select it.
This is all stored in Admin Sheet. Leave the top row intact please
Column B2 through B5 will contain the file paths, if incorrect or empty the cell is coloured red
I extracted four images and saved them as gif files and have placed these in the Flowers zip file
The other is the Excel file
I suggest you unpack the zip files in the folder where you will place or already have the mailing list.
The Flowers will be extracted to a sub-folder named Flowers and in there the image files.
I used the gif extension in the macros, but if you have bmp or jpg files then you will have to change that, but for this test the gif will work.

In the Catalog - Flowers sheet.
Column B contains the name of the flower (no file extension) and the image file should have the same name to be found.
After you have entered the correct name you can double-click the cell with the name in column B and the image will be place in column E as a thumbnail
Right click column B and you will the full size image in a user form just to see it.
So far this is what I did.

The further idea would be to have dropdown lists in the sheets were the flowers are selected for the letter and the full size picture is then retrieved for the letter
But that's for later. I'm off to bed now.

I'm sure there will be many questions but I'll answer them all
I attached one of the images separately as thumbnail

I’m not at my system now but will check later this afternoon
You can remove the line searchfolders or like that in the thiswokbook workbook_open event
Okay, understanding that catalog and list are the dame nut iT was just an idea, i’ll read what you wrote.
It should all work on a Mac as well, which Mac office version?

You can also google Ron de Bruin’s site also has sample code for Mac and the differences great site for many things as well as the ribbon too

I'll come up with my idea and approach, maybe that'll help and maybe even make it simpler with the same result, letters (as PDF)

Hi, I've come up with idea and am working it out, will upload asap, just some tests and explaining to add
The folder selection is due to the \ in the vba code

Change this

Please Login or Register  to view this content.

to this and that should solve it.

Please Login or Register  to view this content.

Good morning Adam,
Just to tell you that I haven't forgotten you and that I ran into some time issues and did not complete what I had in mind,
Still working on it and will update asap.

At least for Flowers it is starting to look good, once I have that sorted out it will work for the other categories too.
I've attached what I have so far as well as the sub folders and images I'm using.
I suggest you place the file in a specific folder and the subfolders in this same folder
The file will open with a splashscreen which dissapeears after initializing (+/- 5 seconds) and the Catalog sheet will open with a menu floating.
I'll have to write the 'manual' but that hasn't come so far yet.
If you select Flowres in the menuç comboox you'll get some extra buttons and select the Flowers - Input button to show the for for the input list.
I do NIT use the Flowers - List or the other xxxx - List files for that matter.
All images are read from the catalog sheet, right click column B to viesw the image (is also to be used to edit but not yet completed)
Right click the first empty row in column B to add a new image and category
Hope this all works, it does here

The EXPORT PDF works, but I haven't implemented the part that inserts the image in the letter, so just the name and size and price. sorry for that
Once you inserted the names in the Flowers Input then the Latter is filled with the text parts (your formulas) and you press the export button an export file will be created in the apps folder named Flowers-Week-??.pdf
The week can be changed with the same image select form

One more, the 'NoImgae.jpg' must also be placed in the app folder, not in the subfolders, you can remove it form these sub-folder

The code is in plcae, just the placement is missing. Will work that out and that part will be fixed too.
I'm waiting for a fridge delivery, but once it's in and installed I'll have the time

Okay, I made some changes and maybe it's too much.
I eliminated the xxxx - List sheets and just left the xxxx - input and the xxxx - Letter.
There is an extra button on the Catalog sheet that will list all the missing image names found in the sub-folders all you need to do is complete the size an price
Once this is done you can open the menu select the category and then the xxxx - Input to select the plants for the input list.
This updates the Letter sheets.
In the letter sheets there are two buttons, one is refresh and that will tun through the names and place the images
The other creates the PDF file
I've attache the new Excel app and a PDF sample, the code automatically takes care so that only the pages with data are exported.
I do however have to fine-tune the layout, I see that page two starts too high that means the top rows of a new page have to be a little higher. will do so tomorrow

There is also a macro that can be run if you add a new category that will create the input and letter sheets.
There is a BLANK - input and a BLANK - Letter that is used as template

Hi Adam,
I started writing a manual explaining the works step-by-step with screenshots and the ideas behind it.
Also this “final” version has some additional functionality which comes in handy and makes manual changes obsolete
Hope to complete it by Sunday
Also goto the page setup working

Got it, let me work it out some more

Well, I got the page setup and logo and everything nice and running as I understood you.
It was a little more than just format and paint, I didn't get to the manual but the Admin Sheet is the sheet that takes care of things like this.
I did add the option in the admin sheet to select another pdf output folder for the pdf files.
Double click B2 and you can select another folder for the export location. Now it's exported in the same folder as the application.
It's version 1.4 now and I also attached a PDF export sample
The export is formatted automatically to the max of sheets as there are images with a max of 7 pages.
I'm off to a late bed now

Strange am on-the-road Will look into it and answer your questions

Strange about the With fldr ....
I don't know why you didn't get the error earlier.
There seems to be a problem with the References that you can access when you go to the menu item Tools: References
These are the five that I have referenced, xx.x stands for the Excel version, in my case 16.0
OLE Automation
Microsoft Forms 2.0 Object Library this is probably the one that's not working
Microsoft Office xx.x Object Library
Microsoft Visual Basic for Applications
Microsoft Excel xx.x Object Library

If that one isn't working then the folder select routine won't work...

I'll check, but there should be an equivalent for MAC Office

That's no problem, does this mean that it is possible that you have a LETTER sheet with 50 pages? Not that that's a problem, just to know.
Combining the PDFs shouldn't be that much of a problem.
The delivery at the end?
Did you manage to get the select pdf folder to work with what I posted?

I added the pages and used the code to reorganize.
Page setup is done, I haven't yet looked at combining the PDFs, but... that's for some time late tomorrow

One thing I forgot to change is the formula in the flowers I the flowers - input, it pints to flowers - List and that should point to the season catalogue
I’ll change this when I turn my system on at around 8 p’vl, another one, could you export the input. - list contents to a XDV file and attach it? Or else send me a pm

External links corrected and all updated

The formulas referred to the other file
Corrected that in the last attachment

No problem, just one thing to be sure. you mention four worksheets, but will is always be the four?
Maybe once Front, Foliage etc and Delivery? Other times Front, Flowers etc, Delivery
And then of course all four: Front, Flowers etc, Foliage etc and Delivery?

Collating PDF's no problem, here's a blank result with 101 pages no data except the last one, the Delivery Info page
I generated a name showing the data en time in the format yyyy-mm-dd hhmm, the week number i did not yet include but maybe you or your sister already have a naming convention in mind?
Will fine-tune it Thursday, Wednesday will be a little difficult for me.
If your sister already has the list of names and size and description, you can either send me that excel sheet or export that part as csv or text file to import it into the catalog sheet
Good-night for now

Okay, let me take a look, won't be til late tonight, have an Excel project this morning and later today we're picking up our daughter from the airport.

Okay, I think I got it all:
The week number will be prompted the moment you press Export PDF button on the Letter sheets
The name of the PDF file is now: Wholesale_Pricelist 2019 Week38-(2019-09-12-0007).pdf
Just edit that part if you want it otherwise. The part in the () can server for your control to see when the file was created and at what time to know which one is the most recent, instead of the actual system timestamp

Great, let me know.
If you still encounter problems with the MAC, I may have the solution, that when the user is working on a MAC he/she will get an input box to enter the folder-name instead of the vba routine with the browser.
It is however strange that that doesn't work but I do not have a MAC at my disposal to test

Just in case there are still issues,
This is the site I use frequently for Windows but it also has a MAC section, and here there's a sample code to select a folder on a MAC
https://www.rondebruin.nl/mac/mac017.htm

If you can test this on the MAC then we could add the the test, if it's a MAC run this macro if Windows the other one, that's not complicated

Will take a look, FYI right-click G1 in admin to inhide all sheets right-click again to hide them

I would like to point out that the title page you're using is not the same as the one you attached in the 'Mailing front page.xlsx' file, there the letters are white.
My output does not have the page number i=on this page or the email address, so they're different, and I do not know where the page size differences come from.
Probably the printer settings on the MAC, these if set incorrectly define the page output when exporting to PDF.
This is something I cannot correct with the macro and the macro doesn't do anything with the page layout except exclude those without a picture

I played around with the file but doesn't really change much, except that the macro button on the Letter sheets does its work and the result maybe for a top margin or so looks good and all fits as it should on the pages.
I attached the PDF output I got as well as the last file with modifications 1.4.5
If you read some articles on the internet you'll also see that it's very dependent of the installed printer how the result looks, some printers can star print right at the top of the page so the top margin may be smaller.
In my case the Brother Inkjet has a wider top margin than an Epson inkjet, and then a lot smaller than an HP inkjet.
The macro that exports all the sheets is Excel_ExportPDF_Multiple_Sheets
The macros Excel_ExportPDF and Save2PDF are no used.

prepare4OutPut prepares the sheets and Set_Letter_PageSetup does take care to make sure the row heights are correct using the range in the Admin sheet

Remember, if you still want to let me help with the part of reading a csv file with the list of flowers input information you might already have and don't want to enter again.

Hi Adam, I think we can close this post.
For those following this post I have attached the final file but without the Delivery info in the Delivery sheet and an empty catalog

Thank you for the compliment