none
Excel Macro to insert File RRS feed

  • Question

  • Hi,

    I need to create a macro in excel that will insert a particular wordpad file.

    I am writing a powershell script to create an inventory from a bunch of text files.  The resulting Excel Workbook has 3 columns: Hostname, Model & Serial.  I then want to be able to create a fourth column that will contain, as a wordpad object, the original text file, where the name text filename contains the Hostname. ie. the Hostname in Column 1 will be in the filename of the object in Column 4.

    I have this as a starting point:

    Sub Insert_OLE_Object()

    Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10
    End Sub

    and I assume I need to create a foreach to run through each item in the list, and define the variable name so it is based on the contents of the cell in Column 1.

    I have not used VBA before, and would really appreciate being pointed in the right direction, or to know if this is even possible!

    Many Thanks,

    Rob


    Wednesday, November 12, 2014 4:04 PM

Answers

  • The following should work for you

    Range("D1").Select 'The location to place the object
        ActiveSheet.OLEObjects.Add FileName:= _
                                   "C:\Test\Router01-Live-ShVer.txt", _
                                   Link:=False, _
                                   DisplayAsIcon:=True, _
                                   IconFileName:="C:\Windows\system32\packager.dll", _
                                   IconIndex:=0, _
                                   IconLabel:="Router01-Live-ShVer"


    Graham Mayor - Word MVP
    www.gmayor.com


    Saturday, November 15, 2014 12:15 PM

All replies

  • Hi,

    I need to create a macro in excel that will insert a particular wordpad file.

    I am writing a powershell script to create an inventory from a bunch of text files.  The resulting Excel Workbook has 3 columns: Hostname, Model & Serial.  I then want to be able to create a fourth column that will contain, as a wordpad object, the original text file, where the name text filename contains the Hostname. ie. the Hostname in Column 1 will be in the filename of the object in Column 4.

    I have this as a starting point:

    Sub Insert_OLE_Object()

    Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10
    End Sub

    and I assume I need to create a foreach to run through each item in the list, and define the variable name so it is based on the contents of the cell in Column 1.

    I have not used VBA before, and would really appreciate being pointed in the right direction, or to know if this is even possible!

    Many Thanks,

    Rob


    Hi Rob,

    Why create a OLEObject? If you could create a Hyperlink to the PDF instead, isnt that a much more easier approach?

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Wednesday, November 12, 2014 5:41 PM
    Moderator
  • Daniel certainly has a point, but before dismissing your original premise, what is the file type of the 'WordPad' file? There is not really any such thing as a 'WordPad' file. WordPad is a simple word processing application that can open a varierty of file types. Do you mean a 'plain text' file?

    Having overcome that obstacle, what exactly does the file contain? Excel is not the best vehicle for containing large blocks of text, but smaller texts could be inserted directly into the cell.

    It may still be better to link to the file, but before doing so you need to ensure that anyone who has access to the workbook will also have access to the files to be linked.


    Graham Mayor - Word MVP
    www.gmayor.com

    Thursday, November 13, 2014 6:10 AM
  • Hi,

    I need to insert a text file that the information in the workbook is originally taken from, so that it is embedded with the excel file if it is moved around between computers.  I created a hyperlink without problems, but the Excel file is going to be transferred, possibly many times, without the relevant text files, which breaks the hyperlink.

    I've started to write a macro:

        Sub Insert_Text_File()

        Worksheets(1).OLEObjects.Add Filename:="C:\Test\Router01-Live-ShVer.txt" 

         End Sub

    This is merely the first step to test the macro, I will eventually change the filepath etc. and then either loop through the cells in the column via the macro or powershell, not sure which is better yet.  The macro works fine in the spreadsheet, but I'm having difficulty making it available to the workbook, when I run the Powershell script.  I think this may have something to do with the fact that I'm opening an existing CSV rather than a new Excel file, therefore opening the Personal.xlsb file??  Also I'm just in the process of automating the choice of cell in which to run the macro.  Long day ahead.

    Thursday, November 13, 2014 10:15 AM
  • I just added this:

    $xlmodule = $wb.VBProject.VBComponents.Add(1)

    $code = @"
    Sub Insert_Text_File()
    Worksheets(1).OLEObjects.Add Filename:="C:\Test\Router01-Live-ShVer.txt"
    End Sub
    "@

    $xlmodule.CodeModule.AddFromString($code)

    $xl.Run("Insert_Text_File")

    which works a charm, just need to get it in the right location!

    Thursday, November 13, 2014 11:03 AM
  • The following should work for you

    Range("D1").Select 'The location to place the object
        ActiveSheet.OLEObjects.Add FileName:= _
                                   "C:\Test\Router01-Live-ShVer.txt", _
                                   Link:=False, _
                                   DisplayAsIcon:=True, _
                                   IconFileName:="C:\Windows\system32\packager.dll", _
                                   IconIndex:=0, _
                                   IconLabel:="Router01-Live-ShVer"


    Graham Mayor - Word MVP
    www.gmayor.com


    Saturday, November 15, 2014 12:15 PM