Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

johnnymartinez
New Contributor III

Excel VBA to create inline table for Load Script

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


2 Replies
tomasz_tru
Valued Contributor

Re: Excel VBA to create inline table for Load Script

Thanks! I will remove table name inputbox and set default table name to maje it faster.

BTW. If you copy text from different sources, you can always change delimeter for INLINE LOAD:

load * inline [

...

](delimiter is '\t');

johnnymartinez
New Contributor III

Re: Excel VBA to create inline table for Load Script

Cool - thanks , didn't know that!

Am working on moving all of our reporting from VBA / SQL via Excel to Qlik - and just put this together to stick in all the cross references I'd previously had as functions.  Hopefully it saves a few people 5 minutes.

p5.png

Community Browser