macro export to excel

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

' Save and quit

objExcelSheet.SaveAs strExcelAppendFile

objExcelApp.Application.Quit

SET objExcelSheet = NOTHING

SET objExcelApp = NOTHING

END SUB

where is the problem

Re: macro export to excel

tamilarasu — Tue, 06 Mar 2018 09:53:59 GMT

Hi Amber,

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

Re: macro export to excel

Anonymous — Tue, 06 Mar 2018 12:05:20 GMT

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

tamilarasu — Wed, 07 Mar 2018 02:35:08 GMT

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

Re: macro export to excel

Anonymous — Wed, 07 Mar 2018 06:08:21 GMT

i did that but not working

Re: macro export to excel

tamilarasu — Wed, 07 Mar 2018 09:06:43 GMT

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

topic Re: macro export to excel in QlikView

macro export to excel

Re: macro export to excel

Re: macro export to excel

Re: macro export to excel

Re: macro export to excel

Re: macro export to excel