Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
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.