VBS script problem with excel file saving

[#1]

Hi all,

I'm trying to create to export a report in excel file by VBS macro and save it automatically in a particular folder..actually I´m able to create the file excel but i have always problem in saving it.

here the piece of code I use for saving the file

Const xlWorkbookNormal = -4143 '// Excel compatibility mode

Const xlWorkbookDefault = 51 '//Excel 2007

Dim fileName 'as String

fileName = "C:\Desktop\NEW QLIKVIEW PRJ\test.xls"

on error resume next

Call objExcelWorkbook.SaveAs(fileName,xlWorkbookDefault) '// or use xlWorkbookNormal

if (len(Err.Description) > 0) then

                msgbox("Error Saving Excel" & Chr(13) &  Err.Description & Chr(13) & Err.Source)

end if

on error goto 0

objExcelWorkbook.Application.Quit

In attachment you can find the entire script,

Can someone tell me please

- where is the error?

- in which point of script have to insert this?

- do you know a macro for making the same stuff but to export the file in pdf?

Thanks to all in advance for help!

[marcus_sommer]

You are sure the save-path is correct? Desktop is most often here: C:\Users\YourUserName\Desktop.

- Marcus

[#3]

Hi Markus,

thanks for answer..yeah forgot to tell,  the path name i have inserted is just example

[marcus_sommer]

I couldn't find the save-part in this attachment - but perhaps is the cause the file-extension ".xls" by saving as 2007 version ".xlsx"?

- Marcus

[#5]

Hi Markus,

I have inserted the save command at the bottom of the entire script..here again the entire script in attachment, i have tried to change extension of excel file to xlsx but have always the same error message, please check screenshot

[Andrea_Ghirardello]

Try this

set xlApp = CreateObject("Excel.Application")

xlApp.visible = False

xlApp.ScreenUpdating = False

xlApp.DisplayAlerts=false

xlApp.Workbooks.Add

Set wbTarget = xlApp.Workbooks(1)

wbTarget.SaveAs ("yourPath\yourFileName.xlsx")

'some changes in the wbTarget

wbTarget.Save

wbTarget.close

xlApp.quit

'clean up

set wbTarget = nothing

set xlApp = nothing

1- use xxx=value instead of "Const xxx=value"

2- you can avoid "call workbook.saveas(filename, const)", just use workbook.saveas(path&name.xlsx)

3- I suggest you avoid macros of that kind. If you need to perform complex actions I suggest you a specific software (e.g. NPrinting).

[#7]

Hi Andrea,

thanks for suggestion...cause I m totally new to VBS scripting.

Now with your suggestion i'm generating a new excel file empty but i would like to save the excel file that i generate with the above part of the script and save it with particular name/folder

Have an idea how to do it?

I saw that there are particular software for doing what i´m trying do to but unfortunately i have some respnsible that decided to do in this way

Thanks again

[Andrea_Ghirardello]

Add two variables into your qvw and let user configure them: vPath and vFileName.

In your script:

set vPath = ActiveDocument.Variables("Output_Path")

set vFileName = ActiveDocument.Variables("Output_FileName")

if trim(vPath.getcontent.string) <> "" and trim(vFileName.getcontent.string) <> "" and anyothercondition then

     set xlApp = CreateObject("Excel.Application")

     xlApp.visible = False

     xlApp.ScreenUpdating = False

     xlApp.DisplayAlerts=false

     xlApp.Workbooks.Add

     Set wbTarget = xlApp.Workbooks(1)

     wbTarget.SaveAs (vPath.getcontent.string & "\" & vFileName.getcontent.string)

     'some changes in the wbTarget

     wbTarget.Save

     wbTarget.close

     xlApp.quit

end if

'clean up

set wbTarget = nothing

set xlApp = nothing

[marcus_sommer]

I think your script is a little bit too complicated, any parts won't needed - functions for add + name sheets - also all main parts should be sub-routines and not functions (aimed to return only one value back). See the attachment and combine it with the suggestions from Andrea.

- Marcus

[#10]

Hi Andrea,

sorry but seems it doesn't run..