topic Re: Macro for replacing current data in excel in QlikView

Macro for replacing current data in excel

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to Qlik/macros, any help is appreciated.

I'm looking to export my tables from Qlikview and paste into already created excel files where the current table will be overwritten with the new table.

Thanks,

Re: Macro for replacing current data in excel

MK9885 — Fri, 23 Dec 2016 16:21:01 GMT

Maybe below link?

Qlikview to excel export macro

Of course you need to edit it as per your requirement, CH number or destination folder etc...

Re: Macro for replacing current data in excel

el_aprendiz111 — Fri, 23 Dec 2016 17:10:45 GMT

Hi,

1 Example:

Sub DeleteFiles
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("C:\Folder\tmp.xlsx") then
filesys.DeleteFile "C:\Folder\tmp.xlsx"
Msgbox("Files Deleted")
Else
Msgbox("No Files to delete")
End If
End Sub

sub Export_Excel
set oXL=CreateObject("Excel.Application")

oXL.Workbooks.Add
oXL.visible=True
aSheetObj=Array("CH08","CH09")
for i=0 to UBound(aSheetObj)
oXL.Sheets.Add
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing

oSH.Name=left(sCaption,30)
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select

next
call DeleteFiles
oSH.SaveAs "C:\Folder\tmp.xlsx",51

set oSH=Nothing
set oXL=Nothing
end sub

Re: Macro for replacing current data in excel

joseduque — Fri, 23 Dec 2016 17:21:14 GMT

Hi,

So you need to updated it like a report? Where You have already an excel with data but you need to update just the graphic? Cause I recommend you to make the report from Qlikview:

You can Create your sheet with all the data and graphics you need, and update every month

Hope This helps