Solved

Write to OLE embedded text file in Excel

Posted on 2011-02-11
5
465 Views
Last Modified: 2013-11-05
Hello there

This is definitely a 500 point question.
I've written a large "object-oriented" application in Excel 2003 vba and this includes writing a lot of settings to a text file. I read and write the text files line by line and I'm quite happy with the way this works.
HOWEVER: One of the main incentives for coding this project in VBA was that the application should be self-contained, i.e. just the *.xls-file and no extra *.txt-files or any other "tag-alongs" to keep track of. So I have to get rid of the separate text file somehow. As far as I can see, there are two options:

1) Use a multiline text box instead: This, however, requires that i can perform read/write operations on the text box in a similar manner as I do with a text file. This does not seem to be possible and I don't particularly want the hassle with chopping up a huge string in substrings containing a lot of special characters and so forth.

2) Embed the text file in my spreadsheet as an OLEobject. This works, but how the h*** do I access it programmatically for read/write operations in VBA without opening it for the user to see? Working with such embedded OLEobjects in VBA seems to be a nightmare. The .open and .verb methods just open the damned file for plain view.

I would appreciate any help here :o). I can't find anything useful on the internet.

Best regards,
Einar
0
Comment
Question by:EISTO
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34868955
If they are settings, why not store them in document properties, defined names, or even a hidden worksheet? (or really anything but an embedded text file!)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34868956
Oh, or the registry, depending on what the settings are?
0
 

Author Comment

by:EISTO
ID: 34868998

Hi, Rorya

Document settings? I'm afraid I don't know what you mean by this.
The "settings" are both settings for my objects and data to be kept between sessions in general. I would very much prefer to avoid using a spreadsheet unless I absolutely have to since this means entirely rewriting the methods for reading/writing settings.

Einar
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34869043
You can add custom properties to a workbook in addition to its built-in ones like author, company, subject etc.
For data that needs storing I would probably use defined names or the registry.
0
 

Author Closing Comment

by:EISTO
ID: 34869670
Ok. Seems that I will have to admit defeat and rework my functions since they store arrays as well as other data. Thanks for showing me custom document properties, though. Although rather limited in flexibility they might come in handy later on.

Einar
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now