topic Re: Saving Excel file created by macro with current date and formatting in QlikView

Saving Excel file created by macro with current date and formatting

Wed, 02 Nov 2016 16:00:41 GMT

Hello QlikView Community,

I am new to working with macros and have a macro I found in this community that creates an Excel file with multiple tabs (Thanks QlikCommunity!).

I would like to do three (3) additional things with this file and not sure how.

1. My priority is: what can I add to this macro script to save the file (have multiple drives so will not save to the usual C-drive, not sure if that is an issue)?

2. Priority 2: When saving the file, what do I need to add to have the current date added to the file name?

3. Not priority, but very helpful: What can I add to format each sheet to have the filter option on each sheet? I am not trying to actually filter anything, just want to format the sheet to have the filter on:

I found this thread in the community, but do not know how to get this to work for what I have.

How to save excel file with current date using macro

Thank you for any help!

This is my macro script thus far (again, used base script from this community, so not mine as I did not know how to do this):

sub exportToExcel_Variant6

'// Array for export definitions
Dim aryExport(2,3)

aryExport(0,0) = "objPended"
aryExport(0,1) = "Pending"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "objApproved"
aryExport(1,1) = "Approved"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

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

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

end sub

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Wed, 02 Nov 2016 16:30:07 GMT

For the first two things have a look at this code.

Just adapt the path where you want to save the Excelfile (-->filepath)

sub exportToExcel_Variant6
Dim strFilePath
Dim strFileName
timestamp =  date()&"-"&time()
strFilePath = ActiveDocument.GetProperties.FileName
strFileName = (InStrRev(strFilePath,"\",len(strFilePath)))
strFileName = Mid(strFilePath, InStrRev(strFilePath,"\") + 1)
'msgbox(strFileName)
'// Array for export definitions
Dim aryExport(2,3)
aryExport(0,0) = "objPended"
aryExport(0,1) = "Pending"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"
aryExport(1,0) = "objApproved"
aryExport(1,1) = "Approved"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"
Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
filePath = "C:\Users\Admin\Desktop\"&strFileName&"-"&timestamp&".xlsx"
objExcelWorkbook.SaveAs filePath
end sub

hope this helps!

Re: Saving Excel file created by macro with current date and formatting

Wed, 02 Nov 2016 16:59:04 GMT

Thank you for the script. As this is new to me, where in this script do I set the file's name? Not really sure what is being done in lines 6 and 7 or 21 and 22, so did not want to edit.

Thank you again.

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Wed, 02 Nov 2016 17:16:13 GMT

The setup of the macro was to use the QV-Documenttitle as the Filename.

But if you want to use a self created name then use this code to only append the timestamp to the title:

sub exportToExcel_Variant6
timestamp =  date()&"-"&time()
'// Array for export definitions
Dim aryExport(2,3)
aryExport(0,0) = "objPended"
aryExport(0,1) = "Pending"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"
aryExport(1,0) = "objApproved"
aryExport(1,1) = "Approved"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"
Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
filePath = "C:\Users\Admin\Desktop\TITEL HERE"&"-"&timestamp&".xlsx"
objExcelWorkbook.SaveAs filePath
end sub

Just adapt C:\Users\Admin\Desktop\TITEL HERE with your path & titel and the macro will add the timestamp to it and saves the document.

hope this helps

Re: Saving Excel file created by macro with current date and formatting

Wed, 02 Nov 2016 19:02:56 GMT

Oddly enough, it is not working. The file gets created, but nothing is saved and keeps "Book1" as title.

This is what I've used. Can you possibly see what I've done wrong?

sub exportToExcel_Variant6

timestamp = date()&"-"&time()

'// Array for export definitions
Dim aryExport(2,3)

aryExport(0,0) = "objPended"
aryExport(0,1) = "Pending"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "objApproved"
aryExport(1,1) = "Approved"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

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

' Save and quit

filePath = "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"
objExcelWorkbook.SaveAs filePath

end sub

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Wed, 02 Nov 2016 20:16:44 GMT

this is working for me:

sub exportToExcel_Variant6
timestamp = date()
'// Array for export definitions
Dim aryExport(1,3)
aryExport(0,0) = "objPended"
aryExport(0,1) = "Pending"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"
aryExport(1,0) = "objApproved"
aryExport(1,1) = "Approved"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"
Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
filePath = "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"
objExcelWorkbook.SaveAs filePath
end sub

see attached file

(make sure that the functions like copyObjectsToExcelSheet are available in Module --> see module of attached app)

hope this helps

Re: Saving Excel file created by macro with current date and formatting

Wed, 02 Nov 2016 20:48:24 GMT

I will have to further investigate. I took your entire macro script and used it in mine, and still have the same issue. This time however, the file is no longer "Book1" but has 2016 as the file name, but it stays open and does not save, either to my V-drive or my C-drive.

Thank you for all the help. I will need to keep looking into why it is not saving.

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Wed, 02 Nov 2016 20:52:40 GMT

Is there an error message showing in module?

Re: Saving Excel file created by macro with current date and formatting

tamilarasu — Thu, 03 Nov 2016 03:33:40 GMT

Hi Karen,

I have attached a sample which fulfill all your three requirments. Kindly let me know If you come across any issues.

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 11:57:03 GMT

Oddly enough, no messages of any kind.

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Thu, 03 Nov 2016 12:16:52 GMT

and if you start the macro from module?

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 12:35:54 GMT

I've tried it that way as well, and while I receive no error message, the process simply does not move forward.

It opens a clean Excel file, labels the first tab "Pended" and then stops, highlighting the following in the module.

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 12:36:56 GMT

Thank you for the suggestion, unfortunately, I am quite a novice at this and not sure how to implement this or incorporate it into my module.

Do I add this to my current script in the module? If so, where?

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Thu, 03 Nov 2016 12:41:52 GMT

just comment this line by putting ' in front like:

'qvDoc.GetApplication.WaitForIdle

and try again

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 12:56:35 GMT

Once I do that, it does create the file properly, but names is 2016 only (not current date) and then stops like before, but this time at the SaveAs filePath section as seen below with the message that SaveAs method of workbook class failed:

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Thu, 03 Nov 2016 13:13:13 GMT

can you please show full filePath in macro?

are you sure it is correct? path exists?

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 13:28:24 GMT

Sorry, I just noticed it got cut off. The file path is:

V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census

I use it daily and simply copied and pasted it from an Excel file I have in that folder and add the file name piece:

filePath = "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Thu, 03 Nov 2016 13:34:46 GMT

and if you delete the filePath row and use the below row like that:

objExcelWorkbook.SaveAs "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"

the same error appearing?

Re: Saving Excel file created by macro with current date and formatting

Thu, 03 Nov 2016 14:05:35 GMT

Tried it and same error message. Could it have something to do with the fact that this is a server I connect to using a VPN?

I also tried saving to C-drive using the following and get this error message:

New FilePath:

objExcelWorkbook.SaveAs "C:\Users\KSM9972\Desktop\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"

New Error Message:

Microsoft Excel cannot access the file 'C:\Users\KSM9972\Desktop\CGX Daily Census Report 2 -11\3\7C448000'. There are several possible reasons:

Re: Saving Excel file created by macro with current date and formatting

Frank_Hartmann — Thu, 03 Nov 2016 14:17:54 GMT

as long as the V disk is mounted in your system that should not be the problem!

is it possible that you recently updated your excel version from an old to a newer one?

If yes then probably the SaveAs method wont work in this way und you will haveto use either this command:

objExcelWorkbook.SaveAs ("V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"), 56

or this command:

in order to save the document according the version you are using!

for reference have a look here:

petri.com/forums/forum/windows-scripting/general-scripting/28303-saveas-method-of-workbook-class-failed

hope this helps