1 Reply Latest reply: Sep 8, 2010 10:55 AM by jonathan.bowen RSS

    'File Save As' including a Field Value & date stamp in file name

    jonathan.bowen

      Hi,
      I'm a real newbie to this so apologies from the outset if the copy/paste below goes screwy or if the font is a bit iffy!
      Scenario: I am running a batch file that opens a qvw that then creates various tabs, including many charts & graphs that I am then exporting to Powerpoint via a macro; again across many different slides into various positions, all of which works fine [sample code below & thanks to the many contributors whose ideas have helped create it].
      However, I want to save my qvw file as "X:\xxx\xxx_([Contributor Name])_(Date).qvw" where the [Contributor Name] is a field that has been loaded to Qlikview and that I am making use of internally within a text box. Also, I want to include the date [yyyymmdd] in the file name. I have tried various syntax options & searched the forum for ideas but I've so far drawn a blank on how to do this!!
      Can anyone please HELP!?

      Sample code:


      sub powerpoint
      Set PPApp = CreateObject("Powerpoint.Application")
      PPApp.Visible = True
      Set PPPres = PPApp.Presentations.Open ("C:\xxx\xxx.ppt")
      PPApp.Visible = True
      ActiveDocument.Reload
      ActiveDocument.GetApplication.WaitForIdle
      set s=ActiveDocument.Sheets("Summary")
      ActiveDocument.Sheets("Summary").Activate
      ActiveDocument.GetApplication.WaitForIdle
      ActiveDocument.GetSheetObject("CH28").Restore
      ActiveDocument.GetSheetObject("CH28").CopyBitmapToClipboard
      PPApp.Visible = True
      Set PPSlide = PPPres.Slides(11)
      PPSlide.Shapes.Paste
      PPSlide.Shapes(PPSlide.Shapes.Count).LockAspectRatio = -3
      PPSlide.Shapes(PPSlide.Shapes.Count).Left = 54.1383
      PPSlide.Shapes(PPSlide.Shapes.Count).Top = 286.2812
      PPSlide.Shapes(PPSlide.Shapes.Count).Height = 187.0748
      PPSlide.Shapes(PPSlide.Shapes.Count).Width = 284.297
      ActiveDocument.GetSheetObject("CH28").Minimize
      ActiveDocument.GetApplication.WaitForIdle
      set PPApp = nothing
      set PPPRes = nothing
      set PPSlide = nothing
      ActiveDocument.SaveAs "C:\xxx\xxx_([Contributor Name])_(Date).qvw"
      ActiveDocument.GetApplication.Quit
      end sub


      Also, has anyone managed to automate the copying of tables from qvw to PowerPoint; as I just can't get it to paste and my macro stops at the point of paste, whatever syntax I use; even though, if I then Ctrl-V in PowerPoint it places the data perfectly. [So far I have got 'round this by splitting my tables out into 1 line text boxes that I then seperately weld back into PowerPoint via macro!]

      Thanks in advance!