Posts by Miffo

    Re: Random Errors When Macro Is Run In A Bat-file

    Thanks for the quick replys... :)

    I tried to have like that, and gave it ample time (I thought), but as the queires takes longer and longer the more the month progress it becomes more difficult to predict how long is enough, specially with requests on customized reports that will require even more queries, and thereby increasing the time.

    I would like to have it all in one bat-file so that it becomes indipendant of the time-issue.
    As I see at the moment, the only way I can do that is by building a small application in a programing language that does what the bat-file does, but more smart so to speak, but I just felt that's a little bit overkill for the task at hand.

    Thanks anyway guys... :)

    Re: Random Errors When Macro Is Run In A Bat-file

    The batfile looks something very similar to this:
    (not Excel, still Code-tag??)

    CD Querys
    For %%1 in (*.SQL) Do Echo %%1 | isql -E -i %%1

    And this is where I would like to include

    CD Macros

    User interaction doesnt work. The computer literacy here isnt at top so to speak. We've had a couple having hard time using a mouse, and the files should be ready by the time they come in to work in the morning.

    Hello all!

    I dont know if this is the right forum for this, but I this is the best folrum I've found, so I'd thought I'd give it a shot anyway.

    The scnario is that I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.
    When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.
    When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.

    I was wondering if any of you know how to cuircumvent this/solve the problem?
    I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

    Re: Removing A Query


    Thanks for the suggestion, but the problem is that you solution only closes the workbook, not the application.
    I have to close down everythng when its done, and if I do this Excel will still stay open, with a new session opening next time the macro runs. It will run once every hour and if I'm away for 2 weeks I'll have 24*14 sessions of Excel open = memory hogging on server.

    I actually tried that once, but after the command


    the macro stops running, and I wont be able to do the last step:


    I also tried with the combination:

    ThisWorkbook.Saved =  True

    but it still gives me the "Do you want to save"-message then.

    Thanks anyway... :)

    Hello all!

    In my workplace I've been put in charge of deliviring a couple of reports. They are all done in MSSQL, and so far no problem. The problem is when I'm reformatting it in Excel.
    I start with an empty spreadsheet (with only the macro behind it) that runs the reports, reformat it and save it to another completly empty spreadsheet. This way I wont get any problem with file already exists or anything like that. My problem is though, that the file with the macro is supposed to stay empty. Everytime I'm running the reports it has to be closed, and to close it I have to save it so I wont get the annoying "The file has been changed, do you want to save it". When I save it the last query is saved with it, and it builds up, so if I'm on vacation the file will be huuuuge when I come back since no one has emptied the lingering query.

    My question is now:
    How do I delete a query before I save the file
    How do I close the file without saving it?

    Any option would suit me as the macro file should remain constant and not change at all.

    Any help here would be greatly appreciated.

    Kind regards

    Re: Counting Weeks With Date Time Spans


    Based on the cells in your document, try this:


    This takes the weeknumber of the date and do the calculation on that. That would mean that if both dates would be in the same week the result would be 0, therefore the +1 in the end.

    Im not sure what you mean by "Count the weeks", thats what we do in the row underneath where you write "Sum the weeks".

    This might not work so great overlapping several years though. Some weeks have 53 weeks (although just a few days week 53), and I'm always counting on 52 weeks no matter what.

    Kind regards

    Re: Looking At Emails Wont Get Recievedtime

    Ok, now I feel like a complete n00b... :)
    And the worst thing is I've been working in computer-support centre and used that very sentance you did thousand of times myself...

    Thanks for your help... :)

    Oh, and worked...

    Re: Looking At Emails Wont Get Recievedtime

    Hello :)

    As far as I know nothing has happend to the mailsystem, unless there's been an update that the system admins has done, but I did ask them and they say nothing has been done to the mailsystem.

    And right now the oddest thing happend...I tried to run it again, and it got stuck on the subfolder to Inbox now (the one I called "Subfolder1" in my script previously). I ran it three times, and suddenly it stops on the next lvl instead (the "The folder with the mails I want."-one), so right now Im not even goetting as far as to try to remove the "On error"-statement...
    The error Im getting now is: "Run-time error '-693894897 (d6a4010f)': The operation failed".

    I'm just confused right now...have no idea what can be causing this...

    Hello all!

    I have a problem I just cant understand.
    This code has worked for me about 6 months now, and I havent done ANY changes at all to it. Can any of you guys think of a reason why it wont work?

    The following is just en excerpt of the entire code, but the only thing thats relevant.

    Ok. As you might see here I have a mystery on my hands. The mails are found but without the recievedtime.
    Anyone knows what this can be?
    And as I said before. This code has worked without a problem for about 6 months now.

    Kind regards

    Re: Remove Duplicates By Id's

    Hello there!

    Before I begin I just want to say that my code probably isnt the best one, but since I felt I needed some practise I sat down and wrote some code for this.
    I did the assumption that one ID can occur more than just twice, so you have more then just two rows to compare (that would have been SOOOO much easier...). I tried my code a few times, and it worked for me on your example + 2 added lines with the ID 19 (as in your example) and codes that are similar to the ones in your example.

    Heres the code:

    As I said, there are most likely better ways of doing this, so you might want to wait until someones else helps you too.

    I hope I have commented the code enough to understand the logic in it (its not easy all the time)...

    Hope it works for you... :)

    Kind regards

    Re: Find File With Keyword In A Cell

    Well, actually...I didnt know about the Instr-function. Thats why I wrote my own.
    After having a look at it, my function and Instr is exactly the same, except that you have more options with Instr.

    Thats the way it goes when my boss wants something, but doesnt give me enough time to find the things I need... :)

    Thanks for the tip btw...

    Re: Find Selected Area's Workbook Name


    Not sure if Im on the right track here, but since you cant change the workbook when the macro is active the workbook you make the selection from will always be the active one. From there you should be able to get the name with:


    Kind regards

    Re: Find File With Keyword In A Cell

    Hello DauntlessUk!

    I just had a quick look at it, and as far as I can see you are using the wrong searchfield. In your version you changed to the cell 10,6 which would be the cell F10, but your searchcell in the document are placed at 10,4 which is D10. If you change the code to 10,4 instead of 10,6 it should work fine. At the moment the macro is reading the keyword from the wrong cell.
    Since the keyword is empty it will list all the files in that folder.

    You also have to have the last backslash in your path-string so it should look like: "C:\LEGENDS\Recipe Manager\Recipes\".

    I think that should do it. I will look a little bit closer later (I have to do my normal work too, the boss can be a bit strict on that point) and tell you if I find anything else.

    Kind regards

    Re: Find File With Keyword In A Cell

    Hello again!

    I tried to post earlier today but I only got "Page cannot be displayed", so sorry for the long reply, but its not my fault... :)

    I tried my own script a little bit more extensive and as far as I can see it works as it should, so I would think that the best option is if you can post your code you got from me here so I can see what has happend, and possibly come up with a solution.

    Kind regards

    Re: Find File With Keyword In A Cell

    Hi again!

    To change which row you want the hits to be starting on just change the startvalue of i from 1 to whatever you want (13 in your case).
    so instead of i = 1, just type i = 13.

    When it comes to the files not being displayed I didnt notive before, but it seems that the hyperlink doesnt include the last backslash used in the path-string somehow. I tried with just adding more of them, but to no avail, however it is quite easily solved. Try this code instead for making the actual hyperlink:

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Path & "\" & TmpFileName, _

    The only diffrence is that I added a fixed \ between the path and the filename. This should fix that problem.
    Oh, and I just assumed that you changed the path-string to your actual path on your hardddrive.

    Cant figure out why Excel wont include it in the string thou...thats really odd.

    Hope it works anyway.

    Kind regards

    Re: Find File With Keyword In A Cell


    I think I have something that can help if you havent been able to create a macro yourself.
    This macro has a button that points to the macro called ListHypers, and you enter your searchcriteria in the cell b1. All the hits are listed on top of each other in the column E. You can of course change it if you want. Its just a quick and dirty thing I made in 5 minutes here.

    Hope it helps.

    Kind regards

    Re: Copy From Line Below


    Theres a few things that need to be cleared out. If you have empty rows in your document, how many emty rows are there together (if its just one empty row at once, or if there can be several empty rows after each other).

    I did a quick and dirty little script that can do this, but it assumes there can only be one empty row at once. If it finds two empty rows it will stop.

    Can probably be done better, but it works anyway as long as theres only one empty row between filled rows.

    Re: Run Code On Cell Change On Separate Worksheet

    What I usually do in these cases is just make a sub-jump to a sub thats in the main module, and not bound to any sheet.
    If you need the cell-reference, just make the sub with parameters for the target.row and target.column.

    There might be a better way to handle it, but I found it pretty easy and useful.

    Kind regards


    Tried a couple of diffrent title that I think would be better suited, and wasnt allowed any of them. I hope this title is good enough

    My problem:
    I have written a macro that will open and close a couple of diffrent excel-documents for information-collection. To make it easier and more flexible I wanted to set up a constant with the name of the main document (where the macro is). so, assuming my document is called Document1.xls, instead of using the code:


    I wanted to create a constant with the current name of the workbook:

    Public Const ActWB = ActiveWorkbook.Name

    So I could use that constant everywhere instead of directly naming the document. That would mean that the document would be able to change name and I wouldnt have to go in and change the code for diffrent copies of the same document.
    The problem is that when I try to run it I get the errormessage:
    "Compile Error.
    Constant expression required"
    and the .Name is hightlighted.

    Anyone know why this is, and if possible, how to avoid or work around it?

    Re: Yes No Dropdown Menu

    I would think the easiest way to do that would be to create a validation list.
    Just write Yes and No in two diffrent cells, selectect them and give the list a name (in the top right corner, just above the columnletters).
    Then you just select the cell you want the drop-don list to be places, go to DATA - VALIDATION and in the list you chose List.
    In the Souce-field you write in the name of the list you just created (and dont forgoet the = before the listname).

    After that, every time you select that cell, you'll get a dropdown list with Yes and No.
    Its easy to handle in code, since the choise will be the lists value.

    If Cells(1,1).Value = "Yes" then
      'do what you want if they chose yes
      'do what you want if they chose no
    end if