+ Reply to Thread
Results 1 to 7 of 7

include VBA macros as text file

  1. #1
    Suresh
    Guest

    include VBA macros as text file

    Hi there,

    I have a strange requirement. I am not sure if it is feasible. I would be
    very greatful if someone could provide a solution for this problem.

    I have to generate a file. This file would have a "command" or "code" in the
    first cell A1. When the user opens the file, the "Auto_Open" macro within
    the file would check this code and expand it as required.

    Now the problem is that, the file has to be editable by another program,
    which would modify the cell A1 and insert the correct code. But this program
    could work only with text files.

    I am aware that excel can process XML, CSV files (which are text file) and
    read in the data.

    My question is, is it somehow possible to include macros in XML file ? Or is
    there a better way to acheive this ?


    Thanks a lot in advance.



  2. #2
    Kletcho
    Guest

    Re: include VBA macros as text file

    Suresh,

    If I understand your request, you want another program besides excel to
    create a text file that is a VBA macro. An option for you may be to
    use VB script. VB script is like VBA but a little less function and it
    allows you to control excel from outside of excel. Here is an example
    to get you started:

    dim ExcelApp, ExcelWB, ExcelWS
    set ExcelApp = createobject("Excel.Application")
    set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls")
    set ExcelWS = ExcelWB.Worksheets("MyWorksheet")
    ExcelWB.Range("A1") = "Some value"
    set ExcelWS = Nothing
    set ExcelWB = Nothing
    ExcelApp.Close

    This is all untested. Save the file as a .vbs file and run. Hope
    that's what you were looking for.


  3. #3
    Suresh
    Guest

    Re: include VBA macros as text file


    "Kletcho" <kletcho@gmail.com> wrote in message
    news:1145457131.107166.242150@v46g2000cwv.googlegroups.com...
    > Suresh,
    >
    > If I understand your request, you want another program besides excel to
    > create a text file that is a VBA macro. An option for you may be to
    > use VB script. VB script is like VBA but a little less function and it
    > allows you to control excel from outside of excel. Here is an example
    > to get you started:
    >
    > dim ExcelApp, ExcelWB, ExcelWS
    > set ExcelApp = createobject("Excel.Application")
    > set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls")
    > set ExcelWS = ExcelWB.Worksheets("MyWorksheet")
    > ExcelWB.Range("A1") = "Some value"
    > set ExcelWS = Nothing
    > set ExcelWB = Nothing
    > ExcelApp.Close
    >
    > This is all untested. Save the file as a .vbs file and run. Hope
    > that's what you were looking for.
    >



    Thankyou very much.

    Thats exactly what I was looking for.

    hmm ... that means .... I will have to learn some VB Scripting now .. :-S




  4. #4
    AA2e72E
    Guest

    RE: include VBA macros as text file

    Yes you can include Excel macros stored in a text file at runtime: e.g.

    application.VBE.ActiveVBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromFile "c:\myxl.txt"

    This adds all the code from c:\myxl.txt into the ThisWorkbook class/module.
    Thereafter, you can call the macros as you would normally.

    PROBLEM:

    Once added, the code becomes permanent as soon as the workbook is saved,
    unless you remove the code. Is is Chip Pearson's site that gives details of
    how this is done? Someone with better memory will guide you.




    "Suresh" wrote:

    > Hi there,
    >
    > I have a strange requirement. I am not sure if it is feasible. I would be
    > very greatful if someone could provide a solution for this problem.
    >
    > I have to generate a file. This file would have a "command" or "code" in the
    > first cell A1. When the user opens the file, the "Auto_Open" macro within
    > the file would check this code and expand it as required.
    >
    > Now the problem is that, the file has to be editable by another program,
    > which would modify the cell A1 and insert the correct code. But this program
    > could work only with text files.
    >
    > I am aware that excel can process XML, CSV files (which are text file) and
    > read in the data.
    >
    > My question is, is it somehow possible to include macros in XML file ? Or is
    > there a better way to acheive this ?
    >
    >
    > Thanks a lot in advance.
    >
    >
    >


  5. #5
    RB Smissaert
    Guest

    Re: include VBA macros as text file

    VBA can import code from a text file like this:

    Sub test()

    Dim VBProj As VBProject
    Dim VBComp As VBComponent
    Dim VBCodeMod As CodeModule
    Dim strFile As String

    Set VBProj = ThisWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("SubsFromFile")
    Set VBCodeMod = VBComp.CodeModule
    strFile = "C:\codetester.txt"

    VBCodeMod.AddFromFile strFile

    End Sub

    But because you alter the VBE the workbook will need to
    re-compile.
    It will be much simpler to read a text file, but don't import
    it in the VBE. Just get the needed data from that file and
    run a Sub according to that data. The other program can
    edit the text file.

    RBS


    "Suresh" <no-emails> wrote in message
    news:1145454974.26952.0@doris.uk.clara.net...
    > Hi there,
    >
    > I have a strange requirement. I am not sure if it is feasible. I would be
    > very greatful if someone could provide a solution for this problem.
    >
    > I have to generate a file. This file would have a "command" or "code" in
    > the
    > first cell A1. When the user opens the file, the "Auto_Open" macro within
    > the file would check this code and expand it as required.
    >
    > Now the problem is that, the file has to be editable by another program,
    > which would modify the cell A1 and insert the correct code. But this
    > program
    > could work only with text files.
    >
    > I am aware that excel can process XML, CSV files (which are text file) and
    > read in the data.
    >
    > My question is, is it somehow possible to include macros in XML file ? Or
    > is
    > there a better way to acheive this ?
    >
    >
    > Thanks a lot in advance.
    >
    >



  6. #6
    Registered User
    Join Date
    01-11-2013
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: include VBA macros as text file

    @RB Smissaert: How would you simply read get the macro to read the text as you suggested?

  7. #7
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,146

    Re: include VBA macros as text file

    tsmabaso,

    The original poster or the person who provided the solution is not likely to reply after 7 yrs. Its better you post a new thread for your question.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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