Good afternoon.
I have a single Row A1:TT1 with data I have imported from an API output.
The data looks similar to this and I import it using a comma delimiter
[{"instructorEmail":"jby@xyz.com","instructorUserId":"123","timeZoneName":"(UTC+01:00) Brussels, Copenhagen, Madrid, Paris","startDate":"10/01/2018 9:00 AM","startDateUtcOffsetMinutes":120,"endDate":"10/02/2018 6:00 PM","endDateUtcOffsetMinutes":120,"status":"has not started","name":"11 FR, Paris Time October 1-2, 2018","shortId":"ygg2","studentLoginUrl":"https://xyz.com/Class/ygg2","id":"abc"},
{"instructorEmail":"sc@xyz.com","instructorUserId":"456","timeZoneName":"(UTC-05:00) Eastern Time (US & Canada)","startDate":"10/04/2018 6:00 AM","startDateUtcOffsetMinutes":-240,"endDate":"10/05/2018 8:00 PM","endDateUtcOffsetMinutes":-240,"status":"has not started","name":"Class Y October 4-5, 2018","shortId":"w56t0","studentLoginUrl":"https://xyz/Class/w56t0","id":"def"},]
I am tying to write a method which splits each course onto a seperate Row in Excel by searching for the like {*instructorEmail and moving the data until it finds the next {*instructorEmail to the next available Row Down in a loop
My end game is have a way where the instructor will select his course from the list and the macro will move his fields across to another worksheet where the output will be used for creating an email to the students. I.e class A starts on day B and the url to join the class is C but as I am unable to crawl I don't think I am ready to start this marathon just yet.
My very basic code so far. Sorry I am a real Excel VBA baby
Sub CommandButton1_Click()
Dim itemNumber As String
Dim itemAmount As Single
Dim myData As Workbook
Dim Wk As Worksheet
Dim Cel As Range
Set myData = Workbooks.Open("C:\Users\xyz\Desktop\classes.csv")
Set Wk = myData.Worksheets("classes")
For Each Cel In Wk.Range("A1:TT1")
'For Each Cel In Wk.Rows(1)
'{"instructorEmail":"me@me.co.uk"
If Cel.Value Like "*instructorEmail*" Then
'Cel.Value = Cel.Address
'MsgBox (Cel.Value)
' get next blank cell in row
Wk.Cells(Cel.Row, Wk.Columns.Count).End(xlUp).Offset(, 1).Value = Cel.Value
End If
Next
myData.Close True 'save & close
End Sub
I have a single Row A1:TT1 with data I have imported from an API output.
The data looks similar to this and I import it using a comma delimiter
[{"instructorEmail":"jby@xyz.com","instructorUserId":"123","timeZoneName":"(UTC+01:00) Brussels, Copenhagen, Madrid, Paris","startDate":"10/01/2018 9:00 AM","startDateUtcOffsetMinutes":120,"endDate":"10/02/2018 6:00 PM","endDateUtcOffsetMinutes":120,"status":"has not started","name":"11 FR, Paris Time October 1-2, 2018","shortId":"ygg2","studentLoginUrl":"https://xyz.com/Class/ygg2","id":"abc"},
{"instructorEmail":"sc@xyz.com","instructorUserId":"456","timeZoneName":"(UTC-05:00) Eastern Time (US & Canada)","startDate":"10/04/2018 6:00 AM","startDateUtcOffsetMinutes":-240,"endDate":"10/05/2018 8:00 PM","endDateUtcOffsetMinutes":-240,"status":"has not started","name":"Class Y October 4-5, 2018","shortId":"w56t0","studentLoginUrl":"https://xyz/Class/w56t0","id":"def"},]
I am tying to write a method which splits each course onto a seperate Row in Excel by searching for the like {*instructorEmail and moving the data until it finds the next {*instructorEmail to the next available Row Down in a loop
My end game is have a way where the instructor will select his course from the list and the macro will move his fields across to another worksheet where the output will be used for creating an email to the students. I.e class A starts on day B and the url to join the class is C but as I am unable to crawl I don't think I am ready to start this marathon just yet.
My very basic code so far. Sorry I am a real Excel VBA baby
Sub CommandButton1_Click()
Dim itemNumber As String
Dim itemAmount As Single
Dim myData As Workbook
Dim Wk As Worksheet
Dim Cel As Range
Set myData = Workbooks.Open("C:\Users\xyz\Desktop\classes.csv")
Set Wk = myData.Worksheets("classes")
For Each Cel In Wk.Range("A1:TT1")
'For Each Cel In Wk.Rows(1)
'{"instructorEmail":"me@me.co.uk"
If Cel.Value Like "*instructorEmail*" Then
'Cel.Value = Cel.Address
'MsgBox (Cel.Value)
' get next blank cell in row
Wk.Cells(Cel.Row, Wk.Columns.Count).End(xlUp).Offset(, 1).Value = Cel.Value
End If
Next
myData.Close True 'save & close
End Sub
