Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
For a couple of months I have been having an app in QlikView that generates an Excel file after reloading the data via a macro.
Today the app kept failing. After the reload is completed the macro window pops up and doesn`t give an error.
Just once I have received an error like "Inconsistency Type B".
Below you have the macro, maybe you can figure something out..
sub Excel
filePath = "E:\Path\file.xlsx"
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
Set curWorkBook = excelFile.WorkBooks.Add
aSheetObj=Array("CH02")
for i=0 to UBound(aSheetObj)
excelFile.Sheets.Add
Set curSheet = excelFile.ActiveSheet
curSheet.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
curSheet.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
curSheet.Rows("1:1").Select
excelFile.Selection.Font.Bold = True
curSheet.Cells.Select
excelFile.Selection.Columns.AutoFit
curSheet.Range("A1").Select
curSheet.Name=left(sCaption,30)
set curSheet=Nothing
next
excelFile.Visible = true
excelFile.DisplayAlerts = False
curWorkBook.WorkSheets("Sheet1").Delete
curWorkBook.SaveAs filePath, 56
curWorkBook.Close
excelFile.Quit
Set curWorkBook = nothing
Set excelFile = nothing
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "-"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "-"
msgConf.Fields.Update
'
'objMsg.To = "-"
objMsg.CC = "-"
objMsg.From = "-"
objMsg.Subject = "-"
objMsg.HTMLBody = "-"
objMsg.AddAttachment "E:\Path\file.xlsx"
objMsg.Sender = "-"
Set objMsg.Configuration = msgConf
objMsg.Send
ActiveDocument.GetApplication.Sleep 5000
Set objMsg = nothing
Set msgConf = nothing
Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile "E:\Path\file.xlsx"
ActiveDocument.GetApplication.Quit
END SUB
You can find the line containing the error by putting a line like: msgbox "no error" in your code.
When the code runs, if you see the message there was no error up to that point. So you move the line of code down and run again. At some point, you will not see the message. The error was in the line above.
You can also put you code into Excel and debug.
Thanks for the reply!
This is an odd situation. Right now (after business hours) everything works OK. I think this has something to do with server occupation level but really it shouldn`t because the server has plenty of resources (RAM / Storage / CPU).
Any other ideas?