Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
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.
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.
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.
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.
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