We help IT Professionals succeed at work.

Xlsheet.delete

jim25
jim25 asked
on
771 Views
Last Modified: 2012-08-13
Hi all

I have the below code and I want to delete the active worksheet that is being written to if a certain criteria is not met. I am currently using xlsheet.delete but this does not work. Can someone tell me how to do it? Many thanks!

LANGUAGE=VBSCRIPT
[PCOMM SCRIPT HEADER]
LANGUAGE = VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
Option Explicit
autECLSession.SetConnectionByName (ThisSessionName)

Robinson_names
MsgBox "Done!"

Sub Robinson_names()

   Dim FSO, xlFile, TS,row1,i,stamp,x,eloise, tempstr,lngrow, row,finish,gender,start,first_Name,accommodation(10000),xlApp,xlBook,xlSheet,date_Time,enternum,From_date(1000000),To_Date(10000),eloise_id(1000000),surname
     
   Set xlApp = CreateObject("Excel.Application")
   xlFile = "C:\Program Files\IBM\Client Access\Emulator\private\Robinson_Names.xls"
   set xlBook = xlApp.Workbooks.Open(xlFile)
   set xlSheet = xlBook.Worksheets.Add
   'Msgbox Day(Date()) & "-" & MonthName(month(Date()),true) &" "& Right("0"&Hour(Time()),2) & "¦" & Right("0"&Minute(Time()),2) & "¦" & Right("0"&Second(Time()),2)
   xlSheet.name = Day(Date()) & "-" & MonthName(month(Date()),true) &" "& Right("0"&Hour(Time()),2) & "¦" & Right("0"&Minute(Time()),2) & "¦" & Right("0"&Second(Time()),2)
   xlSheet.Range("A1:F1") = Array("From Date","To Date","First Name", "Surname", "Gender","Accommodation")

   enterNum = 0
   row1 = 9
   lngrow = 2
   Start =  (Time())
   tempstr = 66402
   
    autECLSession.autECLPS.SendKeys "TC745 [enter]"
    Wait
    autECLSession.autECLPS.WaitForAttrib 6,34,"10","3c",3,10000
    PutText 11, 35, tempstr, autECLSession
    wait
    PutText 15, 35, "010707", autECLSession
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
Do    
    If row1 > 22 Then
     autECLSession.autECLPS.SendKeys "[enter]"
     wait
     row1 = 9
    End If
       
    autECLSession.autECLPS.SetCursorPos row1, 14
    wait
    If autECLSession.autECLPS.GetText(row1, 16, 1) = " " Then Exit Do
    wait
    From_Date(x) = autECLSession.autECLPS.GetText(row1, 2, 5)
    To_DAte(x) = autECLSession.autECLPS.GetText(row1, 8, 5)
    wait
     
    autECLSession.autECLPS.SendKeys "x"
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
    wait
    ELOISE_ID(x) = autECLSession.autECLPS.GetText(6, 50, 6)
    x = x + 1
    wait
    autECLSession.autECLPS.SendKeys "[pf12]"
    row1 = row1 + 1
    wait
Loop
    x = x - 1
    wait
    autECLSession.autECLPS.SendKeys "[pf7]"
    wait
    autECLSession.autECLPS.SendKeys "TC700"
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
Do
    wait
    'autECLSession.autECLPS.WaitForAttrib 5,37,"10","3c",3,10000

    PutText 12, 38, ELOISE_ID(x), autECLSession
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
    wait
    wait
    autECLSession.autECLPS.SendKeys "07"
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
    wait
 'Title = Trim(autECLSession.autECLPS.GetText(5, 61, 12))
 Surname = Trim(autECLSession.autECLPS.GetText(4, 38, 18))
 First_Name = Trim(autECLSession.autECLPS.GetText(5, 38, 11))
    wait
    GENDER = autECLSession.autECLPS.GetText(21, 38, 1)
    wait
    Row = 3
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
    wait
    autECLSession.autECLPS.SendKeys "06"
    wait
    autECLSession.autECLPS.SendKeys "[enter]"
    wait

    Do Until autECLSession.autECLPS.GetText(Row, 27, 5)  = From_Date(x) AND autECLSession.autECLPS.GetText(Row, 35, 5)  = To_Date(x)
      autECLSession.autECLPS.SendKeys "[down]"
      Row = autECLSession.autECLPS.CursorPosRow
    Loop
          
    wait

    ACCOMMODATION(x) = trim(autECLSession.autECLPS.GetText(Row, 44, 13))

    If UCASE(Accommodation(x)) <> "RESIDENCE" Then
      Eloise = autECLSession.autECLPS.GetText(1, 8, 6)
      tempstr = InputBox("Check the student with the below Eloise number for their correct accommodation!", ,ELOISE)
      xlsheet.delete
      xlBook.Save
      xlBook.Close
      xlApp.quit
      Exit Sub
    End If

    wait
    With xlSheet
      .Range(.Cells(lngRow, 1), .Cells(lngRow, 6)) = array(From_date(x),To_DAte(x),First_Name,Surname,Gender,accommodation(x))
    End With
    lngRow = lngRow + 1
    autECLSession.autECLPS.SendKeys "[pf2]"
    x = x - 1  
Loop Until x = -1
   
Finish = FormatDateTime(Time()-Start,3)
Msgbox Finish

xlBook.Save
xlBook.Close
xlApp.quit
End Sub

Function Wait()
 autECLSession.autECLOIA.WaitForAppAvailable
 autECLSession.autECLOIA.WaitForInputReady
End Function
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
>I am currently using xlsheet.delete but this does not work.

In what way. most likely webtubbs gave you the right answer.

Leon

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.