Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
giancarlo_corra
Partner - Contributor II
Partner - Contributor II

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
tamilarasu
Champion
Champion

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!!

View solution in original post

7 Replies
tamilarasu
Champion
Champion

Hi Giancarlo

Yes. Please upload a sample and expected output here.

giancarlo_corra
Partner - Contributor II
Partner - Contributor II
Author

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)
tamilarasu
Champion
Champion

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!!

giancarlo_corra
Partner - Contributor II
Partner - Contributor II
Author

Perfect.

Thank you very much Tamil

tamilarasu
Champion
Champion

Great Giancarlo! Happy weekend!!

giancarlo_corra
Partner - Contributor II
Partner - Contributor II
Author

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

tamilarasu
Champion
Champion

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