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]

and if you start the macro from module?

[#3]

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.

[#4]

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?

[Frank_Hartmann]

just comment this line by putting  ' in front like:

'qvDoc.GetApplication.WaitForIdle

and try again

[#6]

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:

[Frank_Hartmann]

can you please show full filePath in macro?

are you sure it is correct? path exists?

[#8]

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"

[Frank_Hartmann]

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?

[#10]

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:

[Frank_Hartmann]

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:

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"), 43

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