Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Macro to export variables into an existing Excel file

Hi all,

I need of a Macro to export variables values into specific cells of an existing Excel file.

Is it possible?

Thanks

1 Solution

Accepted Solutions

Re: Macro to export variables into an existing Excel file

Hi Giancarlo,

Sorry for the delay in response. I was held up with work.I am here with attaching a file which is dynamic. Please have a look and do not hesitate to contact me if you want any changes.

Code:

Sub Test 

Dim XLApp, XLSheet

 

FileName = ActiveDocument.Variables("vExportPath").GetContent.String

Var1 = ActiveDocument.Variables("Var1").GetContent.String

Var2 = ActiveDocument.Variables("Var2").GetContent.String

Var3 = ActiveDocument.Variables("Var3").GetContent.String

 

Set XLApp  = CreateObject("Excel.Application") 

XLApp.Visible = True 'False to hide the excel 

XLApp.Workbooks.Open(FileName) 

set XLSheet = XLApp.Worksheets(1) 

 

XLSheet.Cells(1,1).Value =  Var1

XLSheet.Cells(3,2).Value =  Var2

XLSheet.Cells(4,7).Value =  Var3

XLSheet.Saveas FileName  

XLApp.Application.quit   

set XLSheet = Nothing   

set XLApp = Nothing   

Msgbox "Variables are exported sucessfully!!"   

 

End Sub 


Note: Don't forget to change the Current local security to "Allow System Access" while running the macro. If you forget to change, Qlikview will not be able to open the excel file.

Capture.PNG

Good luck!!

7 Replies

Re: Macro to export variables into an existing Excel file

Hi Giancarlo

Yes. Please upload a sample and expected output here.

Partner
Partner

Re: Macro to export variables into an existing Excel file

Hi Tamil,

thanks in advance for the answer.

Example:

I have a .qvw file with 3 variables:

  • Var1=10
  • Var2='test'
  • Var3=57

and I'd like to export these variable values (through a macro) into an existing .xlsm file in these cells:

  • Var1 into (1,1)
  • Var2 into (3,2)
  • Var3 into (4,7)

Re: Macro to export variables into an existing Excel file

Hi Giancarlo,

Sorry for the delay in response. I was held up with work.I am here with attaching a file which is dynamic. Please have a look and do not hesitate to contact me if you want any changes.

Code:

Sub Test 

Dim XLApp, XLSheet

 

FileName = ActiveDocument.Variables("vExportPath").GetContent.String

Var1 = ActiveDocument.Variables("Var1").GetContent.String

Var2 = ActiveDocument.Variables("Var2").GetContent.String

Var3 = ActiveDocument.Variables("Var3").GetContent.String

 

Set XLApp  = CreateObject("Excel.Application") 

XLApp.Visible = True 'False to hide the excel 

XLApp.Workbooks.Open(FileName) 

set XLSheet = XLApp.Worksheets(1) 

 

XLSheet.Cells(1,1).Value =  Var1

XLSheet.Cells(3,2).Value =  Var2

XLSheet.Cells(4,7).Value =  Var3

XLSheet.Saveas FileName  

XLApp.Application.quit   

set XLSheet = Nothing   

set XLApp = Nothing   

Msgbox "Variables are exported sucessfully!!"   

 

End Sub 


Note: Don't forget to change the Current local security to "Allow System Access" while running the macro. If you forget to change, Qlikview will not be able to open the excel file.

Capture.PNG

Good luck!!

Partner
Partner

Re: Macro to export variables into an existing Excel file

Perfect.

Thank you very much Tamil

Re: Macro to export variables into an existing Excel file

Great Giancarlo! Happy weekend!!

Partner
Partner

Re: Macro to export variables into an existing Excel file

Hi Tamil,

I need your help one more time

I need to export an image (loaded with bundle) into an existing Excel through a macro.

Is it possible??

Thanks

Re: Macro to export variables into an existing Excel file

Hello Giancarlo,

Unfortunately, this is not possible as far as I know. Once I tried to answer someone in the community and realized that it's not possible. Sorry for not being of any help in this case.

exporting table into excel with keeping numbers and images into it