How to embed a text file into a VBA module (if it's possible)

Not sure of you exact need here...

I'd like to do this in order to create a one-off self contained module that i can drop into an MS Access db and not worry about dependencies.

...can you post an example of how this will be used?
You mentioned line breaks, and your posted string seems to be HTML, ...so that may complicate things...

But in general, you can create a new module in the VBA editor, ...and declare a Public variable in it, ....then set the variable to your string:

Public Const strMyString As String = "<b>The quick brown fox jumps over the lazy dog</b>"

Now you can reference the string from anywhere in your Access app.
ex:
msgbox strMyString

There may be another way to do this with newer versions of Access, ...but this should get you started...

JeffCoachman

Storing it in a table is more or less the same as storing it as a file. it's still a separate entity to manage.

Dynamically creating a module might be an option.

Good to know the line break limit though presumably this is easily worked around by concatenating.

Thanks for the suggestions.

Still would be useful to know what these strings will be used for, ...in case there might be other alternatives, ...

But let us know how the Public variable in a module works for you...

JeffCoachman

The strings will be written out to a file. Yes i can assign them to variables but it's messy.

A very dirty workaround is just to paste the text into a dummy subroutine, export the mod (DoCmd.OutputTo acOutputModule, ....) and then read back in the contents of the dummy subroutine.

It would be less dirty if VBA offered a block commenting notation similar to /* this is a comment */ as this would ensure the dummy routine does not generate any errors if the text file accidentally contains some actual VBA (which it shouldn't in my case as it's an HTML template)


See attached screenshot example.
DummyHeader..JPG

(You probably know this already based on your posts, ...but...)

Not sure if it helps at this point, ...but you can comment out lines of code in VBA by preceding the string adding an apostrophe...

But let's see what other Experts may post in the next few days...

You wrote about a HTML template:

How do you use it? There is nothing wrong in having it as file on disk. Also your CSS can be in a separate file and linked in the HTML header.

The module generates documentation of a database in HTML format. To run it, i plan to drop the module into the target database and run it. But I don't want to manage any other files just the one module.

You have a misconcept here: Whether the data is in a module or a physical file, you still need to "manange" it.

And it is perfectly okay to use external resources like template files. Especially as you don't need to deploy your database, when you only need to change the template. Keep in mind, that if it is embedded in code, you need to run tests on your code base. This is not necessary, when using a file. The worst thing here is that you don't display/produce any data.

Or in other words: you're actually trying to violate the rule of simplicity.

Thanks good points. The question of what is simpler is perhaps out of scope of this question.

The answer to this question "How to embed a text file into a VBA module" so far is my dirty fix, or a string assignment.

Looking at the problem more generally another option is to deploy as a standalone db (rather than dropping a module into an existing db), in which case storing text in a table is an option too.

Hi,

In other words, you want to store a "ressource" within your VBA code.

As other said, it is much better to store it in an external file or, in the case of Access, in a table.

But if you really want the ressource within VBA, I won't suggest concatenating in a global constant (globals have a lot of drawback), but write a function that will return your ressource (and will be much easyer to read / edit:

Public Function getRessource() As String
    getRessource = vbNullString
    getRessource = getRessource & "<b>The quick brown fox jumps over the lazy dog</b>" & vbCrLf
     getRessource = getRessource & "</br>" & vbCrLf
    getRessource = getRessource & "<b>The fast black cat run around the lazy dog</b>"
End Function

Select allOpen in new window

hi Fabrice, thanks yes i certainly wouldn't use global constants. This question is how to store it within the module, rather than to consider the pros & cons, which would also require a review of more information than is provided in this question.

Reason: it's a direct answer to the question (albeit dirty), rather than an alternative.

IF I understood right:
You export your code module, read it back filtering whatever you don't want to obtain your "ressource".

I fail to see any advantage over a function returning the "ressource" directly, as in both cases, the "ressource" is hard-coded.