Help with writing Data (Perhaps by cell lookup?)

Help Activities
, , ,
1

I would like to get my extracted data, that I have in a message box right now into an excel sheet as shown below.

Current Excel data

image
image1845×512 14.6 KB

Extracted Month
image

Extracted Distance
image

Expected outcome

image
image1870×173 10.1 KB

Basically would there be a way for me to look up the month on my excel and write the distance, as I will be assuming that there would be other months as well. (For example if I had one result that said December 2022, it would input it into the December column in excel.)

2

Hi @HENG_JUN_YING_AARON

Try this:
inputDate = “04/2023”
dateParts = inputDate.Split(‘/’)
month = int.Parse(dateParts[0])
year = int.Parse(dateParts[1])
abbreviatedMonth = new DateTime(year, month, 1).ToString(“MMM”)
formattedYear = (year % 100).ToString(“D2”)
outputDate = string.Format(“{0} '{1}”,abbreviatedMonth,formattedYear)

3

hey @HENG_JUN_YING_AARON

Is this what you’re trying to achieve?

Main.xaml (8.8 KB)
Output.xlsx (8.2 KB)

The above is just a test workflow. It makes use of the ‘LookUp Range’ Activity inorder to find the cell name based on the header.

image

Here we extract the month from the input and then use string operations to create the header, in this case it would be Apr’23
Then we use the LookUp range activity to find the cell number and after we do so we can add the value(eg:14.00) into that cell. I have assigned an index to indicate the cell number but if you use a for each loop you can increment this index

1 Like
4

Yes, this is what I want to take place. However, the date I extracted is actually in a variable called month (as shown below) and the 14 is actually another variable as well, because I am trying to loop this process.
image

I tried to replace input date you have with the month variable I have
image

but I get the error of Cannot assign from type ‘System.Object’ to type ‘System.String’ in Assign activity ‘Assign’.

5

@HENG_JUN_YING_AARON Check the type of the variables you have used. Make sure it’s of type string.

6

Yup it was all in string, I tried removing the .Tostring after the (Month.Trim)
image

and now I get a new error
image

Do I need to change my month variable?

7

@HENG_JUN_YING_AARON
Try using
CDate(Month.Trim).ToString(“MMMM”).Substring(0,3)+“'”+Month.ToString.Substring(Month.Length-2)

8

Thank you for your reply Brian, however I now get a new error that reads Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

9

@HENG_JUN_YING_AARON Try retyping the quotes in the expression

11

Hi Brian, thank you so much for guiding me through this, I am just wondering what the index= 2 meant in your workflow.

Cheers

12

Hey @HENG_JUN_YING_AARON ,

Glad I could help. The purpose of the index was to ensure that while writing into the cell in the Excel, it writes from A2,B2,C2…and so on. This index will be incremented in order to write it to the next cells i.e. A3,B3,C3 and so on

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.