are creating problems, stop hanging execution of the macro.
Sulucion:
To make way for the execution have put the MsgBox (), which you comment on, if this is uncomment the macro generates runs smoothly, but as it is annoying to be solving the message rremplasala by:
I enclosed a demo with a macro that should do what you want - I modified it a little, but it works (to test it, excel file C:\test\test.xls with at least one sheet named "OldSheetName" is required). It does NOT add any worksheets of course, but that was also not proposed by your code fragment.
So on the whole the thing should work. Of course, sometimes in QV9, when a macro isn't executing correctly, it's necessary to put the mystical "WaitForIdle" here and there - but noone could still tell me when and where (Support: once told me "every time when a sheet object is accessed").
Thanks for your valuable reply,We are creating five sheets and then mail it through macro, i am putting full code at bottom
sub test2
set XLApp = CreateObject("Excel.Application") XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add
dim a,text,tm a=Array("CH18","CH14","CH26","CH30","CH31") '"CH27")
for i = 0 to 4 b=a(i) m = i m = i + 1
if b="CH18" then text = "DailyTracker" else if b="CH14" then text = "AR" else if b="CH26" then text = "ValSales" else if b="CH30" then 'CH27 text = "QtySales" else if b="CH31" then 'CH27 text = "CustomerQtySales" end if end if end if end if end if ' msgbox text
SheetIt XLDoc,"Sheet" &m,b,text
NEXT tm=second(time()) strFile = "C:\ToSent\" & getVariable("User1") &tm & ".xls"
XLDoc.SaveAs strfile XLDoc.Close XLApp.Quit
sendMail strFile rem ** let QV sleep for 10 seconds ** ActiveDocument.GetApplication.Sleep 10000
function SheetIt(ExcelDoc,SheetName,ChartName,Category) ExcelDoc.Sheets(SheetName).activate ExcelDoc.Sheets(SheetName).activate msgbox SheetName ExcelDoc.Sheets(SheetName).activate set obj = ActiveDocument.getsheetobject(ChartName)
rem ** let QV sleep for 10 seconds ** ActiveDocument.GetApplication.Sleep 10000
obj.CopyTableToClipboard true
ExcelDoc.Sheets(SheetName).paste ExcelDoc.Sheets(SheetName).cells.select ExcelDoc.Sheets(SheetName).cells.mergecells = false ExcelDoc.Sheets(SheetName).Cells.EntireRow.RowHeight = 12.75 ExcelDoc.Sheets(SheetName).Cells.EntireColumn.AutoFit msgbox "Category " & Category ExcelDoc.Sheets(SheetName).name = Category end function
function sendMail(reportFiles) 'msgbox reportFiles Dim objEmail Dim strMailTo
Const cdoSendUsingPort = 2 ' Send the message using SMTP 'Const cdoBasicAuth = 1 ' Clear-text authentication Const cdoTimeout = 60 ' Timeout for SMTP in seconds 'Const cdoPort = 25 ' Port number for SMTP
Const cdoNTLM = 2
'Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay
'Get Selected values from field "mailto" Set objSelected = ActiveDocument.Fields("mailto").GetSelectedValues
if objSelected.Count = 0 then ' Nothing Selected 'msgbox ("No e-mail reciepient selected") exit function else 'Sending mail Set objEmail = CreateObject("CDO.Message") Set objConf = objEmail.Configuration Set objFlds = objConf.Fields
With objFlds '--------------------------------------------------------------------- ' SMTP server details .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = getVariable("mailServer") .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoNTLM .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = getVariable("SMTPport") .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout .Update '--------------------------------------------------------------------- End With
'For i = 0 to objSelected.Count-1 ' create mailTo list ' strMailTo = strMailTo & objSelected.item(i).Text & ";" ' msgbox "in for loop" & strMailTo 'next strMailTo = objSelected.item(i).Text
'strMailTo = left(strMailTo,len(strMailTo)-1) ' remove the last ; in list 'msgbox strMailTo objEmail.To = strMailTo objEmail.From = """QlikView Admin"" <Qlik@piind.com>" objEmail.Subject = getVariable("mailSubject") objEmail.TextBody = getVariable("Body")
' for each item in reportFiles ' Add selected reports to mail ' if item <>"" then 'msgbox "befire attachemnt" & reportFiles objEmail.AddAttachment reportFiles ' end if ' next objEmail.Send
Set objFlds = Nothing Set objConf = Nothing Set objEmail = Nothing end if rem ** let QV sleep for 10 seconds ** 'ActiveDocument.GetApplication.Sleep 10000
'msgbox ("Mail Sent")
end function
function deleteReport(rFile) set oFile = createObject("Scripting.FileSystemObject")
currentStatus = oFile.FileExists(rFile)
if currentStatus = true then oFile.DeleteFile(rFile) end if set oFile = Nothing end function
function getVariable(varName)
set v = ActiveDocument.Variables(varName) getVariable = v.GetContent.String end function
function getText(obj) set mytext = ActiveDocument.GetSheetObject(obj) prop = mytext.GetProperties getText = prop.Layout.Text.v end function
sub selectcuser ActiveDocument.Save 'set v2 = ActiveDocument.Variables("test") 'ActiveDocument.Fields("Email").Select v2.GetContent.String
'if tym > "08:30" and tym < "09:00" then Set Euser = ActiveDocument.Fields("Email").GetPossibleValues
For l=0 to Euser.Count -1 'msgbox Euser.Item(l).Text ActiveDocument.Fields("mailto").Select Euser.Item(l).Text 'Select v2.GetContent.String 'Select Euser.Item(l).Text ' ActiveDocument.Fields("Email").Select Euser.Item(l).Text 'Select Euser.Item(l).Text 'Select Euser.Item(l).Text call test2 Next 'end if
the full code would have been helpful earlier, but at first quick sight I would say it should be ok and working (though I wonder why you activate the sheet thrice).
But try to replace the "Sleep" with ActiveDocument.GetApplication.WairForIdle
I can hardly believe that Sleep worked and ActiveDocument.GetApplication.WaitForIdle does not work! Did you really type it correctly WaitForIdle and NOT as in your post: WaitFor Idle ?
Can't imagine that it's a memory issue, but not impossible, 9.0 is buggy enough. I'd hand it to the support.
Well. I'm out of ideas now and it's hard to guess without the whole app. As said I would give it to the support.. Perhaps someone else here has a heureka ..
