Set Range in Excel

I've spent numerous hours googleling and searching this forum for a way to send a pick array to a range of cells in excel with no luck so I created my own version of it.

In order to do this you need to add this macro into your excel sheet:


Sub SetRange(ByVal CurrentCell As String, ByVal Data As String)
    ' CurrentCell is the starting cell where the data will be copied to
    ' Data contains multi-valued data to be exploded into an array.
    ' @AM = '^'    => Row delimiter
    ' @VM = ']'    => Column delimiter
    ' @SVM = '\'   => LF delimiter within a cell
    
    Dim col As Integer
    Dim row As Integer
    Dim DataArray() As Variant
    Dim x, y, n, nn, nn2 As Integer
    
    col = Range(CurrentCell).Column
    row = Range(CurrentCell).row
    
    s = Split(Data, "^", -1)
    n = UBound(s, 1)
    nn2 = -1
    For x = 0 To n
        ss = Split(s(x), "]", -1)
        nn = UBound(ss, 1)
        If nn > nn2 Then
            ReDim Preserve DataArray(n, nn)
        End If
        For y = 0 To nn
            DataArray(x, y) = ss(y)
        Next y
        If nn > nn2 Then nn2 = nn
    Next x
      
    Range(Cells(row, col), Cells(row + n, col + nn2)) = DataArray
    Range(Cells(row, col), Cells(row + n, col + nn2)).Replace What:="\", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub


From your BASIC program do:


      CellPos = "A1"   ;* This is the starting position

      * The dynamic array must be converted as follows
      CellData = SWAP(PickArray,@AM,"^")
      CellData = SWAP(CellData,@VM,"]")
      CellData = SWAP(CellData,@SVM,"\")

      SubName = 'SetRange'
      
      CALL ATINVOKEMETHOD(ExcelApplication, 'Run',SubName:@VM:CellPos:@VM:CellData,'', ERR, OPTS)
      IF ERR NE '' THEN CALL ATRESETOBJMGR;RETURN


You can change the @AM, @VM and @SVM replacement characters to suit your needs. If you do, don't forget to change the BASIC code and the excel macro.

This is a lot faster than sending data cell by cell.

I hope this will be useful to someone. If you have a better way of doing this I'd love to hear it.

Enjoy,

Pierre

Love to hear it? Check out NebulaXLite.

Ah, my brother from another mother...

NebulaXLite is free for non-production / developer use. Once it is deployed to an end-user system for production use, there is a one-time cost of $200. All support is free via our forum, many enhancements and all fixes are free. (I'm actually following Pete's model...) At some point a major release (or complimentary product with more extensions for Excel 2007) may be issued with many new features. This new release will be for sale on the same basis, essentially as a new product.

For people who use AccuTerm on a daily basis and they and their end-users are still (after a decade) looking for a solution for "real" spreadsheets - NebulaXLite is not the same as the AccuTerm feature.

• NebulaXLite generates documents on the server, does not require Excel, provides full formatting, and documents can be distributed to anyone without Accuterm. Accuterm can very well be used to deliver NebulaXLite documents to end-users.
• Accuterm uses macros, so the MV developer needs to learn VBA and end-users are subject to macro security issues. NebulaXLite requires nothing but Pick BASIC.
• With Accuterm end-users need Excel to import data into documents. Not with NebulaXLite - the end-user just needs a free Excel reader (or Open Office or a web browser to access Google) and no access to a live MV server.
• Accuterm imports data, that's all. NebulaXLite creates entire workbooks with multiple spreadsheets and full formatting. It can dynamically change styles per-company, per-user, or any other way you wish.

These products are not competitive. They were created for completely different uses, and the pricing was intentionally set so that these products could co-exist in the same environments very affordably.

Coffee is on you next time.

lesley,

I was not aware of the limitation. I've also never sent that much data that way! I do an import to load a lot of data to the sheet, I think it's a lot more efficient.