0

I have an Excel sheet with an embedded txt file which I would like to copy into a certain target folder.

I used the following code which works fine if I use it from my computer:

'copy oleobject
ActiveSheet.OLEObjects(1).Copy

'paste to activeworkbook's path
CreateObject("Shell.Application") _
.Namespace(ActiveWorkbook.path) _
.Self.InvokeVerb "Paste"

However, some of my colleagues did receive the text file which was embedded in the Excel sheet with an additional line beforehand which included the file save path in the temporary folder. I could just delete this line with VBA but I would like to understand why this happens with other computers and not with mine.

Please help!

Thanks

Community
  • 1
  • 1
user3570409
  • 1
  • 1
  • 1
  • This method doesn't work for me at all. All I get is a Text file with some junk in the first line. I tried to use this before and ultimately settled on other methods of storing text within the document. What do you really need to do? Perhaps there is a better way than working with OLEObjects... – David Zemens Apr 24 '14 at 19:55
  • This txt file is a template and VBA is supposed to read it in again in a string file and change it. Until now it was just saved in a different folder and that worked fine but for mobility reasons (to be used by many other users) it would be a great advantage if it could be handled with just one file. I guess I could just put the text into the cells and hide it but that wouldn't be so elegant and easy to change if necessary. I also thought of using a text field but if the oleoobject would work I thought I could also embed other file types like pictures. Do you have a suggestion? Thanks! – user3570409 Apr 25 '14 at 05:01
  • What about storing the path to the template text file within a cell. Then your VBA code can look at the path, open the text file, make the changes and do a save as to produce the proper version of the file. I guess I don't fully understand the purpose of using Excel to manage the updates to the text file other than perhaps it is just being a VBA host? –  Apr 25 '14 at 16:01

1 Answers1

1

OK so here is the issue:

txt file is a template and VBA is supposed to read it in again in a string file and change it. Until now it was just saved in a different folder and that worked fine but for mobility reasons (to be used by many other users) it would be a great advantage if it could be handled with just one file.

It is possible to do this with txt file embedded in Excel, but embedded files are not really well-supported by VBA automation. Most files only have a few methods available, and if I remember correctly with TXT file type, the available method is to open the file.

ActiveSheet.OLEObjects(1).OLEFormat.DoVerb 1

I mentioned a similar problem that I had (in PPT, instead of Excel, but the issue is the same). The route we chose initially was given here:

Extracting an OLEObject (XML Document) from PowerPoint VBA

  1. Invoke the .OLEFormat.DoVerb 1 to open the file in Notepad
  2. Use WinAPI functions to read the contents of Notepad into a string variable
  3. Use WinAPI functions to close Notepad
  4. Use FileSystemObject to write/modify a new text file
  5. Embed the new, modified text file

The functions to find and read Notepad contents are documented here and require some use of WinAPI functions (all noted in the link):

http://www.excelforum.com/excel-programming-vba-macros/729730-access-another-unsaved-excel-instance-and-unsaved-notepad-text.html

This is a LOT of work for not much benefit, instead you could simply open a file dialog prompting user to choose the text file (located on a shared network drive, etc.). This would be much more reliable.

Alternatively, depending on the size of the text file, you can store the contents inside a Shape (and you can put the shape on a hidden worksheet, etc.), either in the .TextFrame or another property that allows text. Ultimately we abandoned the solution in the links above in favor of storing the contents inside a Shape's .AlternativeText property. This worked very well for us, in storing XML contents of about 500,000 to 1 million characters per file.

The reason we chose not to go the Notepad route is that there was a lag while the file is being read and user could accidentally interrupt the procedure, corrupting the file(s), etc. and that Notepad doesn't fully support automation, etc.

I also thought of using a text field but if the oleoobject would work I thought I could also embed other file types like pictures.

Pictures are easy to work with because they have a built-in .Export method.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130