topic Re: Excel file not saved by Macro in QlikView

Excel file not saved by Macro

its_anandrjs — Fri, 09 Nov 2018 07:31:55 GMT

Hi All,

I have requirement where we have to send 3 straight chart objects data into excel.

We did this but every time when we export excel it asking to save the file on the location.

But we are looking where excel get store without asking save as.

Some thing very similar in the below thread where tamilarasu‌ suggest but there is no luck it not worked.

Re: Macro to export multiple tables in to single excel

Can any one suggest best way for the same.

Regards

Anand

Re: Excel file not saved by Macro

andrey_krylov — Fri, 09 Nov 2018 08:13:02 GMT

Hi Anand, if you get a message "file exists" try to add DisplayAlerts = False

set oXL=CreateObject("Excel.Application")

oXL.DisplayAlerts = False

Re: Excel file not saved by Macro

Frank_Hartmann — Fri, 09 Nov 2018 08:23:24 GMT

Can you show your macro?

Re: Excel file not saved by Macro

its_anandrjs — Fri, 09 Nov 2018 09:07:57 GMT

Thanks Here is my Macro Code

sub exportToExcel 


'==============================================================
' File Path & Name


Path = "E:\temp\"

' Set Path = ActiveDocument.GetVariable("vPath")
' Path = vPath.GetContent.String

 FileName = "Test_"
 strSaveFile = Path & FileName
'==============================================================
'Open Excel


set oXL=CreateObject("Excel.Application") 
oXL.visible=True 
oXL.Workbooks.Add


aSheetObj=Array("TB01","TB02","CH01")  ' Chart ID's here


'==============================================================
for i=0 to UBound(aSheetObj)
 Set oSH = oXL.ActiveSheet

 num_rows = oSH.UsedRange.Rows.Count

 If num_rows = 1  then
 oSH.Range("A2").Select    
 Else
 oSH.Range("A" & num_rows+4).Select 
 End If

 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
 obj.CopyTableToClipboard True
 oSH.Paste 
 sCaption=obj.GetCaption.Name.v
 set obj=Nothing  
        
oSH.Cells.Select


oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17


 If num_rows = 1  then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
 Else

oSH.Range("A" & num_rows+3).Value  = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
 End If
 'oXL.Selection.Columns.AutoFit  


next
'==============================================================
  oSH.Range("A1").Select  
  oXL.Sheets("Sheet2").Delete
  oXL.Sheets("Sheet3").Delete
    oSH.Name = "Data"
    
   oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx" 
   'oXL.ActiveWorkBook.SaveAs strSaveFile & ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY hh:mm:ss')") & ".xlsx"
   
set oSH = Nothing    
set oXL=Nothing
end sub

And i am sharing my sample QVW file also.

Regards,

Anand

Re: Excel file not saved by Macro

its_anandrjs — Fri, 09 Nov 2018 09:10:13 GMT

Thanks Andrey,

I which line i have to write this below is my macro code.


sub exportToExcel   
  
  
'==============================================================  
' File Path & Name  
  
  
Path = "E:\temp\"  
  
' Set Path = ActiveDocument.GetVariable("vPath")  
' Path = vPath.GetContent.String  
  
 FileName = "Test_"  
 strSaveFile = Path & FileName  
'==============================================================  
'Open Excel  
  
  
set oXL=CreateObject("Excel.Application")   
oXL.visible=True   
oXL.Workbooks.Add  
  
  
aSheetObj=Array("TB01","TB02","CH01")  ' Chart ID's here  
  
  
'==============================================================  
for i=0 to UBound(aSheetObj)  
 Set oSH = oXL.ActiveSheet  
  
 num_rows = oSH.UsedRange.Rows.Count  
  
 If num_rows = 1  then  
 oSH.Range("A2").Select      
 Else  
 oSH.Range("A" & num_rows+4).Select   
 End If  
  
 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))  
 obj.CopyTableToClipboard True  
 oSH.Paste   
 sCaption=obj.GetCaption.Name.v  
 set obj=Nothing    
          
oSH.Cells.Select  
  
  
oSH.Columns("A").ColumnWidth = 12.17  
oSH.Columns("B").ColumnWidth = 12.17  
  
  
 If num_rows = 1  then  
oSH.Range("A" & num_rows).Value = sCaption  
oSH.Range("A" & num_rows).Font.Bold = True  
'oSH.Range("A" & num_rows).Font.ColorIndex = 3  
oSH.Range("A" & num_rows).Interior.ColorIndex = 40  
 Else  
  
oSH.Range("A" & num_rows+3).Value  = sCaption  
oSH.Range("A" & num_rows+3).Font.Bold = True  
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3  
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40  
 End If  
 'oXL.Selection.Columns.AutoFit    
  
  
next  
'==============================================================  
  oSH.Range("A1").Select    
  oXL.Sheets("Sheet2").Delete  
  oXL.Sheets("Sheet3").Delete  
    oSH.Name = "Data"  
      
   oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"   
   'oXL.ActiveWorkBook.SaveAs strSaveFile & ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY hh:mm:ss')") & ".xlsx"  
     
set oSH = Nothing      
set oXL=Nothing  
end sub

Regards

Anand

Re: Excel file not saved by Macro

Frank_Hartmann — Fri, 09 Nov 2018 09:25:57 GMT

i tried to rebuild the issue.

For me it is not asking to save as.

it is directly saving to C:/temp

If file already exists then popup shows to replace the file.

If I add the statement andrey.krylov showed then everything works very smooth

Can you give more details?

Re: Excel file not saved by Macro

its_anandrjs — Fri, 09 Nov 2018 09:37:21 GMT

Thanks Frank,

Basic idea for this is.

1. Every day a QVW file reloaded and three objects data get stored by macro in excel one by one.

2. This excel file is store in some location lets say E:\temp and then send this excel file as attachment on mail.

Here also mail is triggered by qlikview file and send to some 5-7 members.

For andrew code on which line you put that code can you use my file for this and shown to me its appreciate.

Regards

Anand

Re: Excel file not saved by Macro

Frank_Hartmann — Fri, 09 Nov 2018 09:52:56 GMT

see attached!

hope this helps

Re: Excel file not saved by Macro

its_anandrjs — Fri, 09 Nov 2018 10:19:24 GMT

Thanks Frank,

Its working fine, one more thing suppose i change the folder location of excel file to save on new location will it effect not to export excel.

Just checking from my side and let you know, because currently it is working from folder location E:\temp\ may be some windows security issue let me check and let you know.

Regards,

Anand

Re: Excel file not saved by Macro

its_anandrjs — Thu, 15 Nov 2018 07:38:08 GMT

Hi Frank,

While i put this code on the Windows scheduler it is not working.
Can you please check this.

Regards
Anand

Re: Excel file not saved by Macro

its_anandrjs — Thu, 15 Nov 2018 08:08:28 GMT

Hi Frank,

With new folder it is working fine but when i put this QVW on windows schedule it not working can you check about this.

Regards,

Anand