Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Thank You for Your Advice.
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
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?
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