Posts by Jstrogue

    Re: Download and delete files from FTP


    Yes, and it didn't work at first because of a stupid reason (connection was already open) :P
    I know how to make it all work now, thank you for all the help S O. Now it's all about ftp :)

    Re: Download and delete files from FTP


    I have tried to login to the ftp with .bat but I have a problem. I have tried everything to auto login.


    40.40.40.40
    ftp
    ftp
    ________________
    40.40.40.40 ftp, ftp
    ________________


    and many other combinations, but it only connects to the ftp, then asks me for username and password. How can I do it all at once?

    Re: Download and delete files from FTP


    Yes there will be multiple lines. I'll have a look at it and tell you how it went. I have to get data from hundreds of files, so i hope this solution will be able to deal with it without too many problems.

    Re: Download and delete files from FTP


    I've tried all the solutions i've found on the internet but nothing seemed to work.
    I would prefer using cmd. Username and password are "ftp".
    The ips are static, but not always online. But I already know how to check that using VBA.

    Hey everyone! I am looking for some help with my latest project.


    I have to get a list of all files on a ftp and then download all older than 2 weeks, do something with the data and then delete them from ftp. I was trying to use cmd but failed. Can someone please explain the best way to do it and maybe post an example?


    Thx sooo much in advance.

    Hey guys I need some help calculating the work times. The main problem is that most of the workers work at more operations at a time. In this case someone started working at 6:03 for 2 operations, and ended first at 12:11 and the other at 12:12. If I simply calculate the time difference, it tells me that he worked 369 + 368 minutes, which is incorrect. He worked 369 minutes in that interval. The last row from the table also fits in this interval, so it shouldnt count to total work time. The 4th row (third row with data) fits in no already accounted time inverval, so it counts towards total work time. So in this case the worker actually worked 369 + 116 minutes. Can you help me write the code to calculate such work times? Start date and end date can also be on different days.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    From

    [/td]


    [td]

    To

    [/td]


    [td]

    Duration

    [/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 6:03

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 12:12

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 86"]

    [tr]


    [TD="class: xl65, width: 86, align: right"]369

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100"]26.5.2014 6:03

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 12:11

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 86"]

    [tr]


    [TD="class: xl65, width: 86, align: right"]368

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 14:05

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 16:01

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 86"]

    [tr]


    [TD="class: xl65, width: 86, align: right"]116

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 8:26

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 100"]

    [tr]


    [TD="class: xl65, width: 100, align: right"]26.5.2014 9:57

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 86"]

    [tr]


    [TD="class: xl65, align: right"]91

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]


    I will be really happy if anyone can help. If you need more data I will gladly attach it or answer any questions.

    Re: Calculate average amount of items made in night shift and day shift


    I just found a better solution for the problem. It's still an idea of how to make it work, but I have to program it again from the beginning. Sadly I don't have much time for this till the middle of the next week because of exams, so please understand that it's not that I don't care about this, but I just haven't had time to do it yet.

    Re: Calculate average amount of items made in night shift and day shift


    Oh sorry, not sure why it didnt show up on mobile... So I missed it. You're on the right path. I also managed to calculate the time difference, after that I need to calculate the time for each period of work. It'll be easiest if I explain on an example.


    Lets say that someone started at 5:00 and worked till 15:00.
    Night shift: 22:00 - 06:00
    Morning shift: 6:00 - 14:00
    Aftrenoon shift : 14:00 - 22:00


    I want the results to look like this:
    [TABLE="width: 500"]

    [tr]


    [td]

    Start

    [/td]


    [td]

    End

    [/td]


    [td]

    Morning

    [/td]


    [td]

    Afternoon

    [/td]


    [td]

    Night

    [/td]


    [/tr]


    [tr]


    [td]

    1.1.2013 5:00

    [/td]


    [td]

    1.1.2013 15:00

    [/td]


    [td]

    60

    [/td]


    [td]

    480

    [/td]


    [td]

    60

    [/td]


    [/tr]


    [/TABLE]


    So let me explain the wanted results. The start and end are on the same day, so I dont need to be carefull when calculating the night shift. Since only one hour was made during the night and afternoon shift, I wrote 60 (minutes) in the cell. And since all 8 work hours have been made in morning shift, I calculated that 8*60 =480 and wrote that into the "afternoon cell". This is one of the easy examples, but I have more difficulties with those when start and end are not on the same day. I am working on some code already, but it still doesn't work correctly.


    Not sure if we're allowed to go offtopic here, but I really suggest you to come visit Slovenia. I've been in many countries so far, but still havent been tempted to move. I love this place. If you decide to come here, PM me on forum and I'll suggest you which places to visit and so on :)

    Re: Calculate average amount of items made in night shift and day shift


    Hey sorry for the late answer. I kinda thought everyone gave up on this post. Lets say a worker starts working at 05:00 and ends at 15:00. This means he worked 1 hour in night shift (from 22:00 to 6:00), 8 hours in morning shift (from 6:00 to 14:00) and 1 hour in afternoon shift (from 14:00 to 22:00). I want the amount of hours in each period. Or even better minutes. If I get these timers, I can calculate the amount of items per hour in each shift and so on.

    Hey everyone!


    I have a simple table, which contains 3 columns that I want to use. In column A, I have the start date of an operation. In column B is the end date. In column C is the amount of items created. Dates in columns A and B are not necessary on the same day. So What I want to do is figure out how much time did the worker spend during the day shifts and night shifts.


    Lets say that he starts working at 16:00 and works till midnight. That totalls 8 hours. In this time he creates 8000 items. Night shift starts at 22:00 and lasts till 6:00. So I want the program to be able to tell me, that this worker spent 2 hours during the night shift and 6 during the day shift. Date format is "13.1.2013 13:12:00". I will be really happy if anyone can help me solve this problem, because I haven't found a solution for it so far.

    Re: Calculate timedifference for each row


    Nevermind, I have been able to figure it out... It's always like this with me... I try to solve it and it doesn't work, but find the solution in 5min since posting on the forum xD. Thx anyways.

    Hey everyone! I would like to calculate time difference between start and end event for each row in database. I just cant figure out how to do it. I will post a dummy query below. Later I will want to sum all the duration for events for lets say a whole month or year, but I should be able to figure that out by myself. Thx so much for all your help.


    SQL
    SELECT MinDate, MaksDate, DateDiff([day],MinDate, MaksDate) AS Dt_diff
    FROM [Database].[table]

    Re: Sort data by 3 columns


    Awesome! That solved it! I never would have thought of that. Now i'll just fix all those "dates" and my macro will finally work. Thank you again for your help holycow!

    forum.ozgrid.com/index.php?attachment/54893/


    Hey everyone! I am trying to sort some data based on values from 3 columns. No matter what I do, the program does not correctly calculate the time difference and sort based on column B.


    Code
    Sheets("Sheet1").Range("A1:J27").Sort Key1:=Range("F1"), Order1:=xlAscending, Key2:=Range("H1" _
            ), Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header _
            :=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
            xlSortTextAsNumbers


    As you can see from my code, I am first sorting based on values from column "F", then "H" and "B" after those two. Sorting works perfectly for first 2 columns, but as you can see in rows 9:12, the values are not properly sorted based on date. I copied those dates from different worksheets, and I'm almost sure that this is the problem. Can anyone help me fix it? Thank you sooo much in advance.


    I noticed that some dates are actually saved as decimals, but still can't figure out how to work around that.

    Re: How to generate 3 random numbers in one step in excel


    You did not post the information of where do you want to save this value, so you will have to edit the last part of the code a bit. I'm kinda sure the code I wrote for you does what you asked for. But I have to remind you, that you will have to set the correct data type for the cell, because otherwise, you will get weird numbers (excel does not really do great with multiple commas). You can set the data type to string, or tell me where do you want to save these values, and i'll add a line of code that will do it for you.


    Re: VBA learning methods


    Heh cytop thx for correction :D I forgot to mention the range for the data types :D Types with smaller range use less memory, but will sometimes not be big enough. For example excel 2003 allows about 65k rows, and integer can only use 32k.