variable in macro to set path

[omyahamburg]

Hi

I am a real newbie in macros.

I have a variable in my qvw named vVar with the value "2013-06".

I like to use this variable in my export to excel macro to set the file name under which the excel file will be stored.

After extensive searching I am still struggling and don't get it worked.

This is my macro code:

'///////////////////////////

sub Export

Dim aryExport(0,3)

aryExport(0,0) = "CH01"  
aryExport(0,1) = "Amount per country"
aryExport(0,2) = "A3"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'filePath = "C:\test\test "&vVar&”.xlsx"  '// this is not working

filePath = "C:\test\test 2013-06.xlsx"  '// manually setting the file name is working

objExcelWorkbook.SaveAs filePath


end sub

'///////////////////////////

Additionally I would like to put the caption into cell "A1" in the excel workbook.

Can somebody help me out ?

Thanks in advance.

rgds

Joerg

[Gysbert_Wassenaar]

You need to get that value of the variable first and assign it to vVar:

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

[omyahamburg]

When I try this code, it gives me the error

”.xlsx"  is unknown string

sub Export

Dim aryExport(0,3)

aryExport(0,0) = "CH01"  
aryExport(0,1) = "Amount per country"
aryExport(0,2) = "A3"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

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


filePath = "C:\test\test "&vVar&”.xlsx"

objExcelWorkbook.SaveAs filePath


end sub

[#4]

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).Cells(1,1).Value='Title'

And Save as the file.

Regards,

Kabilan K

[Gysbert_Wassenaar · Accepted solution]

You've got a wrong quote in there: filePath = "C:\test\test "&vVar&”.xlsx". Make sure you use the double quotes character. Also, if that's not the problem try using msgbox statements to see what vVar and filepath contain:

    msgbox vVar

    msgbox filepath

[omyahamburg]

I got it working (unfortunately still without export of the caption):

 

sub exportToExcel_Variant1

Dim aryExport(0,3)

aryExport(0,0) = "CH02"
aryExport(0,1) = "Amount by country"
aryExport(0,2) = "A3"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

set v = ActiveDocument.Variables("vVar")

filePath = "C:\test "&(v.GetContent.String)&".xlsx"

objExcelWorkbook.SaveAs filePath
'objExcelWorkbook.Close

end sub