Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Advancing A "Excel-Export-Macro"

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

3 Replies
vgutkovsky
Honored Contributor II

Advancing A "Excel-Export-Macro"

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.

Not applicable

Advancing A "Excel-Export-Macro"

Thank You for Your Advice.

Not applicable

Re: Advancing A "Excel-Export-Macro"

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