Excel Export of a Chart with Macro define under QV doc is not running

[Anonymous]

I have created an Excel export macro with Ctrl+M in QV documnet . As it is not working with the QMC task (with Post Reload run Macro option in triggers setting).

Can anyone suggest a way to invoke macro in load script part so that it macro can run even when I reload QV using QMC task?

Thanks in Advance !!

Sanjay

[el_aprendiz111]

Hi,

This can serve you,

http://www.qlikblog.at/509/simulating-include-command-qlikview-macros/

[goro2010]

With a few small modifications, you could use my answer here also Re: Execute batch file with input defined by variable from script

[Anil_Babu_Samineni]

You must use the server path to export not the localhost.

[Anonymous]

Hi Fer,

I was trying it out but I think I am missing something here so that it can give me desirable results.Here is my scenario :

I have written this code in QV doc macro( ctrl+M) window:

Private Function IncludeAndExecute (Byval p_strFilePath) 'as Boolean

Dim objFSO 'as Object

Dim objFile 'as Object

Dim strScript 'as  String

'// Open the File using File-System-Objects

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("p_strFilePath")

'// Copy the content of the file

strScript = file.ReadAll

'// Just destroy the objects again

Call objFile.Close()

'// Execute the script globally

Call ExecuteGlobal(strScript)

'

IncludeAndExecute = true

End Function

I have added this line in load script of QV :

Call IncludeAndExecute(Y:\XYZ.vbs);

which is giving me below script line error .I have also tried to pass the file path in single and double quotes but didn't work. I think the syntax problem which i am not sure and not able to figure out yet.

Script line error:

Call IncludeAndExecute(Y:\XYZ.vbs)

Here is the XYZ.vbs code which i palced in Y:\XYZ.vbs location :

Sub ExcelExpwCaption 

     'Set the path where the excel will be saved  

    filePath = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1)")

      'filePath = "Y:\"

      a=ActiveDocument.Evaluate("Replace(DocumentName(),'.qvw','.xlsx')")

     'Create the Excel spreadsheet  

     Set excelFile = CreateObject("Excel.Application") 

     excelFile.Visible = true 

     'Create the WorkBook 

     Set curWorkBook = excelFile.WorkBooks.Add 

     'Create the Sheet 

     Set curSheet = curWorkBook.WorkSheets(1) 

     'Get the chart we want to export 

     Set tableToExport = ActiveDocument.GetSheetObject("CH823") 

     Set chartProperties = tableToExport.GetProperties 

     tableToExport.CopyTableToClipboard true 

     'Get the caption 

     chartCaption = tableToExport.GetCaption.Name.v 

     'MsgBox chartCaption 

     'Set the first cell with the caption 

     curSheet.Range("A1") = chartCaption 

     'Paste the rest of the chart 

     curSheet.Paste curSheet.Range("A2") 

     excelFile.Visible = true 

     'Save the file and quit excel 

     curWorkBook.SaveAs a 

     curWorkBook.Close 

     excelFile.Quit 

     'Cleanup 

     Set curWorkBook = nothing 

     Set excelFile = nothing 

End Sub 

I think I am doing a lil syntax miss to call this macro in QV. Could you please help me out

Thanks.

Sanjay

[Anonymous]

Hi Anil,

Sorry I didn't get it well. I have my QV doc and .vbs macro file on the same system.

What does server path refer here ?

If possible can we connect on this?

I have texted you on FB too.

Thanks,

Sanjay

[Anil_Babu_Samineni]

I am not expert on Macro's. I've mentioned check the Path whether it is correct or not.

Call IncludeAndExecute(Y:\XYZ.vbs)

PS - Here, Is that Y:\ is your local disk that is the reason i recommend you to change the path in server extend path. Correct me if i am wrong.

[el_aprendiz111]

Hi Sanjay

1 missing ')'

    filePath = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1)")

                   ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")

[Anonymous]

Hi Fer,

The code in XYZ.vbs file is working correctly if used this in QV macro (ctrl +M). I am getting the chart export if i put a trigger on post reload. So I belive the code in XYZ.vbs is having no issue. But as I want the QV to be reloaded with QMC so the reason to use macro fucntion. As I am not well known to 'calling by value' a macro function in load script so might be the issue. Could you please help me with how I can pass ('.vbs file path of XYZ.vbs  file in load script macro function ) and how the code written in IncldueandExeucte can work on path file path passed and can give me the excel export of chart.

Many Thanks Again !!
Sanjay