Importing Images/Pictures/Shapes from a Closed Excel Workbook

I have a Closed Excel workbook in which there are pictures inserted in a sheet. I want to import these pictures or Shapes from this CLOSED temporary workbook into my current Workbook.

• Does ADO have a method to write an SQL Query to import Images/Pictures/Shapes embedded in an Excel Sheet without opening the Excel Workbook?
• Any alternative methods to import the Images/Pictures/Shapes?
• Can ADODB.STREAM object pull all the images from the Sheet of the Closed Excel Workbook as HEX or BLOB data and store them in a Collection Class or Dictionary Class for later use?
• Can an MSMXML2.Document object be able to read the Sheet (.Load File OR .LoadXML Range.Value() ) of a the Closed Excel Workbook and have information of the Images/Pictures/Shapes in its XML, that can be processed further to recreate the images into the Current Workbook?

Any help will be most appreciated.

I'm guessing unless U open the file, U can't do anything. Dave

Thanks for your kind reply.

Just a random thought:

• Is there a way to hold the pictures in memory (maybe as a BLOB or a Picture itself) using some Custom Class (Dictionary or Collection Class) till the workbook (session) is open, and then, whenever needed, use them (convert them back from BLOB to picture) for inserting into a worksheet cells of current workbook for report creation? If so, how can such a Custom Class be created?

• Long ago, I had seen somewhere an example of someone converting a bunch of images pulled from a folder into HEX data, storing them into a Custom Array Class (to maintain live session) and then reconverting the HEX data back to individual images to be inserted into worksheets, whenever required. Unfortunately, i was unable to locate this code anymore.

What's the benefit if you can load any picture fast as lightning from your SSD-disc ?

@snb just to give you a brief idea:

the pictures are located on a far-off slow network drive (File Server is located on US servers and users are mostly from Asia and Europe) and the filepath length (too many long aphanumeric sub-folder names) sometimes exceeds 218 characters which results in import errors in ADO. So to avoid such errors, what i have done is used `WShell` and run `SUBST` command to map and shorten the network drive as Y:\ . This works well though still slow over network.

Now my code first imports all the csv's and Images from the network Y:\ folder in 1 go to a temporary temp.xlsx file created on the fly, which is used to reformat the csv data and resize the images, for later use for report generation. Once the temp workbook is created and has all the data loaded and formatted, then another button in current workbook will run to create multiple reports.

The problem is, i am able to import the reformatted csv data from this temp.xlsx workbook to my current workbook, but as we know there is no way to pull the images from an excel sheet using ADO.

So thinking of doing away with temp workbook and if there is a way to load the images and csv from a folder directly into a Class module in my current workbook, then i can create the reports whenever i want without much hassle.

Hope this makes sense somehow.

P.S: I don't want to bloat my current workbook with all the csv data and images in hidden sheets, that is the reason why i had to create the Temp.xlsx workbook.

Quote:

---

Originally Posted by snb

What's the benefit if you can load any picture fast as lightning from your SSD-disc ?

---

Exactly.
You're going to have to download them at least once, so why not get your vba code to download them to a local temporary folder (pictures and csv) and get your code to reformat the csv in that folder, then create your reports?

Quote:

---

Originally Posted by p45cal

Exactly.
You're going to have to download them at least once, so why not get your vba code to download them to a local temporary folder (pictures and csv) and get your code to reformat the csv in that folder, then create your reports?

---

I am additionally already doing that i.e. downloading the csv and images from mapped Y:\ network folder to a mapped Z:\ shortened local folder. This is just for testing my written code in a fast way from local folder instead of connecting again to network folder. I just change the network folder path in Current workbook Main Sheet to point to the created Z:\ local folder drive and it loads the csv and images into a created temp.xlsx workbook quickly. Then i manipulate the temp.xlsx workbook and pull the manipulated csv data into my current workbook to create required reports using ADO.

From your above note, I am inquisitive to know how to reformat the csv data in the folder itself?

Quote:

---

Originally Posted by sifar786

From your above note, I am inquisitive to know how to reformat the csv data in the folder itself?

---

What kind of transformations are you planning to do?
Supply an example and raw csv file (preferably one that hasn't been loaded in Excel and saved as a csv file because Excel makes some changes to it), perhaps also that file imported into Excel and transformed as you like it (attach Excel file), and if you want also supply a csv file as you'd like it to finish up, great, and I'll have a go.

@p45cal all the csv files are different in format and data and each requires different steps of transformation and different blocks of code. So not conducive to explain or share the same as it will take lot of explaining.

All i can say is i try to arrange and reformat the csv columns data as ADO-ready as possible.

Apparently you don't need/want any help.

@snb you are entitled to your opinion.

I am not an expert like you all, but trying to make good use of whatever i learn from each one of you.

Quote:

---

Originally Posted by sifar786

So not conducive to explain or share the same as it will take lot of explaining.

---

OK. Good luck.

Thanks for all your help guys!