3 Replies Latest reply: Oct 31, 2011 3:15 PM by Jaanus Muruõis RSS

    Advancing A "Excel-Export-Macro"

    daniel_k Oct 14, 2011 7:40 AM

      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

      • 11439 Views
      • Tags:
        • Advancing A "Excel-Export-Macro"
          Vlad Gutkovsky
          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