6 Replies Latest reply: Jan 18, 2013 1:39 AM by gemarohb RSS

    Automatic overwrite excel file

      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
          Fernando Suzuki

          Try this:

           

          excelFile.DisplayAlerts = False

          curWorkBook.SaveAs filePath

          excelFile.DisplayAlerts = True

            • Re: Automatic overwrite excel file

              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
                  Fernando Suzuki

                  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

                      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
                          Fernando Suzuki

                          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

                              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