topic Re: Automatic overwrite excel file in QlikView

Automatic overwrite excel file

Wed, 16 Jan 2013 15:00:34 GMT

Hi,

I create an excel file out of QlikView with a macro.

How can i get to automatic overwrite the file with a same name on my harddisk.

Thank you all fot answers

Gerrit

Macro i have:

Sub ExcelExpwCaption
'Set the path where the excel will be saved
filePath = "C:\Voorraadanalyse"

'Create the Excel spreadsheet
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
'Create the WorkBook
Set curWorkBook = excelFile.WorkBooks.Add
'Create the Sheet
Set curSheet = curWorkBook.WorkSheets(1)

'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("CH01")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTableToClipboard true

'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption

'Set the first cell with the caption
curSheet.Range("A1") = chartCaption
'Paste the rest of the chart
curSheet.Paste curSheet.Range("A2")
excelFile.Visible = true

'Save the file and quit excel
curWorkBook.SaveAs filePath
curWorkBook.Close
excelFile.Quit

'Cleanup
Set curWorkBook = nothing
Set excelFile = nothing
'XLApp.Quit

End Sub

Re: Automatic overwrite excel file

fosuzuki — Wed, 16 Jan 2013 15:16:16 GMT

Try this:

excelFile.DisplayAlerts = False

curWorkBook.SaveAs filePath

excelFile.DisplayAlerts = True

Re: Automatic overwrite excel file

Wed, 16 Jan 2013 17:26:48 GMT

Fernando,

Thanks for this answer. I'll try it tomorrow.

Have i fil this rules in my macro under: 'save the file and quit excel'

or are these rules a complementation on the macro? if so: where.

Thanks in advance for your answer.

Gerrit

Re: Automatic overwrite excel file

fosuzuki — Wed, 16 Jan 2013 17:47:05 GMT

Hi Gerrit,

in your macro, replace the line:

curWorkBook.SaveAs filePath

with the 3 lines I posted.

This is just to disable the overwrite confirmation window that normally appears, and reenable it afterwards.

Re: Automatic overwrite excel file

Thu, 17 Jan 2013 06:28:34 GMT

Hi Fernando,

I've tried your solution.

Unfortunately, it does not work.

Excel ask me to overwrite the file because there is already a file with the same name.

Do you have any idea what the solution might be.

Thanks in advance for your answer.

Gerrit

Re: Automatic overwrite excel file

fosuzuki — Thu, 17 Jan 2013 19:31:17 GMT

Before the line

excelFile.DisplayAlerts = False

insert the code:

excelFile.Visible = false

I think that you don't need the line "excelFile.DisplayAlerts = True" which I suggested earlier. Right after that you kill the Excel app, so it make no difference.

Re: Automatic overwrite excel file

Fri, 18 Jan 2013 06:39:32 GMT

Good morning Fernando,

The macro I have now is the following:

Sub Exporteerd_naar_Excel_met_een_titel
     filePath = "C:\Voorraadanalyse.xls"
     Set excelFile = CreateObject("Excel.Application")
     excelFile.Visible = false
     Set curWorkBook = excelFile.WorkBooks.Add
     Set curSheet = curWorkBook.WorkSheets(1)
     Set tableToExport = ActiveDocument.GetSheetObject("CH01")
     Set chartProperties = tableToExport.GetProperties
     tableToExport.CopyTableToClipboard true
     chartCaption = tableToExport.GetCaption.Name.v
     curSheet.Range("A1") = chartCaption
     curSheet.Paste curSheet.Range("A2")
     excelFile.Visible = false
     excelFile.DisplayAlerts = False
     excelFile.Visible = false
     curWorkBook.SaveAs filePath
     curWorkBook.Close
     excelFile.Quit
     Set curWorkBook = nothing
     Set excelFile = nothing
End Sub

and this one is working.

Thanks for all your advise. It's great to me.

Gerrit