Posts by AndrewJ

    Re: Constantly Running Macro


    Code
    Private Sub Workbook_Open()
         Application.OnTime TimeValue("15:00:00"), "MyMacro"
    End Sub


    Where MyMacro is the name of the macro you wish to run and resides in a Standard Module and has the OnTime Method again like below


    Code
    Sub MyMacro()
        Application.OnTime 
      TimeValue("15:00:00"), "MyMacro"
    'YOUR CODE
    End Sub


    http://www.ozgrid.com/Excel/run-macro-on-time.htm


    What you could do is test the folder for changes (count total files, look for total folder size changing) every few minutes, and then if it finds a change you could have it scan for all the files.

    Re: Set Range Of Varying Size


    Resize would probably work. Youll need to find the first blank in the row and then use that value in your resize statement.


    Code
    dim x
    x = Selection.Row


    ^ gives you the starting row (when you have the cell found by date selected)


    Code
    dim y
    
    
       For Each Bcell In Range("A1:A65000")
    	If IsEmpty(Bcell) Then 
              Bcell.Select
              y = Selection.Row
              Exit sub
            End if
       Next Bcell


    ^ will select the first blank in your column (I'm assuming you can hard code the column letter?)


    Code
    Selection.Resize(-(y-x), 4).Select


    ^ will select the rows from the first cell you found by date up thru the first blank




    Im still slightly unclear on what you're trying to do tho. Tell me if this is it.

    Re: Constantly Running Macro


    I dont think its possible to have a macro running while your working in excel unless you try something like using a userform. But for normal spreadsheet work I dont think itll happen.


    An alternative would be to use the ontime event to schedule a check every 30 seconds, minutes, 5 mins, or w/e. Your code looks very short and as long as it doesnt take a long time (if K: is a network drive and you have a good connection to it) to execute you could even do 10 secs and hardly notice it.

    Re: Text Stream


    Yeah I spent a while googling and looking through forums, couldnt seem to find anything on it at all... everything seems to require you to import the entire file or already know the exact text... no way to just grab one line by referencing the line number... but its weird because you'd think it SHOULD be possible...

    Re: Macro Stops In Middle


    I would expect that since you have the VBE open, the line ActiveWindow.WindowState = xlMinimized causes your macro to work, but when it is not open it minimizes the wrong window and screws up on which window SHOULD be selected. Use the hard coded name option above and let me know if it works :)


    Also, I doubt its actually stopping halfway, most likely its just copying blank data :)


    put a msgbox at the very end of your code - MsgBox "Macro completed" - and you'll see if it gets triggered.


    You can also take off all those row scrolling lines and just use Range("A1").Select, or just use a single line of scrollrow - scrollrow 1

    Re: Chart Menu Bar Wont Change To Data Menu Bar


    Found it.


    For anyone with the same problem - apparently the worksheet menu bar w/ chart menu bar has its own ID separate from the worksheet menu bar w/ data menu? My array that I used to save all open commandbars was apparently changing 'data' to 'chart' at the end of the macro. Application.CommandBars("Worksheet Menu Bar").Enabled = True fixed it.

    Re: Macro Stops In Middle


    OK, I think I understand. It looks like youre having trouble activating the correct workbook (havnt tried your procedures tho)


    try referencing the name of each workbook, you can hard code it since they dont look like they change


    Code
    Workbooks("add_ins.xls").Activate
    
    
    copy your data
    
    
    Workbooks("other_book.xls").Activate
    
    
    paste your data


    also, things like - "Columns("D:D").Select
    Selection.EntireColumn.Hidden = True "


    can be changed to Column("D:D").EntireColumn.Hidden = True


    you dont need any of the selects, itll only slow it down (although you will need selects for the copy paste method you're using)

    Re: Macro Stops In Middle


    Im a little unclear on how you structured this, tell me if I've got it:


    You have workbook1 open
    you have a macro in workbook1 that open a specific workbook, say "sourceworkbook"
    the macro located in workbook1 is supposed to copy/paste data from sourceworkbook to a newly created workbook?


    also, what do you mean it runs all the way through when you use the VBE but not when you use excel? Theyre the same thing.... VBE just accesses excel automatically

    Re: Text Stream


    Good to see Ger got ya straightened out ;)


    I just skimmed the thread as it seemed Ger had you on the right track but something I'll just throw out there that might help you -


    in my config file, I structure it as:


    01:X
    02:X
    03:X


    etc,


    where X is a number usually 1 or 0 (indicating whether the option is on or off) or 2-9 if multiple options can be chosen for the setting (multiple color schemes each tied to a unique number). To change a setting, I just test my project to see what the setting is currently set to, and if it needs to be changed, I know the exact text I have to substitute in the txt file. (since the substitution macro I posted requires you to know the exact text to change)


    I'm fairly new to working with outside files and I know this isnt the best or most effecient way by far, but I dont know of a way to read only a specific line. Anyone know if its possible?

    Re: Text Stream


    by the way, if you're trying to edit an existing text file, this might be useful:


    Re: Text Stream


    to create/edit a txt file:



    to get text from a text file: (this will import every line of the text file into a new cell in column A)


    Re: Chart Menu Bar Wont Change To Data Menu Bar


    Ok, if I start the file without macros enabled it runs correctly, if I start the file WITH macros it starts correctly but then something in activeworkbook.open is causing the chart menu to appear, specifically something here:



    I run the sub 'turn off' above, which runs both of the above subs, and then when I run turn_on, everything reappears, but with the chart menu in place of the data menu. It still does it with the 9 controls commented out (like below)


    Hey guys, this one has me stumped.. my worksheet menu bar is displaying the 'Chart' menu no matter what I do. Not only do I have no charts in the workbook, (verified this by: )


    Code
    For Each ws In Worksheets
    
    
    ws.Select
    MsgBox ActiveSheet.ChartObjects.Count
    
    
    Next ws


    but adding worksheets, selecting various parts of a worksheet, creating a chart and deleting it, and everything else I've tried has no effect.


    One interesting thing - the first chart I added (to test if it would 'unstick' itself upon deletion of the new chart) was named 'Chart 2', implying there was a chart1 that existed previously.... although I am fairly certain I never added a chart to this workbook at any point.


    Also, the menu bar is working fine in other workbooks, and changes to chart and back to data like normal.


    Anyone have any ideas?

    Re: Extracting Data


    this isnt the right forum and Im sure it will get moved, but use this:


    in cell c1: =IF(A1="test", A1,"")


    and fill down column c, then paste special values to remove the formula

    Re: Workbook_open


    If it works in another spreadsheet.. I have no idea.


    Try:

    Code
    Sheets(5).Select
    Sheet(5).Select
    Sheets5.Select
    Sheets("namehere").Select


    and see if any of them work. There's a link on ozgrid somewhere about the different ways to refer to specific sheets, I looked really fast but didn't find it. I'm sure someone has it bookmarked and will come along and give you the link soon if you can't find it ;)