Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Try this:
excelFile.DisplayAlerts = False
curWorkBook.SaveAs filePath
excelFile.DisplayAlerts = True
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
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.
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
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.
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