-
Advancing A "Excel-Export-Macro"
Vlad Gutkovsky Oct 16, 2011 2:06 PM (in response to daniel_k)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"
daniel_k Oct 19, 2011 10:02 AM (in response to daniel_k)Thank You for Your Advice.
-
Re: Advancing A "Excel-Export-Macro"
Jaanus Muruõis Oct 31, 2011 3:15 PM (in response to daniel_k)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