topic Help needed for Macro alternative for Data Appending in Excel in QlikView

Help needed for Macro alternative for Data Appending in Excel

Anonymous — Fri, 29 Jul 2016 04:57:06 GMT

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.

Re: Help needed for Macro alternative for Data Appending in Excel

vinieme12 — Fri, 29 Jul 2016 08:23:37 GMT

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

Re: Help needed for Macro alternative for Data Appending in Excel

Anonymous — Fri, 29 Jul 2016 08:35:14 GMT

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)

Re: Help needed for Macro alternative for Data Appending in Excel

vinieme12 — Fri, 29 Jul 2016 09:06:35 GMT

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..

Re: Help needed for Macro alternative for Data Appending in Excel

beck_bakytbek — Fri, 29 Jul 2016 09:11:00 GMT

Hi Rana,

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

beck