Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
amberrebma
New Contributor II

macro export to excel

Hi

i try this for updating excel file but this is not working

Sub Test1

   SET objVariable = ActiveDocument.Variables("vpath")              

   strExcelFile = objVariable.GetContent.String

   ExcelAppend strExcelFile, "CH09", 1 

End Sub 


' Sub to append data to an excel file

Sub ExcelAppend(strExcelAppendFile, strExelAppendObjectID, intExelAppendHeader) 

   ' Create an instance of Excel 

   SET objExcelApp = CREATEOBJECT("Excel.Application") 

    

   ' Open workbook 

   WITH objExcelApp 

      .DefaultSaveFormat = xlWorkbookNormal 

      .DisplayAlerts = FALSE 

      .Workbooks.Open strExcelAppendFile 

      .DisplayFullScreen = FALSE 

      .Visible = FALSE 

   END WITH 


   ' Set worksheet 

   SET objExcelSheet = objExcelApp.Worksheets(1) 

       

   ' Set Excel used range 

   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 

   ' Last used row in column A 

   intExcelLastRow = objExcelRange.Row 

   ' Set object to append from 

   SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)    

   ' Loop all rows of the object, if intExelAppendHeader is set to 1 the header will export too

ON ERROR RESUME NEXT '

   FOR intObjectRow = 1 - intExelAppendHeader To objObjectFrom.GetRowCount - 1 

MSGBOX ERR.DESCRIPTION

      ' Loop all columns of the object 

      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1

         ' Get object data 

         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 

         ' Add that data to Excel cell 

         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 

      NEXT 

   NEXT 

  

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppendFile    

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

END SUB


where is the problem

5 Replies

Re: macro export to excel

Hi Amber,

The above code is working fine. Have a look at the attached file.

amberrebma
New Contributor II

Re: macro export to excel

how ? when i click on append nothing happens .. and problem is when table is updated with new record then want to updated excel file instead of creating file

Re: macro export to excel

Make sure you have change the current local security to "System access" in the macro window.

Capture.PNG

amberrebma
New Contributor II

Re: macro export to excel

i did that but not working

Re: macro export to excel

Did you change the variable path? Try changing the excel path and try again. It is working fine for me.

Capture.PNG