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]

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

I receive this message when Excel is not closed befor executing or the file already exists in that folder with the same name!

make sure that when executing the macro excel is closed!

Hope this helps

[tamilarasu]

I have a couple of questions to ask you before explaining the procedure.

● Do you really want to hard-code the path into the macro? Or do you want an input box? (Which allows you to paste the file path in front end directly and the path can be changed later).

● If you wish we can include browse folder window which allows you to browse the folder whenever you press the export button. File will be saved in the selected path.

● What is the date format for timestamp? Do you want to see your file name like "CGS Daily Census Report 2- 03Nov2016"?

Once you answered these questions, i'll provide the code along with the procedure by tomorrow morning.

[#4]

Thank you.  I will take anything!

First, I would like it hard-coded into the macro as I have an automation process that runs every day and gets these files where they need to be without much interaction.

Currently, the date format is set to MM.DD.YYYY, but am willing to change it as long as I can get this to work.

[#5]

I've now tried all versions of this, also using code 51 as I have Excel 2010.  Something is definitely not letting me save this and I'm assuming it's either my pc configuration or my QlikView settings, not sure at this point.

Thanks again for all the help though, I'm learning a lot and truly appreciate it.

[tamilarasu · Accepted solution]

Fine. I have updated the code with the above mentioned path and chart id. Just copy and paste the below code in your application macro window and run it. I have also attaced a sample file which saves the excel in the above mentioned path.

Sub Export

  Dim oXLDoc, i
  set oXL = CreateObject("Excel.Application") 

  oXL.DisplayAlerts = False
  oXL.visible = True 'change to False to turn screen updating off

  Set oXLDoc = oXL.Workbooks.Add

  FilePath =  "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\"    ' File path can be changed later.
  FileName = "CGX Daily Census Report 2 - " & ActiveDocument.Evaluate("date(Now(), 'MM.DD.YYYY')") 'Date format can be changed later

  '---------------------------------------
  SheetObj=Array("objPended","objApproved")   'Chart ID's
  '---------------------------------------

for i=0 to UBound(SheetObj)

  oXL.Sheets.Add
  oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

  Set oSH = oXL.ActiveSheet
  oSH.Range("A1").Select

  Set obj = ActiveDocument.GetSheetObject(SheetObj(i))
  obj.CopyTableToClipboard True
  oSH.Paste
  sCaption=obj.GetCaption.Name.v

  oSH.Rows("1:1").Select
  oXL.Selection.Font.Bold = True
  oXL.Selection.AutoFilter

  oSH.Cells.Select
  oXL.Selection.Columns.AutoFit

  oSH.Range("A1").Select    
  oSH.Name=left(sCaption,30)

  Set obj=Nothing
  Set oSH=Nothing

Next
'---------------------------------------
  Call DeleteBlankSheets(oXLDoc)
'---------------------------------------
    oXLDoc.Sheets(1).Select
    oXLDoc.SaveAs FilePath & FileName & ".xlsx"
    oXL.DisplayAlerts = True
    oXLDoc.Close True
    oXL.Quit

  Set oXL    = Nothing
  Set oXLDoc = Nothing

  Msgbox "Export successfully completed."

End Sub 

'--------------------------------------------------------------------
Private Sub DeleteBlankSheets(ByRef oXLDoc)

For Each ws In oXLDoc.Worksheets
  If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
     On Error Resume Next
     Call ws.Delete()
  End If
Next

End Sub
'--------------------------------------------------------------------

Let me know.

[#7]

Thank you so much, this has worked perfectly!

I have a follow up question though.  What would I need to do if I needed to create more than one file and save each to a different location with different names?

Thank you again!  This helps A LOT.

[tamilarasu]

Hi Karen,

Not a problem. Regarding your second question, we need to slightly modify the above code. I will update the code when I get free time.

I just thought and would like to share with you. You can mark Frank's reply as "helpful answer".  I understand that he helped you to learn something (From your below reply ) new.

Karen Mujeyi wrote:

Thanks again for all the help though, I'm learning a lot and truly appreciate it.

[arpita]

Hi Frank,

Can you please help me with script wherein I want all objects on same sheet wherein Objects can be both Data and Image
Also, everytime its exported, new files are created and saved in folder

Thanks