Excel file not saved by Macro

[its_anandrjs]

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

[andrey_krylov]

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

set oXL=CreateObject("Excel.Application")

oXL.DisplayAlerts = False

[Frank_Hartmann]

Can you show your macro?

[its_anandrjs]

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

[its_anandrjs]

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

[Frank_Hartmann]

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?

[its_anandrjs]

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

[Frank_Hartmann · Accepted solution]

see attached!

hope this helps

[its_anandrjs]

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

[its_anandrjs]

Hi Frank,

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

Regards
Anand