Writing Values Only into a Datatable

Help Activities
, ,
1

Hello,

I am reading a datatable from one workbook and writing it to another tab in another workbook. I’d like to keep values only from the original workbook. Am I able to do this with datatables? We need to be able to keep the N/As. Right now it returns numbers. Any help would be greatly appreciated.

image

2

Hi @jenn.cruz

Did you try by checking the preserve format check box in read range activity?

PreserveFormat - option reads the values as they are displayed in the range (e.g. currency, date, text). The actual number formatting is not retained, only the way in which data is displayed.

Hope it helps! Cheers!

3

Thank you. Checking preserve format makes the automation run extremely slow and doesn’t progress. Are there any other options?

4

Hi @jenn.cruz ,

You can achieve this with Invoke VBA

  1. Use Excel application scope activity to open source file.
  2. Inside Excel application scope use Invoke VBA activity to invoke the VBA script.
Sub OpenWorkbook() 

	Sheets("Sheet1").Range("A2:D9").Copy
  
	'PasteSpecial to paste values, formulas, formats, etc.
	Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues

End Sub
5

Hi @jenn.cruz

You can also try to copy from the first excel and convert the string in the clipboard to datatable and write range in the second excel.

Please find the solution attached.
TEST1 - Copy.zip (15.8 KB)

Hope it helps!

6

Hello @jenn.cruz

Sequence: Copy DataTable with ‘N/A’

Excel Application Scope: WorkbookPath="[path to original workbook]"
    Excel Read Range: SheetName="[original sheet name]" Range="[range]" DataTable="[dtOriginal]"
End

For Each Row: in="[dtOriginal]"
    For Each: col In="[dtOriginal.Columns]"
        Assign:
            Left: row(col)
            Right: If(IsNumeric(row(col)), "N/A", row(col))
    End For Each
End For Each Row

Excel Application Scope: WorkbookPath="[path to new workbook]"
    Excel Write Range: SheetName="[new sheet name]" Range="[destination range]" 
    DataTable="[dtOriginal]"

End

Thanks & Cheers!!!

7

Would you put the read range activity in for each? Sorry I’m new to UiPath.

image