topic Re: Macro to export variables into an existing Excel file in QlikView

Macro to export variables into an existing Excel file

giancarlo_corra — Wed, 17 May 2017 08:38:57 GMT

Hi all,

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

Is it possible?

Thanks

Re: Macro to export variables into an existing Excel file

tamilarasu — Thu, 18 May 2017 08:09:32 GMT

Hi Giancarlo

Yes. Please upload a sample and expected output here.

Re: Macro to export variables into an existing Excel file

giancarlo_corra — Thu, 18 May 2017 08:16:13 GMT

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

tamilarasu — Fri, 19 May 2017 08:44:19 GMT

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

Re: Macro to export variables into an existing Excel file

giancarlo_corra — Fri, 19 May 2017 12:38:59 GMT

Perfect.

Thank you very much Tamil

Re: Macro to export variables into an existing Excel file

tamilarasu — Sun, 21 May 2017 06:37:10 GMT

Great Giancarlo! Happy weekend!!

Re: Macro to export variables into an existing Excel file

giancarlo_corra — Mon, 22 May 2017 15:57:56 GMT

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

tamilarasu — Mon, 22 May 2017 16:09:59 GMT

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