topic Re: Advancing A "Excel-Export-Macro" in QlikView

Advancing A "Excel-Export-Macro"

Fri, 14 Oct 2011 11:40:11 GMT

Hello,

I tried out the following "export to excel" macro.

It works fine, even on the QlikView- (web) - Accespoint.

sub exportObject1()

call exportToExcel("CH01", "C:\Temp\Export_Excel1.xls")

end sub

sub exportObject2()

call exportToExcel("CH03", "C:\Temp\Export_Excel2.xls")

end sub




'--------------------------------- Generic Function for use of all objects ----------------------------

' you can now use this function independent of object and path
' just call this function as above by passing the object ID and the desired filePath

sub exportToExcel(objectId, filePath)

dim o
Set o = ActiveDocument.GetSheetObject(objectId)
o.ExportBiff filePath

Set o = nothing


end sub

but in which way must this script be changed, to make it possible to export

"CH01" to "Sheet1" in "C:\Temp\Export_Excel1.xls"

and

"CH02" to "Sheet2" in "C:\Temp\Export_Excel1.xls"

Thanks for any advice in advance.

Greetings

Daniel

Advancing A "Excel-Export-Macro"

vgutkovsky — Sun, 16 Oct 2011 18:06:39 GMT

Daniel,

You can't use the automated "exportbiff" function for this custom functionality. You would need code similar to the following:

Sub ExportExcel

SET XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
SET XLDoc = XLApp.Workbooks.Add
SET XLSheet1 = XLDoc.Worksheets(1)
SET XLSheet2 = XLDoc.Worksheets(2)

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
XLSheet1.Paste XLSheet1.Range("A1")

ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard true
XLSheet2.Paste XLSheet2.Range("A1")

XLApp.Visible = True

End Sub

You can then use VB formatting functions, etc, to make the export look nicer.

Advancing A "Excel-Export-Macro"

Wed, 19 Oct 2011 14:02:01 GMT

Thank You for Your Advice.

Re: Advancing A "Excel-Export-Macro"

Mon, 31 Oct 2011 19:15:23 GMT

Here would be my solution, i'm planning to use it to generate automatic muli-sheet excel report in QV. This is just a demo from a test-file.

I still plan to add a check if the files were deleted by AppShell, so that the reload of the QVW will not get an dialog from excel (overwrite excisting file) and the reload would be paused until someone will press "Yes" overwrite, but I plan to add this in nearby future to the script.

Does someone know how to use VBA's standard "DoCmd.SetWarnings False" with Excel executed via QlikView macro? 😕

Function SendNiceAndEasyStaffToExcel()

' .XLS file names are defined - START

XLSExport = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\Exporttest2.xls"

' Final report file.

XLSExportTMP1 = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\TEMP\Exporttest2CH01.xls"

' First part of the report is exported to this TMP file

XLSExportTMP2 = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\TEMP\Exporttest2CH02.xls"

' Second part of the report is exported to this TMP file

' .XLS file names are defined - END

' A shell app. must be used to remove old .XLS files, because QlikView itself

' can not delete .XLS files (although it can overwrite them), but then there

' is a risk of QV itselt not having Read-Write access and QlikView reload would

' be cancelled.

' A shell app. (Command prompt) that removes old XLS exports - START

RemXLS = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\MoveXLS.bat"

' .bat file bath

Set ShellApp = CreateObject("Shell.Application") ' ShellApp type as "Shell.Application"

ShellApp.Open(RemXLS) ' .bat file is executed

' A shell app. (Command prompt) that removes old XLS exports - END

' Clears all selections from QVW file - START

ActiveDocument.ClearAll

ActiveDocument.ClearAll false

' Clears all selections from QVW file - END

' PAUSE time to compleate the laste ClearAll request (wise to use with filse >= 1GB )

ActiveDocument.getApplication.sleep 5000

' Defines excel so the export file can be accessable in VBA

Set AppExcel = CreateObject ("Excel.Application") 'AppExcel definition

AppExcel.Visible = True 'AppExcel (visible) will be executed

' thereby we can Modify/SaveAs/ReName the export file, because it will also created

' with "Excel.Application" and it is now mapped as AppExcel for us to access..

'Report CH01 export - START

set obj = ActiveDocument.GetSheetObject("CH01") 'Obj is defined & accessed

obj.SendToExcel 'SendToExcel function on Obj

'Report CH01 export - END

AppExcel.ActiveSheet.Name = "Raport CH01" 'Renames the active sheet.

' .XLS file names are defined - START

XLSExport = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\Exporttest2.xls"

XLSExportTMP1 = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\TEMP\Exporttest2CH01.xls"

XLSExportTMP2 = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\TEMP\Exporttest2CH02.xls"

' .XLS file names are defined - END

AppExcel.ActiveSheet.SaveAs (XLSExportTMP1) 'First export is saved as the value of XLSExportTMP1

'NB All the .XLS files must be removed with AppShell first!! QV CAN NOT OVERWRITE EXCISTING .XLS FILES!!!

' There seems to be an error with closing an exported file (no opening is defined)

' so I had to:

' - save it first, to avoid dataloss (previous step). It must be done before

' - define it as a object with an .Open ; Object name is OWB_XLSExportTMP1

' - somehow access/reopen this object (with .Open clause) & I choose to use .Save clause because double .Open is not allowed.

' - Then use the object (with .Open clause) to be comletely closed with .Close

' It might look dumb, but it was the only way to do it. There was no comleate solution even in QV community to successfully complete

' the multi-sheet export and the .XLS file would not remain opened. Does not make much sense as VBA in total, but works

set OWB_XLSExportTMP1 = AppExcel.Workbooks.Open(XLSExportTMP1) 'Object OWB_XLSExportTMP1 (with .Open ) is defined.

OWB_XLSExportTMP1.Save 'Object OWB_XLSExportTMP1 is reopened/accessed via .Save

OWB_XLSExportTMP1.Close 'Object OWB_XLSExportTMP1 is closed via .Close

'First part of the report is exported and closed.

'Report CH02 export - START

set obj = ActiveDocument.GetSheetObject("CH02") 'Obj is defined & accessed

obj.SendToExcel 'SendToExcel function on Obj

'Report CH02 export - END

AppExcel.ActiveSheet.SaveAs (XLSExportTMP2) 'Second export is saved as the value of XLSExportTMP2

'NB All the .XLS files must be removed with AppShell first!! QV CAN NOT OVERWRITE EXCISTING .XLS FILES!!!

' There seems to be an error with closing an exported file (no opening is defined)

' so I had to:

' - save it first, to avoid dataloss (previous step). It must be done before

' - define it as a object with an .Open ; Object name is OWB_XLSExportTMP2

' - somehow access/reopen this object (with .Open clause) & I choose to use .Save clause because double .Open is not allowed.

' - Then use the object (with .Open clause) to be comletely closed with .Close

' It might look dumb, but it was the only way to do it. There was no comleate solution even in QV community to successfully complete

' the multi-sheet export and the .XLS file would not remain opened. Does not make much sense as VBA in total, but works

set OWB_XLSExportTMP2 = AppExcel.Workbooks.Open(XLSExportTMP2) 'Object OWB_XLSExportTMP2 (with .Open ) is defined.

OWB_XLSExportTMP2.Save 'Object OWB_XLSExportTMP2 is reopened/accessed via .Save

'NB object OWB_XLSExportTMP2 will be closed in the end of this macro, I just had to reopen it to be able to close it after a also access

'other .XLS files via App.Excel

AppExcel.ActiveSheet.Columns("A:AZ").Copy 'Table from second Export is copied into Clipboard

' First export file is opened, a sheet is added & data from second export is added. Then it is saved as the final report. - START

AppExcel.Workbooks.Open(XLSExportTMP1) ' Opens the first exported .XLS file

AppExcel.Sheets.Add() ' Sheet is added to the first export

AppExcel.ActiveSheet.Cells(1,1).Activate ' Cell A1 is selected on the new sheet

AppExcel.ActiveSheet.Paste ' Data from second export is pasted into the new sheet

AppExcel.ActiveSheet.Name = "Raport CH02" ' New sheet is renamed.

AppExcel.ActiveSheet.SaveAs (XLSExport) ' This result is saved as the value of XLSExport (final report)

'NB All the .XLS files must be removed with AppShell first!! QV CAN NOT OVERWRITE EXCISTING .XLS FILES!!!

' There seems to be an error with closing an exported file (no opening is defined)

' so I had to:

' - save it first, to avoid dataloss (previous step). It must be done before

' - define it as a object with an .Open ; Object name is OWB_XLSExport

' - somehow access/reopen this object (with .Open clause) & I choose to use .Save clause because double .Open is not allowed.

' - Then use the object (with .Open clause) to be comletely closed with .Close

' It might look dumb, but it was the only way to do it. There was no comleate solution even in QV community to successfully complete

' the multi-sheet export and the .XLS file would not remain opened. Does not make much sense as VBA in total, but works

set OWB_XLSExport = AppExcel.Workbooks.Open(XLSExport) 'Object OWB_XLSExport (with .Open ) is defined.

OWB_XLSExport.Save 'Object OWB_XLSExport is reopened/accessed via .Save

OWB_XLSExport.Close 'Object OWB_XLSExport is closed via .Close

'Final part of the report is saved and closed.

' First export file is opened, a sheet is added & data from second export is added. Then it is saved as the final report. - END

'AppExcel.Workbooks.Open(Ruta) 'Opens the Main ExportedFile

'Second part of the report is closed.

OWB_XLSExportTMP2.Close 'Object OWB_XLSExportTMP2 is closed via .Close

'Now closing App.Excel also from running processes (needs all workbooks to be closed).

AppExcel.Visible = False ' Excel.exe is closed

set AppExcel = nothing ' definition cleared to avoid conflicts with other functions.

set OWB_XLSExport = nothing ' definition cleared to avoid conflicts with other functions.

set OWB_XLSExportTMP1 = nothing ' definition cleared to avoid conflicts with other functions.

set OWB_XLSExportTMP2 = nothing ' definition cleared to avoid conflicts with other functions.

End Function

Re: Advancing A "Excel-Export-Macro"

Eijon — Wed, 27 Jul 2022 06:13:32 GMT

Hello! Sorry if some way I hijacked the thread. Btw I want to create macro on our qlikview web application to automatically "Send to Excel". May I know where do I start?

Re: Advancing A "Excel-Export-Macro"

marcus_sommer — Wed, 27 Jul 2022 07:53:34 GMT

If you used the IE plugin or Edge within the IE mode the use of such macros should be further possible but by using the AJAX client they won't work. In this case you might try to use actions with which you could export field-values of multiple fields into a csv-file or you may apply serversideexports-macros which in general allow to export objects which is similar to the send-feature. Helpful for any macro-stuff ist the APIGuide.qvw with various examples, like:

By larger export-jobs you should consider to use the desktop client with regular macros for it - which could be principally also automated whereby depending on the environment it is not trivial. I'm not sure but I assume the most customers don't use own created macro-stuff for bigger tasks else NPrinting or similar third party tools because it's usually cheaper to purchase a general solution instead of developing and maintaining own stuff.

- Marcus