topic Re: Re: VBS script problem with excel file saving in QlikView

VBS script problem with excel file saving

Sun, 01 Sep 2013 09:39:02 GMT

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!

Re: VBS script problem with excel file saving

marcus_sommer — Sun, 01 Sep 2013 10:35:42 GMT

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

- Marcus

Re: VBS script problem with excel file saving

Sun, 01 Sep 2013 10:37:47 GMT

Hi Markus,

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

Re: VBS script problem with excel file saving

marcus_sommer — Sun, 01 Sep 2013 15:00:07 GMT

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

Re: Re: VBS script problem with excel file saving

Sun, 01 Sep 2013 18:12:05 GMT

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

Re: VBS script problem with excel file saving

Andrea_Ghirardello — Sun, 01 Sep 2013 19:45:27 GMT

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).

Re: VBS script problem with excel file saving

Sun, 01 Sep 2013 20:21:14 GMT

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

Re: VBS script problem with excel file saving

Andrea_Ghirardello — Mon, 02 Sep 2013 07:13:02 GMT

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

Re: Re: VBS script problem with excel file saving

marcus_sommer — Mon, 02 Sep 2013 07:31:44 GMT

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

Re: Re: VBS script problem with excel file saving

Mon, 02 Sep 2013 09:06:34 GMT

Hi Andrea,

sorry but seems it doesn't run..

Re: VBS script problem with excel file saving

Andrea_Ghirardello — Mon, 02 Sep 2013 09:43:45 GMT

The problem may be spaces on your path...try

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

Re: VBS script problem with excel file saving

Mon, 02 Sep 2013 09:58:47 GMT

in save as, you may need to mention file format also..

So please mention "51" for xlsx.

For different kind of file format:

http://msdn.microsoft.com/en-us/library/office/bb241279(v=office.12).aspx

Sample code:

ExcelWorkbook.SaveAs(vPath.getcontent.string & "\" & vFileName.getcontent.string ,51)

Karthik

Re: VBS script problem with excel file saving

suhle1234 — Mon, 14 Sep 2020 07:58:50 GMT

Hi this is not working by me:

ExcelWorkbook.SaveAs(vPath.getcontent.string & "\" & vFileName.getcontent.string ,51)

I can't give the parameter ,51, its working without.

I need to save in Format 52 with Makro enabled.

Thank you in advance for your help.

Re: VBS script problem with excel file saving

suhle1234 — Mon, 14 Sep 2020 11:29:37 GMT

Working:

FileFormat=52

strpath = "\\testdrive\test.xlsm"

AppExcel.Workbooks(1).SaveAs strpath, Fileformat