Trying to bring in an image with VBE for Excel

Question

Hi I am trying to bring an image and a text file on the same row as a matched string in a cell an example would be 070.txt & 070.jpg. matching up with R17-8976-070. I seem to be able to bring in the text file alright but when I run the code for this the image comes in a long string of characters. Could someone please tell me what I am doing wrong? below is the code I am using

   Sub InsertStuff2()
   Dim myText As String
   Dim myImage  As Picture
   Dim fileLoc As String
   Columns("a:h").ColumnWidth = 13 ' adjust column width
   Rows("1:8").RowHeight = 55 'adjust row height

   Application.ScreenUpdating = False

   fileLoc = "Macintosh HD:Users:paul-walker:Documents:VBA_Scripts:"
   myText = Range("A1").Value & ".txt"   '<<-- Text file name
   myImage = Range("A1").Value & ".jpg"  '<<-- Image file name

   Set myTextFile = Workbooks.Open(fileLoc & myText)
   myTextFile.Sheets(1).Range("A1").CurrentRegion.Copy _
       ThisWorkbook.Sheets(1).Range("D1")
   myTextFile.Close (False)

   Set myImageFile = Workbooks.Open(fileLoc & myImage)
   myImageFile.Sheets(1).Range("A1").CurrentRegion.Copy _
       ThisWorkbook.Sheets(1).Range("E1")
   myImageFile.Close (False)
   Range("D1").WrapText = True
   End Sub

Use [.Pictures.Insert](https://stackoverflow.com/questions/12936646/how-to-insert-a-picture-into-excel-at-a-specified-cell-position-with-vba) to bring the image into excel and position it as per your requirement. See the accepted answer in that post. — Siddharth Rout, Nov 26 '17 at 07:01

score 0 · Answer 1 · answered Nov 26 '17 at 01:28

Neither the TXT file nor the JPG file are Excel workbooks. Excel will actually open the TXT file, converting it into a workbook in the process but it can't do that with a JPG file.

It's not clear what you intend to do with the picture (or the text). If you wish to import it into an Excel cell google for something like "Import a picture to Excel by VBA" and you will find plenty of guidance. Similarly, if you wish to import text into a workbook converting it into a workbook first which is then imported is likely to give you an uncontrollable number of rows. Regardless of the volume of text involved I would recommend a more direct and, therefore, more controllable method. Google for "Insert text file into Excel cell by VBA".

Remember to set ScreenUpdating back to TRUE at the end of your procedure.

Trying to bring in an image with VBE for Excel

1 Answers1