Posts by insomniac_ut

    A very simple query function that takes in a path for a source CSV file and a SQL statement as a string (I'm also transposing the data from the VBA function),



    This code works intermittently against a CSV files, some data is retrieved correctly and some is not. An example are these two CSV files - Small and Large. The following SQL query works perfectly on the Small file, but returns #VALUE on the Large file,
    SELECT birthYear FROM [File].


    It's definitely not a data limit/size issue as the Full file only contains 1800 rows. Incidentally if I wrap up the logic into a Sub rather than a UDF then it works perfectly without any errors,



    I am very confused, and would appreciate any pointers. I have also posted this question on Stackoverflow, but no answers yet unfortunately.

    Re: Application.Quit - EXCEL.EXE is still visible in the Task Manager


    Just saw AAE's reply. When I'd originally posted there were no possible answers that came up.


    I've spent a lot of time thinking/researching this problem and I'm completely baffled. Initially I was running a set of scheduled tasks at night. This was easy enough to deal with, I wrote a C# class that uses the System.Process class to terminate EXCEL.EXE processes that begin between 12am and 6am. This worked fine.


    The problem I now have is that in addition to the nightly sched tasks, I am running an hourly one and I don't want to take a sledge hammer approach of terminating all threads. Which is why I started barking up this PID tree.


    That being said, AAE's links look *very* promising.

    Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    This batch script kicks everything off. This is the script that's wrapped into the Windows Scheduler.


    Code
    @ECHO OFF
    SET AUTOCOPY=TRUE
    START EXCEL "C:\Users\elvis\Desktop\Book1.xls"


    Books1.xls serves as the "calling" workbook. The XLL is loaded into Excel's memory via the Workbook_Open event once the AUTOCOPY envrionment variable is set.



    Once the XLL is cached, the OpenUp method (included in Module1 of Book1) causes Book2 (which does all the heavy calculations) to open up. Once Book2 stops calculating (after its Workbook_Open event) the rest of this module ia just logic to copy/paste value/number formats into the final workbook (Book3). Book3 is the file that's saved down with date/timestamp.



    The Workbook_Open event of Book2 doesn't really do much apart from calling CalcAll, a method in Module1 of Book2.


    Code
    Option Explicit
    Private Sub Workbook_Open()
        Call CalcAll
    End Sub


    The CalcAll method of Book2 loops through all the worksheets in Book2 and does all the relevant calculations. Depending on the complexity of the calculations (which depends on the time of day), this can take between 6 and 45 minutes.


    Code
    Option Explicit
    Public Sub CalcAll()
        Dim ws As Worksheet
        For Each ws In Worksheets
            ws.UsedRange.Calculate
        Next ws
    End Sub


    Once Book2 has finished calculating, it is saved via Module1 of Book1 and the application is terminated.


    Only it isn't as I can see a ghost EXCEL.EXE thread still running in the Task Manager.


    Thank you in advance.

    I am running a process via scheduled task where a batch file opens up an Excel
    session, loads an XLL, calculates a bunch of numbers, saves the values to a
    separate file with the date/time stamp, and closes down.


    This process
    works fine apart from the closing down bit. Though Excel itself is not visible
    any longer, an EXCEL.EXE is still running (with a large memory footprint) in the
    Task Manager.


    The problem is that since this process is repeated on an
    hourly basis, the scheduled task encounters memory overflow errors due to the
    ghost Excel threads, unless I physically terminate all the ghsot threads every
    few hours. This is really inconvenient.


    I've researched this a lot, and
    it looks like it's not an uncommon problem. So I was thinking that one
    workaround could be to log the PIDs of the processes kicked off by the scheduled
    task and then run another scheduled task every few hours to go and kill those
    PIDs.


    Can anyone suggest another technique? If not, could anyone point me
    to some sample code as I don't really know how or where to start.


    Thank
    you.

    Hello,



    I'm trying to copy a fixed dimension range from one workbook to the end of a worksheet in another workbook.



    This is the code I'm using to achieve this, though it's not particularly stable. I occasionally get a UsedRange of Object _Worksheet Failed or PasteSpecial Method of Range Class Failed error.



    Is there something wrong with this code, or can someone suggest some improvements?



    Thank you.



    Hello,


    I have experienced a very peculiar Excel problem that I am at a loss to explain. I have some VBA code to iteratively calculate 4-5 sheets across a single workbook, so nothing groundbreaking.


    By and large this code runs fine except for a particular section - it's always the same set of about 15 cells - on one particular sheet that always throws #VALUE erors after the VBA has run.


    To fix this problem I either hit Shift+F9 on the problem sheet or F2+Enter on the individual problem cells. without changing anything in the workbook, these cells now calculate. This works every time, so I can't understand why the VBA fails.


    The VBA itself is very simple. Simply a series of calls like:
    Sheet1.UsedRange.Calculate
    Sheet2.UsedRange.Calculate
    Sheet3.UsedRange.Calculate, etc.


    The reason I use UsedRange is because of this issue: http://support.microsoft.com/kb/919127. I also ran some VBA code to establish the bounds of UsedRange, and I can confirm that the problem cells are within this bound (they are the last column).


    The cells that throw the error are in the last column of the UsedRange on that particular sheet. I tried to extend the UsedRange by adding some dummy TODAY() / NOW() calls beyond this column, but to no avail.


    I then ran some VBA to try and see what the dimensions of the UsedRange was, and the problem cells fell within the UsedRange.


    I then tried to replace Sheet1.UsedRange.Calculate with just Sheet1.Calculate, but again received the same error - the 15 cells threw #VALUE and everything else calculated.


    It's not difficult to rectify the broken cells. I'm just curious to know what could be tripping it up to begin with.


    I was wondering if you could throw any light on this?


    PS - I can't attach the sheet because it uses some custom functions running off a commercial add-in.