none
VBA Stops when working in other Applications

    Question

  • Hi All,

    The scenario I have is as follows:

    I Have a Workbook containing VBA code that opens other excel workbooks (10 000's of Files) and transfers the data in those workbooks to SQL.

    The Issue:

    Whenever i leave the code to run and move to another application like outlook or word, the code stops completely without error or user termination warning.

    I get that this may be due to Excel Loosing focus on the windows its working on.

    one of the ideas i had was to try to not open the workbook i.o.w. : how do I copy data from the workbooks without opening it?

    or better yet, how do i prevent the VBA code from stopping?

    Many Thanks,


    Maheshvaran Padiachi

    Friday, March 22, 2013 10:44 AM

All replies

  • Hi Maheshvaran,

    I  think you can read it using Oledb, you can see how to do it from below pages

    Reading an Excel Workbook using C# and OLEDB

    Reading and Writing Excel using OLEDB

    Monday, March 25, 2013 9:15 AM
  • Hi Maheshvaran,
    I don't have an answer to your problem, but I have the same problem.
    Using OLEDB as proposed by Wilson is not the way which fits to my VBA code.

    Did you find an alternative solution ?

    Thanks

    Axel

    Sunday, June 23, 2013 6:14 PM
  • Hi All,

    The scenario I have is as follows:

    I Have a Workbook containing VBA code that opens other excel workbooks (10 000's of Files) and transfers the data in those workbooks to SQL.

    The Issue:

    or better yet, how do i prevent the VBA code from stopping?

    Many Thanks,


    Maheshvaran Padiachi

    Did you try adding this line of code ?:
    Application.Interactive = False

    (Don't forget to reset within an error handler or at the end of the procedure)

    Monday, June 24, 2013 7:32 PM
  • Hi Syswizard,

    thanks for your answer. Didn't know about that property.
    I'll try it and see if it fixes the issue.

    That may take a while as the bug is not occring very often.

    Wednesday, June 26, 2013 7:52 PM