Posts by homegrownandy

    Re: Uploading incoming data to outlook calendar.


    I'm amazed at what it does and how quick you have put this together. I also like the extra functionalty of catogaizing things.. I will use this so i can see all new dates. It's brilliant. One small thing I didnt think of.


    Is it easy to make these into "all day events". I didnt realise if they are not it shows a time before the name, and you cant drag the event to cover 2 days (you can sett he end time a day later though). If you could also apply this to a file in a folder.. or any files there that would be great too.


    Anyways, Thats amazing.


    Andy.

    Re: Uploading incoming data to outlook calendar.


    There wont be duplicate items within the document. But items may appear on more than one document so they will already be in place on the calendar. I just want these to be ignored. Otherwise there will be multiple versions of the same jobs/or if overwritten data may be lost.


    Cheers.

    Re: Uploading incoming data to outlook calendar.


    I didnt even think of doing it that way, making code to make each apointment. I can further add to confusion here and explain why we needed end date as a column before.


    some "sites" are 2 day install so second days for these are included in the email sent with this document. not on the document itself.


    For these sites i was just adding the second day as the end date. That worked quite well..


    But with this now if it works, I'm happy to go in after and just drag the few (usually less than 5) on the calendar. I'm assuming I would have to put them in manually to the document anyway so it wouldnt be any 'extra' work. Just thinking out loud there, or whatever the equivelent is for typing. haha.

    Re: Uploading incoming data to outlook calendar.


    We just need the dates on the calendar, What I have seen in posts/videos says it needs to be CSV for import. If thats wrong im sorry for telling you wrong information. But if you advise otherwise that would be great. If a sample docuemnt would help I can make one with no sensative data. Look forward to your reply.


    Cheers.

    Re: Formatting data for an import into outlook calendar


    Another thing mate, Just noticed this after a while troubleshooting.


    The formatting of the copied dates changes to american. I dont know why. So where we had a date for 10/02/16 its now 02/10/16 moving that job from febuary to october. Any advice on this one pal?


    The above code formats and saves the data how it needs to be to be imported to outlook. To make it even faster I was hoping someone could help automate the uploading side of it. I'll be logged into the outlook account that has the calendar on so that wont be an issue. I have tested this code and it works well.


    Also If this code could be applied to all files in one folder that would be great. (or all xlsx) then I can just throw files into there as they come in and hit run. Obviously I'll move the files after.


    When uploading outlook asks what to do with duplicates. I dont want duplicates on there if possible.


    Thank you in advance. :)

    Re: Formatting data for an import into outlook calendar


    Thanks a lot, this works perfectly. As you said, I didnt think there was that much either. Also you mentioned importing to outlook with code. At this stage the code you provided is brilliant. But do you think its possible to have it upload and ignore duplicates?


    It would need to be saved as CSV I believe. I can make a new thread if this is possible, but not simple. Thanks again.

    Back again with a new request!


    I was wondering if someone could help me format a spreadsheet we recieve weekly so i can just click import in outlook to see whats upcoming. It will contain a lot of data which isnt needed for this, which can be delted. I'll write what i would like below and hopefully someone can help.

    *delete rows A+B they contain merged cells and titles that wont be of any use for this.
    *delete columns except A/F/G. Colums go up to AZ but we only need these three.
    *we then need to name the three remaining colums by inserting a line in row 1. A1 should be named "subject". B1 = "location" C1= "start time" and D1 = "end time" (D1 will be empty i know but its incase we wish to manually add an end.)


    *After this is done, the range needs to be named... using the define name command on formulas. the name doesnt matter its just so it can be imported, but the range will be.... !$A$1:$D$300


    Any questions just ask, hopefully someone can help.


    Thanks!

    Re: Renaming the active workbook.


    Quote

    I wish to add this as part of a loop that goes through a series of documents and produces test plans and renames them. The renaming part is the only bit I don't have working.


    In which case it would be the active workbook. But as you said its not possible to actually do that. It wont work as I imagined earlier. It would have to be its own procedure not part of the loop which edits the documents. I can rename if you would like but it does relate to what I wanted. I was just couldn't see how it would work earlier.


    Edit: that's the fastest editing I've seen I went to change it to quote myself haha.

    Re: Renaming the active workbook.


    Sorry.... I think my post title may have confused things. When this is included in my other code which does open the workbook then perform an action; that will then be the active workbook. I'm really confused.


    That being said. The test document is located within the same directory. This was not renamed with the code.

    Re: Renaming the active workbook.


    I see how the \ is needed now. Previously I was just copying file location but obviously its needed to separate the folder and file name. Your suggestion to add the name at the start is fine.


    Code
    Sub rename()
        While strFile <> vbNullString
            Name "C:\Users\Admin\Documents\test\New folder\" & strFile As "C:\Users\Admin\Documents\test\New folder\" & "Test_Plan " & strFile
            strFile = Dir
        Wend
    End Sub


    this is getting no errors, but also the files are not being renamed, have I missed something?
    perhaps defining the file type?


    Thanks for the support so far.

    Re: Renaming the active workbook.


    I thought it should be simple like this; maybe my search wasn't good enough haha.


    I call it simple; but I still don't understand sorry. Here is what I have put:


    Code
    Sub rename()
    While strFile <> vbNullString
         '// As an example, but you will need to account for the file extension too ;)
        Name "C:\Users\Admin\Documents\test\New folder" & strFile As "C:\Users\Admin\Documents\test\New folder" & strFile & " Test_Plan"
        strFile = Dir
    Wend
    End Sub


    they are all xlsx files, you say I need to account for that. I'm not sure how to do that. Is it possible you could put this together for me?


    To be honest I struggle with VBA.