Posts by homegrownandy

Re: Uploading incoming data to outlook calendar.

ive looked a bit into this but ill need more time. Thanks for the link mate i appreciate it.

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.

I've just read what you wrote again. My previous post was refefing to site only. Duplicate dates will happen all the time, but thats fine. Same with all other details except site.

cheers.

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.

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

Theres not much to see but the information (or sample data) we are working with is there. This is about average for the number of records. But if you want/need a limit it should never be more than 200.

Thanks

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?

Sub aaa() 
     
    Dim Rnge As Range 
    Dim ws1 As Worksheet 
    Dim ws2 As Worksheet 
    Dim lr As Long 
     
    Dim c1 As Variant 
    Dim c2 As Variant 
    Dim Counter As Long 
     
    c1 = Array(1, 6, 7) 
    c2 = Array(1, 2, 3) 
     
    Set ws1 = ActiveSheet 
    Set ws2 = Worksheets.Add 
     
    For Counter = LBound(c1) To UBound(c1) 
        lr = ws1.Cells(Rows.Count, c1(Counter)).End(xlUp).Row - 2 
        ws1.Cells(3, c1(Counter)).Resize(lr).Copy Destination:=ws2.Cells(2, c2(Counter)) 
    Next 
     
    With ws2 
        .Range("A1").Resize(, UBound(c1) + 2).Value = Array("Subject", "Location", "Start Time", "End Time") 
        .UsedRange.Name = "HomeGrownAndy" 
    End With 


     ActiveWorkbook.SaveAs Filename:= _
        "c:\Issued_Dates.csv", FileFormat:=xlCSV _
        , CreateBackup:=False
End Sub

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

When importing in outlook it asks how to deal with duplicates, so it will mean data already existing in outlook that is the same as what im uploading. Thanks for your help again.

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.

Re: Formatting data for an import into outlook calendar

Thank you for the reply Grimes,

I try to run this and it causes runtime error 1004. could you advise some amendments? I've briefly looked and i think its trying to move too much. I'm sure you know better than me.

Thanks again

Re: Formatting data for an import into outlook calendar

Anyone have any ideas how I can achieve this?

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.

Okay I thought I explained that but I'm just glad I have the code working. Thanks again.

Re: Renaming the active workbook.

That's brilliant, thanks for the help/patience. If you want me to rename just let me know. But it could be just as helpful to see something can't be done as what can.

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.

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:

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.