Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Saving Excel file created by macro with current date and formatting

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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.

View solution in original post

27 Replies
Frank_Hartmann
Master II
Master II

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!

Not applicable
Author

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
Master II
Master II

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

Not applicable
Author

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
Master II
Master II

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

Not applicable
Author

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
Master II
Master II

Is there an error message showing in module?

tamilarasu
Champion
Champion

Hi Karen,


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

Not applicable
Author

Oddly enough, no messages of any kind.