2 Replies Latest reply: Aug 3, 2017 4:11 AM by John Martin RSS

    Excel VBA to create inline table for Load Script

    John Martin

      This turns a rectangular cell selection in excel into a pre-formatted inline table with the correct syntax to paste in the clipboard into a load script.

       

      It's useful for small lookup/reference tables and or testing - hopefully this helps someone!

       

      Sub MakeQlikInline()

       

      Dim MRang As Range, MCell As Range, TabName As String, LoadSt As String, ColCnt, RowCnt, CellCnt As Integer

       

       

      Set MRang = Selection

       

      CellCnt = 1

      ColCnt = 0

      RowCnt = 1

       

      LoadSt = InputBox("Name the table (" & MRang.Columns.Count & " COLUMNS )", "Name your Table", "MyTable")

      TabName = LoadSt

      LoadSt = LoadSt & ": " & Chr(13) & "Load * Inline [" & Chr(13)

       

       

      For Each MCell In MRang

       

      If CellCnt < MRang.Cells.Count + 1 Then

       

      If RowCnt = 1 Then LoadSt = LoadSt & "'"

       

      LoadSt = LoadSt + MCell.text

       

      If RowCnt = 1 Then LoadSt = LoadSt & "'"

      End If

       

      'Move cursor

      CellCnt = CellCnt + 1

      ColCnt = ColCnt + 1

       

       

      If RowCnt = 1 And ColCnt = MRang.Columns.Count Then LoadSt = LoadSt & " ,'" & TabName & "'_ID'"

      If RowCnt > 1 And ColCnt = MRang.Columns.Count Then LoadSt = LoadSt & " ," & RowCnt & "-" & CellCnt

       

       

      ' add comma

      If CellCnt < MRang.Cells.Count Then LoadSt = LoadSt & ","

       

       

      ' Next line ?

      If ColCnt = MRang.Columns.Count Then

      ColCnt = 0

      RowCnt = RowCnt + 1

      LoadSt = LoadSt & Chr(13)

      End If

       

      Next MCell

       

      'LoadSt = Left(LoadSt, Len(LoadSt) - 1)

      LoadSt = LoadSt & Chr(13)

      LoadSt = LoadSt & "];"

       

      Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

      MSForms_DataObject.SetText LoadSt

      MSForms_DataObject.PutInClipboard

      Set MSForms_DataObject = Nothing

      End Sub



      p1.png

      p2.png

      p3.png

      p3.png


      p4.png