Help needed for Macro alternative for Data Appending in Excel

[Anonymous]

Hi Team,

I have a Qlikview dashboard in which we have to append User given data to an external existing Excel file. I have Input Box, List box to take User input and a button is created which takes the value present in the form and appends it to an existing Excel file. I am able to achieve this via Run Macro.

I have written a macro as below and this works correctly. However, I have been asked to look for a solution beyond Macro since Macros are not allowed on my Client's machines. Can you kindly suggest solutions. I have read about Extensions but do not know How to implement it in my context. Kindly suggest.

Macro Code(Comments_Kuldeep.xls is the file which I want to append my form Data to, For sampling I populate only ColumnA and H):

Sub AppendDataToExcel 

dim doc, xlApp, xlDoc, xlSheet, LastRow 

Const xlUp = -4162 

set doc = ActiveDocument 

Set xlApp = CreateObject("Excel.Application") 

Set xlDoc = xlApp.Workbooks.Open("C:\Users\admin\Desktop\Aasif_20July\Current\Comments\Comments_Kuldeep.xls")  

xlapp.Visible = false   

Set xlSheet = xlDoc.Worksheets("Sheet1")    

xlSheet.Activate 

LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row + 1

msgbox LastRow 

xlSheet.Cells(LastRow + 1, 1).Select

dim Kr1,Kr2,Kr3

set Kr1 = ActiveDocument.Variables("vCommentsUPC")

set Kr2 = ActiveDocument.Variables("vDept_Comment")

xlSheet.Range("A"&LastRow).value = Kr1.GetContent.String

xlSheet.Range("H"&LastRow).value = Kr2.GetContent.String

xlDoc.Save 

xlDoc.Close 

xlApp.Quit

End Sub

Thanks.

Kuldeep.

[vinieme12]

Why would you use Qlikview to append data to an excel file??

[Anonymous]

Thanks for the response Vineet.

Presently, we have an external system which gets opened from Qlikview using Open URL which does the job of saving the Excel data.

However, we want to get rid of the external system and make everything work in Qlikview. With 'Dynamic Update' feature, I am able to achieve the Data saving in Qlik but a Reload flushes out this saved data since it reads from input excel file and hence saving in the Excel file is pretty much needed.

Kindly suggest if there is some solution. (Partial Reload and Incremental Reload will not help either)

[vinieme12]

You need to enable the end user to be able to refresh the document

if they can , then take the user input during reload

let variable1 = input('enter value for field1','YourCaption');

let variable2 = input('enter value for field2','YourCaption');

TABLEX:

LOAD  *

FROM

filename.CSV(txt)

CONCATENATE

LOAD * INLINE [

fieldname,fieldname2..,.,...,....

Variable1 , variable2...,..,.,

Store TABLEX INTO  fullpath.filename.CSV(txt)

But can guarantee simultaneous updates with this method..

[beck_bakytbek]

Hi Rana,

in this blog:qlikblog.at | QlikView / Qlik Sense Blog by Stefan Walther, you can fine a lot Information to your issue.

beck