The intention to put the statement above the excel-stuff was just to separate them and to keep a better overview over the code.
And of course this hasn't any influence as far as there are no further copy-statements between it and the paste-statement - this meant in general you could mix up qlikview-statements and others. On the other hand it's a very good example of confusing the user by highlighting the copy-statement instead of doing nothing or even better of displaying the real error - which is a store-error respecitively the dialog-question (will be only shown by executing the routine with a button from the UI) that the file already exists and if you want to overwrite them.
If you don't get an understandable error you could also use On Error Resume Next within your routine to force to continue even after an error and then to query the error per err.number & ' - ' & err.description.
For this I have the routine a bit extended to a storing-sub-routine which will at first delete an existing older file and storing the new one.
SUB ExcelFile
set doc = ActiveDocument
vPath = "C:\Users\Mindaugasb\Desktop\"
reportFile = vPath & "Test" & ".xlsx"
doc.ClearAll
doc.GetSheetObject("kg").CopyBitmapToClipboard
SET XLApp = CreateObject("Excel.Application")
XLApp.Visible = TRUE
SET XLDoc = XLApp.Workbooks.Add
Set XLSheet = XLDoc.ActiveSheet
XLSheet.Range("A1").Select
XLSheet.Paste
call OverwriteExistingFile(reportFile, XLDoc)
'XLDoc.Saveas reportFile
XLApp.Quit
END SUB
sub OverwriteExistingFile(File, Excel)
set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile(File)
Excel.Saveas File
end sub
- Marcus
