Macro to export variables into an existing Excel file

[giancarlo_corra]

Hi all,

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

Is it possible?

Thanks

[tamilarasu]

Hi Giancarlo

Yes. Please upload a sample and expected output here.

[giancarlo_corra]

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 · Accepted solution]

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.

Good luck!!

[giancarlo_corra]

Perfect.

Thank you very much Tamil

[tamilarasu]

Great Giancarlo! Happy weekend!!

[giancarlo_corra]

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]

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