VBA to .NET ?

LuzBenitez — Thu, 08 Oct 2020 23:42:00 GMT

so Im working in a CSV File that have some columns with more than 15 number characters, and dates, and Strings
I use the import CSV action and then I want To format the current sheet, so I Try to make a code Stage to "Format" Those columns, I have wrote the code on VBA it works with a macro, but I dont wan to to use macro along with blue prism
theres my code

Sub Macro1()
Dim C As Integer
For i = 1 To ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells(3, i).Select
If IsNumeric(Selection) Then
Columns(i).NumberFormat = "###,###,###,###.###,##0.00"
ElseIf WorksheetFunction.IsText(Selection) Then
Columns(i).NumberFormat = "@"
ElseIf IsDate(Selection) Then
Columns(i).NumberFormat = "yyyy-MM-dd hh:mm:ss.0"
End If
Next

End Sub

How can I properly "translate" that code into blue prism?
I keep getting erros like Columns needs to be declared, etc
I'll appreciate some tips or help
Thanks!!!

------------------------------
Luz Benitez
------------------------------

RE: VBA to .NET ?

LuzBenitez — Fri, 09 Oct 2020 02:40:00 GMT

I'm kind of getting there by modifying the code Stage of Import CSV , it does the job for the numbers, but still not what i need
I'm trying to combine this code with the code above, but still getting errors bc i suck at vb.net
Is there a way to get the type of cell just before the import?

Dim dw, ds, dr, qt As Object
Dim formats() As Long, i As Long

ReDim formats(0 To 100)
For i = 0 To 100
formats(i) = 2
Next i
Try

dw = GetWorkbook(Handle, Workbook)
ds = GetWorksheet(Handle, Workbook, Worksheet)
dr = ds.Range(Range)
qt = ds.QueryTables.Add(Connection:="TEXT;" & Path, Destination:=dr)

With qt
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = 1 'xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = 1 'xlDelimited
.TextFileTextQualifier = Qualifier
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = formats
.TextFileTrailingMinusNumbers = True

End With
qt.Refresh(False)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
dw = Nothing
ds = Nothing
dr = Nothing
qt = Nothing
End Try

------------------------------
Luz Benitez
------------------------------

topic RE: VBA to .NET ? in University Forum

VBA to .NET ?

RE: VBA to .NET ?