Excel VBA to create inline table for Load Script

johnnymartinez — Thu, 03 Aug 2017 07:05:37 GMT

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

Re: Excel VBA to create inline table for Load Script

tomasz_tru — Thu, 03 Aug 2017 08:03:41 GMT

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');

Re: Excel VBA to create inline table for Load Script

johnnymartinez — Thu, 03 Aug 2017 08:11:35 GMT

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.

topic Re: Excel VBA to create inline table for Load Script in QlikView

Excel VBA to create inline table for Load Script

Re: Excel VBA to create inline table for Load Script

Re: Excel VBA to create inline table for Load Script