Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
With a few small modifications, you could use my answer here also Re: Execute batch file with input defined by variable from script
You must use the server path to export not the localhost.
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
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
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.
Hi Sanjay
1 missing ')'
filePath = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1)")
ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")
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