I’m facing this problem:
I have a loop and i need to write some values in an excel sheet. The problem is that i need to skip one excel column between iterations. For example:
Loop Iteration(1):
I write the cells A4, B4 and C4
Loop Iteration(2)
I write the cells E4,F4,G4 (it skips G4)
Is there any function/activity to do it? I’ve thought about creating an auxiliar sheet to map the letters of the columns to numbers in order to use a counter (A-1, B-2) but I would like to avoid this solution.
The data comes from a web app but not in a data table format.
I imagine I can use a “Read Range” activity in every loop, work the data table adding columns and finally write it again in the excel.
Is there any way to relate with a function the column with a counter?
So how are you getting your data into the excel application scope? Is it a get text activity? What activities are you using to get the data from the web-app
You could use a Write Range Activity to write a datatable in excel that has empty column where you don’t want to write anything. Depending on how you get the data you can use a Build Datatable Activity to set up the format. Also you should uncheck Include Headers Property to not write the Column Headers.
There are several ways to do this. One very easy one if you know there are no carriage returns inside your cells would be to convert the datatable to a string, then process the string and parse back into a datatable before writing to Excel.
You would begin by using the “Output Datatable” activity on your datatable. This will give you a string that looks something like
DTString = “A1val,B1val,C1valcarriage returnA2val,B2val,C2val…” and so on.
Then you can use use an assign activity to compute
newDTString = DTString.Replace(vbCr, “, ,”)
so that newDTString will look like “A1val,B1val,C1val, ,A2val,B2val,C2val…” and so on.
Then use the “Generate Datatable” activity with CSV parsing to convert newDTString back to a datatable and just write the new datatable using the “Write Range” activity. Alternatively, you could just write newDTString to a CSV directly, depending on what else you need to do to the file.
EDIT: had to correct formatting, the text editor on here does not support double commas
I’m trying to put something together for you, but I’m not sure how you are getting the data, and what data type variable it’s stored in. Could you possibly give us all a screen shot of what you are doing so far?
I pulled up a get Text activity But I’m wondering how you are using it. Are you Getting text from the whole page? Or is your get Text activity in a loop of some sort?