+ Reply to Thread
Results 1 to 6 of 6

VBA to Import and Execute Code from Text File

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    VBA to Import and Execute Code from Text File

    Hello,

    Is it possible to write a macro that can import VBA code in a text file then execute it? I need this functionality as I have produced a corporate spreadsheet template that goes out to many people and I will need to be able to update it as requirements change once it has been distributed. So my idea was to build a macro in the template that has the code in it to import the "update code" from a text file that I would send to all the folks that have the template. If the template was centralized, that would save me from this issue but it is however going to be distributed widely.

    Thanks in advance.

    learning_vba

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA to Import and Execute Code from Text File

    I know it is possible to do this, but that would require people to lower the security level of their computers to allow this code to be imported and executed.
    And yet possibly the installed firewall (if any) might prevent this.

    Alternatively and safer you could add a macro to the template which implements the updates based on a set of parameters in a file. And you could send that file to your users.
    Have them place it in some directory which the macro reads.
    Of course the feasability of this solution depends on the kind of updates to be implemented, but you can do a lot this way.
    Last edited by Tsjallie; 07-22-2014 at 03:23 PM.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: VBA to Import and Execute Code from Text File

    I suppose that is similar to what I was asking about. The changes that will have to be made in the future will be formula changes. So on a given sheet, say Sheet1! range("A1") I had the value set to sum(A2:B2). And the update requires me to change that formula to sum("A2:B3"). I could have a text file that has the following line in it:

    =SUM("A2:B3")

    I could then have the macro read that line in the text file, set it to a string variable called strUpdate and then in the macro have code like this:

    range("A1").value = strUpdate

    That would work wouldnt it?

    Thank you Tsjallie.

    learning_vba

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA to Import and Execute Code from Text File

    I assumed you intended to import a complete macro and execute that macro.
    Didn't realize that with "code" you meant formulas.

    So yes, making a file with the changes and have a macro in the template read it and apply them will work fine.
    range("A1").value = strUpdate
    Correct code the enter a formula would be:
    Please Login or Register  to view this content.
    I would suggest to make a text file with 2 columns: one with the new formula and one with the cell or cells to put it in. Would make it more flexible if cells with formulas are added.
    By example:

    Formula............ Range
    =SUM(A1:A100) A101 'Puts a new formula in cell A101
    =SUM(B1:B100) B101 'Puts a new formula in cell B101

    You can extend the columns if more types of changes are required.
    Do notice that you need to thoroughly think over what kind of changes you would need to apply as it will be difficult to change the macro.

    BTW, why not just redistribute a new version of the template.
    Last edited by Tsjallie; 07-22-2014 at 04:40 PM.

  5. #5
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: VBA to Import and Execute Code from Text File

    Thanks so much- this will be huge help!

    About why not redistribute with new version...because its a budget template that takes a long time for people to fill out. And I anticipate there will be structural changes (primarily formulas changes) that will need to be updated after people have already started filling out the inputs. I want to prevent them from having to do a bunch of extra work.

    BTW- when you say two columns in the text file, I assumed they should be tab delimited?

    Thanks again

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA to Import and Execute Code from Text File

    I assumed they should be tab delimited
    Yes, I would go for tab delimited as recently I experienced Excel (or vba) behaving I bit nutty when using other delimiters.

    BTW, when you write that macro, don't forget to have it make a backup of the template before it starts with applying the changes.

    I see a lot of issues(not just technical) for you to solve in this project. I'm interested how you're gonna solve those. Do keep me posted.
    Last edited by Tsjallie; 07-23-2014 at 07:46 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Import text file from VBA code; import all numbers as text
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-19-2012, 05:19 PM
  2. Edit Text file import code
    By bryanbak3 in forum Excel General
    Replies: 1
    Last Post: 04-09-2012, 02:56 PM
  3. VBA code to import text file to the fixed excel file
    By wangdian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2010, 10:13 AM
  4. VB Code to Transpose & Import a Text File
    By Jdarris in forum Excel General
    Replies: 1
    Last Post: 01-08-2007, 10:49 PM
  5. [SOLVED] Import HTML code from a text file.
    By nathpai@walla.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2005, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1