Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: vba scrpit

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    does anyone know how to (or wether or not it is possible) to create an object that opens the vba script in another excel file without opening the excel file itself?

    Or how to extract the vba script from an excel file without opening the excel file??

    cheers

    Ed

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ed
    I don't know about getting the script from another Excel file, but you can run code from a module which has been exported from another Excel file...
    Comprende?
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how would you do this?

    is it possible without opening the orrigional excel file?

    Cheers

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 01:39, Jackeb wrote:
    how would you do this?

    is it possible without opening the orrigional excel file?

    Cheers
    AFAIK you have to open up the file.
    I beleive there is code on this board to get the VBA code from an open file.
    Do a seach.....
    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ed
    You can import code on the fly via VBA without opening an Excel file. You can import the module alone without the overhead of opening another workbook. I posted an example of how to do this yesterday. Will try to find the thread...
    Tom

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Tom this would be a life saver!!

    Ed

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is an example...
    Not as complicated as it looks, I'm just not real good at explaining...

    Create a folder:
    "C:/DynaLoad"

    Open a new workbook, add a module,
    rename the module "DynaMod"

    Place the following procedure into
    the module:

    Sub LoadDynaTest()
    Sheet1.Range("A1") = "Tis' Loaded!"
    End Sub

    Export the module into folder
    "C:/DynaLoad"

    Now remove the module from your
    workbook all together.

    Insert another module into this
    workbook(name does not matter)

    Place this sub in the module:

    Sub CallDynaLoadedProcedure()
    LoadDynaTest
    End Sub

    In whatever way you want to call it, call it:
    The example here loads the module when the workbook opens.

    Private Sub Workbook_Open()
    Application.VBE.ActiveVBProject.VBComponents.Import "C:/DynaLoad/DynaMod.bas"
    CallDynaLoadedProcedure
    End Sub

    You cannot call the procedure directly
    until the bas file is loaded or you will get a compile error. That is why
    we have the middleman procedure.
    Save the workbook. Close it.
    Open it. Check sheet1.Range("A1")
    Your module is loaded into the project if the range has the data...

    Tom

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    tom....

    this is gonna save me... period

    Ed

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 02:07, Jackeb wrote:
    tom....

    this is gonna save me... period

    Ed
    Ed sorry for putting you wrong I took your Q
    literally to mean Getting a VBA module from
    another work book....what Tom has proposed
    is to actually just import code into your
    book.....this of course can be done. The other way (getting code from another book)
    will require the book to be OPEN.


    Kind Regards,
    Ivan F Moala From the City of Sails

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    gotya, my solution requires a bit of both, the problem is that when I open the excel file it crashed on the open and automatically closes (COE3) so I cannot get to the vba script.... as tom says the only way appears to be to export the orrigional vba script......

    this one is tricky

    Ed

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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