Saving Excel file created by macro with current date and formatting

[#1]

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

[Frank_Hartmann]

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!

[#3]

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.

[Frank_Hartmann]

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

[#5]

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

[Frank_Hartmann]

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

[#7]

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.

[Frank_Hartmann]

Is there an error message showing in module?

[tamilarasu]

Hi Karen,

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

[#10]

Oddly enough, no messages of any kind.