Create a function from a text file. (1 Viewer)

Dan_T · Jun 30, 2005

Hi can someone point me in the right direction of this.

I wish to, in code, create a VBA function from the contents of a text file. Essentially I want to be able to see if a text file exists. If it does then take the contents to create a function. The bit I am lost on is how do I create a new function in code.

Many thanks,

Dan.

modest · Jun 30, 2005

First, search this forum on File System Object, or Open File. This will help you see if a file exists.

Next do searches on Active Code Pane

Go on MSDN or possibly in the forum (or google) for the Active Code Pane objects. Here are some functions that might be helpful to you:

VBE.ActiveCodePane.CodeModule.CreateEventProc
VBE.ActiveCodePane.CodeModule.InsertLines
vbe.ActiveCodePane.CodeModule.AddFromFile

Dan_T · Jul 1, 2005

Thanks Modest, its the Active Code Pane suggestion I was looking for. Its one of those ones that unless you have an incling what the function etc is called you can try all sorts of searches and be miles from the answer. The rest of it I am cool with thanks.

Many thanks

Dan

modest · Jul 5, 2005

Be careful with using it... one mistake could be drastic. It can overwrite your code or mess everything up. But it is pretty powerful.

Dan_T · Jul 6, 2005

year thanks Modest I have been treading carefully. Checking functions exist, finding where it is in the module and removing and replacing. So far just testing it with a test application. I am experiencing one minor irritation which I think I can resolve by running the function differently. However my concern is more that what I am doing is a no no.

The process is this.

1: Finds the existing version of the function.
2: Removes this version
3: creates the new version
4: Runs the new version

This work fine on the first run through, however if I call the code again the application crashes out and closes access offering me the chance to tell Mr Gates all about the error...

I don't need to run this code more than once in a session however I thought it prudent to test this out. Is there some fundamental mistake or 'No no!' to what I am doing?

Many thanks

Dan

ghudson · Jul 6, 2005

Are you just trying to update (replace) a module [public sub or public function]? If so then you could use the TransferText command. Check this thread out Export All Database Objects Into Text Files to see what I am talking about to replace a module.

Dan_T · Jul 6, 2005

Ah now this looks good. My situation is this. An application has to be up and running in 24 sites by the end of the month. Thats all ok, however due to someone else not delivering on time I will have to change some elements of a function and I don't want to have to roll out another version because one function has changed slightly. My thoughts were to provide the text for a function in a txt file in a specified folder, when the changes are in place update it. Now as I suspect this could be more than once I want to check and update when the application is launched.

Many thanks, I am orft to have a butcharse and play with the code you gracefully provided.

ghudson · Jul 6, 2005

I trust that your db is split [front end & back end] for you will always have to make modifications to your db and you will need to provide the users with an updated front end when every you make design changes.

Dan_T · Jul 6, 2005

Indeed it is split. The situation is this.

An application is heading off to 24 sites over the next month. It is built and has been tested over a month at 4 sites. However there is one small part missing, as I have been waiting on someone else to complete. Essentially I don't want to roll this out to all parties only to do it all again for the addition or change of one function. My thoughts were this, do tell me if this is wildly wrong conceptially. To provide the text for this one function, as it currently stands, in a txt file in a defined subfolder. The application will then create the function from this. Therefore when everyone else has got their act together and given me the final details I will be able to re issue the txt file via email, each user will, as it is within their skill base, replace the existing file with the new one and the application will read this in instead.

I've covered aspects of different BE locations, and differing references and their locations even in the runtime versions and this seems to be the final piece.

I have looked at the code in the link and it does indeed perform what i was hoping to achieve. Are there any draw backs? known shortfalls or issues with using this? I infact only needed the one line: Application.LoadFromText

Thanks again

Dan

modest · Jul 6, 2005

ghudson said:
I trust that your db is split [front end & back end] for you will always have to make modifications to your db and you will need to provide the users with an updated front end when every you make design changes.

If you use a MAF link as the front end, do you still need to send the update, or would it auto-update?

ghudson · Jul 6, 2005

modest said:
If you use a MAF link as the front end, do you still need to send the update, or would it auto-update?

What is a MAF link?

modest · Jul 6, 2005

MAF - Microsoft Office Access Form

If you make it, you can use shortcuts to it as a frontend, and I think it will autoupdate when you make changes.

Dan_T · Jul 7, 2005

Hi I am having a wee bit of trouble with executing the function:

ExportDatabaseObjects()

To me it looks fine but at this line.

For Each d In c.Documents

I get the error: Error 13 - Type Mismatch.

Up until this point all is fine with the world and the tables and queiries are beautifully exported to the location I have specified.

Any ideas?

Many Thanks

Dan.

I am using Access 2003 Win Xp Pro.

ghudson · Jul 7, 2005

Not a clue for it works fine for me with Access 2003. Try just running the piece for just the modules since that is all you want. Or is that where it is bugging out?

Dan_T · Jul 7, 2005

I've tried stepping through it and the parts for both forms and queries work fine. Errors occur for the steps for any part using the d variable for documents (Forms, reports, scripts). So I set a break point at the line:

Set c = db.Containers("Forms") 'Declared Dim c As Container

This sets C ok. Stepping through to the next line:

For Each d In c.Documents 'Declared Dim d As Document

The error occurs when this line is executed. When I check back after the error, hovering the mouse over the d variable I see, d = nothing. When I hover the mouse over the d variable on the following line:

If Left(d.Name, 1) <> "~" Then

I see, "d.name = <Object variable or With block variable not set.>

Thanks again for any help on this. I haven't changed any of the code other than to point to the folder I wish the .txt files to be created in, and obviously I have the DAO reference in place for DAO3.6.

Dan.

modest · Jul 7, 2005

You can't loop through it that way. Try this:

Code:

For doc = 0 To CurrentDb.Containers("Forms").Documents.Count - 1
    Debug.Print CurrentDb.Containers("Forms").Documents(doc).Name
Next doc

Dan_T · Jul 7, 2005

Hi modest, the way you suggest is essentially how it is for the queiries.

For i = 0 To db.QueryDefs.Count - 1

'Action for each in here.

Next i

I was confused as the d variable didn't appear to be set anywhere but knowing you can learn something new every day and ghudson has this working I thought I could be wrong in my thinking that it needs setting or a value passed to it before it can be used. Am I wrong?

I did think about making changes but it seemed only right to find out first.

Many thanks

Dan

Create a function from a text file. (1 Viewer)

Dan_T

Registered User.

modest

Registered User.

Dan_T

Registered User.

modest

Registered User.

Dan_T

Registered User.

ghudson

Registered User.

Dan_T

Registered User.

ghudson

Registered User.

Dan_T

Registered User.

modest

Registered User.

ghudson

Registered User.

modest

Registered User.

Dan_T

Registered User.

ghudson

Registered User.

Dan_T

Registered User.

modest

Registered User.

Dan_T

Registered User.

Similar threads

Users who are viewing this thread