Pea and the Matress

ClimoC

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Hello

I have the following MONSTER code.

I ran a rudimentary version yesterday, which worked , perhaps just because the values being put into the IE text fields was supplied directly by me into the macro (see lines in red)
Rich (BB code): 
Public Sub onlinepublishing()
    Dim Shell As Object
    Dim IE As Object
    Dim etitle As Object
    Dim ptitle As Object
    Dim enumber As Object
    Dim snumber As Object
    Dim dur As Object
    Dim lintxh As Object
    Dim lintxm As Object
    Dim pchannel As Object
    Dim hlightflag As Object
    Dim dsel1 As Object
    Dim dsel2 As Object
    Dim dsel3 As Object
    Dim dsel4 As Object
    Dim dsel5 As Object
    Dim genr As Object
    Dim ageclas As Object
    Dim txtyp As Object
    Dim submit As Object
    
    Set Shell = CreateObject("Shell.Application")
    
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "http://privateworkwebsite.com" Then
        
            IE.Visible = True
            IE.navigate "http://privateworkwebsite.com/forms"
Do While IE.busy
        DoEvents
    Loop

Set ptitle = IE.document.all.Item("programmetitle")
Set etitle = IE.document.all.Item("episodetitle")
Set enumber = IE.document.all.Item("episodenumber")
Set snumber = IE.document.all.Item("seasonnumber")
Set dur = IE.document.all.Item("duration")
Set lintxh = IE.document.all.Item("lineartxhour")
Set lintxm = IE.document.all.Item("lineartxmin")
Set pchannel = IE.document.all.Item("parentchannel")
Set hlightflag = IE.document.all.Item("highlightflag")
Set dsel1 = IE.document.all.Item("dateselector1")
Set dsel2 = IE.document.all.Item("dateselector2")
Set dsel3 = IE.document.all.Item("dateselector3")
Set dsel4 = IE.document.all.Item("dateselector4")
Set dsel5 = IE.document.all.Item("dateselector5")
Set genr = IE.document.all.Item("genre")
Set ageclas = IE.document.all.Item("ageclassification")
Set txtyp = IE.document.all.Item("txtype")
Set submit = IE.document.getelementbyid("update")
Application.Speech.Speak "Uploading data"
ptitle.Value = "Testing1"
etitle.Value = "Testing2"
enumber.Value = "1"
snumber.Value = "1"
dur.Value = "105"
lintxh.Value = "21"
lintxm.Value = "30"
pchannel.Value = "2"
dsel1.Value = "01/10/09"
dsel2.Value = "02/10/09"
dsel3.Value = "03/10/09"
dsel4.Value = "04/10/09"
dsel5.Value = "05/10/09"
genr.Value = "4"
ageclas.Value = "2"
With submit
    .Click
    
End With

Do While IE.busy
        DoEvents
    Loop

With IE
    .navigate "http://privateworkwebsite.com/forms"
End With
    End If
    
    Next
 
    
End Sub

But that doesn't help because it needs to be grabbing entries from a work sheet and putting into the appropriate fields. So, I wrote this monster today, and now get "Object Required" where I've highlighted in Yellow (and assume will receive this error for all the other entries)
Rich (BB code): 
Public Sub onlinepublishing2()
    Dim Shell As Object
    Dim IE As Object
    Dim etitle As Object
    Dim ptitle As Object
    Dim enumber As Object
    Dim snumber As Object
    Dim dur As Object
    Dim lintxh As Object
    Dim lintxm As Object
    Dim pchannel As Object
    Dim hlightflag As Object
    Dim dsel1 As Object
    Dim dsel2 As Object
    Dim dsel3 As Object
    Dim dsel4 As Object
    Dim dsel5 As Object
    Dim genr As Object
    Dim ageclas As Object
    Dim txtyp As Object
    Dim submit As Object
    Dim Rowcount, R As Integer
    Dim TestcheckBox As Boolean
    Dim ProTitle, EpisoTitle, SeriNumber, DurationTime, ParChannel, _
    HFlag, Gen, Classification, EpisoNumber, TransStart, _
    TransEnd, LineTX, VisFrom, VisTo, Format As Variant
Application.Speech.Speak "Uploading data"
    
    Set Shell = CreateObject("Shell.Application")
    
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "http://privateworkwebsite.com"Then
        
            IE.Visible = True
            
            IE.navigate "http://privateworkwebsite.com/forms"
        Do While IE.busy
                DoEvents
            Loop
        
Sheets("Sheet1").Select
Rowcount = ActiveSheet.UsedRange.Rows.Count
For R = 6 To Rowcount
  TestcheckBox = Range("Sheet1!B" & R).Value
    If TestcheckBox = True Then
        ProTitle = Range("I" & R).Value
        EpisoTitle = Range("K" & R).Value
        SeriNumber = Range("J" & R).Value
        DurationTime = Range("S" & R).Value * 1440 + Format = (Time)
        TransStart = Range("M" & R).Value
        TransEnd = Range("M" & R).Value + 1
        LineTX = Left(Range("L" & R).Value, 9)
        VisFrom = Range("N" & R).Value
        VisTo = Range("P" & R).Value
        Select Case Range("R" & R).Value
            Case "SD to Both"
                Format = "1"
            Case "HD Only"
                Format = "3"
            Case "HD-HD & SD-SD"
                Format = "2"
            Case "SD Only"
                Format = "0"
        End Select
        Select Case Range("T" & R).Value
            Case ""
                Classification = "1"
            Case "U"
                Classification = "2"
            Case "PG"
                Classification = "3"
            Case "12"
                Classification = "4"
            Case "15"
                Classification = "5"
            Case "18"
                Classification = "6"
        End Select
        Select Case Range("D" & R).Value
            Case "Sky Arts 1"
                ParChannel = "103"
                Gen = "2"
            Case "Sky Arts 2"
                ParChannel = "256005"
                Gen = "2"
            Case "Sky Arts 1 HD"
                ParChannel = "104"
                Gen = "2"
            Case "Sky Arts 2 HD"
                ParChannel = "104"
                Gen = "2"
            Case "Sky Sports 1"
                ParChannel = "117"
                Gen = "5"
            Case "Sky Sports 2"
                ParChannel = "117"
                Gen = "5"
            Case "Sky Sports 3"
                ParChannel = "117"
                Gen = "5"
            Case "Sky Sports Xtra"
                ParChannel = "117"
                Gen = "5"
            Case "Sky Sports HD 1"
                ParChannel = "118"
                Gen = "5"
            Case "Sky Sports HD 2"
                ParChannel = "118"
                Gen = "5"
            Case "Sky Sports HD 3"
                ParChannel = "118"
                Gen = "5"
            Case "Sky Sports News"
                ParChannel = "119"
                Gen = "5"
            Case "SM Action"
                ParChannel = "114"
                Gen = "1"
            Case "SM Comedy"
                ParChannel = "113"
                Gen = "1"
            Case "SM Family"
                ParChannel = "115"
                Gen = "1"
            Case "SM Modern Greats"
                ParChannel = "109"
                Gen = "1"
            Case "SM Sci-Fi/Horror"
                ParChannel = "116"
                Gen = "1"
            Case "SM Premiere"
                ParChannel = "108"
                Gen = "1"
            Case "SM Drama"
                ParChannel = "112"
                Gen = "1"
            Case "SM Indie"
                ParChannel = "110"
                Gen = "1"
            Case "SM Action HD"
                ParChannel = "114"
                Gen = "1"
            Case "SM Comedy HD"
                ParChannel = "113"
                Gen = "1"
            Case "SM Family HD"
                ParChannel = "115"
                Gen = "1"
            Case "SM Modern Greats HD"
                ParChannel = "109"
                Gen = "1"
            Case "SM Sci-Fi/Horror HD"
                ParChannel = "116"
                Gen = "1"
            Case "SM Premiere HD"
                ParChannel = "108"
                Gen = "1"
            Case "SM Drama HD"
                ParChannel = "112"
                Gen = "1"
            Case "SM Screen 1"
                ParChannel = "112"
                Gen = "1"
            Case "SM Screen 1 HD"
                ParChannel = "112"
                Gen = "1"
            Case "SM Screen 2"
                ParChannel = "112"
                Gen = "1"
            Case "SM Screen 2 HD"
                ParChannel = "112"
                Gen = "1"
            Case "Sky News"
                ParChannel = "120"
                Gen = "3"
            Case "The History Channel"
                ParChannel = "124"
                Gen = "3"
            Case "Bio"
                ParChannel = "127"
                Gen = "3"
            Case "Bio HD"
                ParChannel = "257002"
                Gen = "3"
            Case "The History Channel HD"
                ParChannel = "125"
                Gen = "3"
            Case "Military History"
                ParChannel = "128"
                Gen = "3"
            Case "Crime & Investigation"
                ParChannel = "126"
                Gen = "3"
            Case "Crime & Investigation HD"
                ParChannel = "257003"
                Gen = "3"
            Case "Discovery Real Time"
                ParChannel = "256003"
                Gen = "3"
            Case "Discovery Shed"
                ParChannel = "285100"
                Gen = "3"
            Case "Discovery Turbo"
                ParChannel = "135"
                Gen = "3"
            Case "Discovery Science"
                ParChannel = "138"
                Gen = "3"
            Case "Discovery HD"
                ParChannel = "136"
                Gen = "3"
            Case "Home & Health"
                ParChannel = "256004"
                Gen = "3"
            Case "Discovery Knowledge"
                ParChannel = "137"
                Gen = "3"
            Case "Animal Planet"
                ParChannel = "139"
                Gen = "3"
            Case "Discovery"
                ParChannel = "135"
                Gen = "3"
            Case "Rush HD"
                ParChannel = "153"
                Gen = "5"
            Case "BET"
                ParChannel = "154"
                Gen = "2"
            Case "Disney Channel"
                ParChannel = "144"
                Gen = "4"
            Case "PlayHouse Disney"
                ParChannel = "145"
                Gen = "4"
            Case "Disney Cinema"
                ParChannel = "144"
                Gen = "4"
            Case "Disney Cinema HD"
                ParChannel = "262151"
                Gen = "4"
            Case "Disney XD"
                ParChannel = "293200"
                Gen = "4"
            Case "Cartoon Network"
                ParChannel = "147"
                Gen = "4"
            Case "Cartoonito"
                ParChannel = "256000"
                Gen = "4"
            Case "CN Too"
                ParChannel = "148"
                Gen = "4"
            Case "Boomerang"
                ParChannel = "146"
                Gen = "4"
            Case "Sky Real Lives"
                ParChannel = "105"
                Gen = "2"
            Case "Sky Real Lives HD"
                ParChannel = "106"
                Gen = "2"
            Case "Sky Real Lives 2"
                ParChannel = "105"
                Gen = "2"
            Case "Sky One"
                ParChannel = "101"
                Gen = "2"
            Case "Sky One HD"
                ParChannel = "102"
                Gen = "2"
            Case "Sky Travel"
                ParChannel = "100"
                Gen = "3"
            Case "MTV"
                ParChannel = "263050"
                Gen = "2"
            Case "MTV ONE"
                ParChannel = "263050"
                Gen = "2"
            Case "MTVN HD"
                ParChannel = "263050"
                Gen = "2"
            Case "FX"
                ParChannel = "142"
                Gen = "2"
            Case "FX HD"
                ParChannel = "143"
                Gen = "2"
            Case "FX +"
                ParChannel = "142"
                Gen = "2"
            Case "Home"
                ParChannel = "131"
                Gen = "2"
            Case "Really"
                ParChannel = "129"
                Gen = "2"
            Case "Good Food"
                ParChannel = "130"
                Gen = "2"
            Case "Travel Channel"
                ParChannel = "132"
                Gen = "2"
            Case "Eden"
                ParChannel = "268000"
                Gen = "2"
            Case "Yesterday"
                ParChannel = "256001"
                Gen = "3"
            Case "CNBC"
                ParChannel = "155"
                Gen = "0"
            Case "Baby TV"
                ParChannel = "261000"
                Gen = "4"
            Case "Channel 4"
                ParChannel = "257000"
                Gen = "2"
            Case "Channel 4 HD"
                ParChannel = "257001"
                Gen = "2"
            Case "E! Entertainment"
                ParChannel = "250150"
                Gen = "2"
            Case "The Style Network"
                ParChannel = "256002"
                Gen = "2"
            Case "Jetix"
                ParChannel = "152"
                Gen = "2"
            Case "ESPN"
                ParChannel = "134"
                Gen = "5"
            Case "ESPN Classic"
                ParChannel = "134"
                Gen = "5"
            Case "ESPN HD"
                ParChannel = "134"
                Gen = "5"
            Case "Nickelodeon"
                ParChannel = "149"
                Gen = "4"
            Case "Nick"
                ParChannel = "261001"
                Gen = "4"
            Case "Nick Jr"
                ParChannel = "150"
                Gen = "4"
            Case "Nick Jr 2"
                ParChannel = "150"
                Gen = "4"
            Case "NickToons"
                ParChannel = "151"
                Gen = "4"
            Case "National Geographic"
                ParChannel = "121"
                Gen = "3"
            Case "National Geographic HD"
                ParChannel = "123"
                Gen = "3"
            Case "National Geographic Wild"
                ParChannel = "122"
                Gen = "3"
            Case "National Geographic Wild HD"
                ParChannel = "283050"
                Gen = "3"
            Case "Comedy Central"
                ParChannel = "276000"
                Gen = "2"
            Case "Comedy Central X"
                ParChannel = "276000"
                Gen = "2"
            Case "Paramount"
                ParChannel = "276000"
                Gen = "2"
            Case "Eurosport HD"
                ParChannel = "287000"
                Gen = "5"
            Case "Motors TV"
                ParChannel = "268001"
                Gen = "2"
            Case "Sky"
                ParChannel = "-1"
                Gen = "1"
            Case ""
                ParChannel = "100"
                Gen = "1"
            Case "GOLD"
                ParChannel = "250152"
                Gen = "2"
            Case "Dave"
                ParChannel = "133"
                Gen = "2"
            Case "Alibi"
                ParChannel = "250151"
                Gen = "2"
            Case Else
                ParChannel = "100"
                Gen = "1"
        End Select
    
    
    Set ptitle = IE.document.all.Item("programmetitle")
    Set etitle = IE.document.all.Item("episodetitle")
    Set enumber = IE.document.all.Item("episodenumber")
    Set snumber = IE.document.all.Item("seasonnumber")
    Set dur = IE.document.all.Item("duration")
    Set lintxh = IE.document.all.Item("lineartxhour")
    Set lintxm = IE.document.all.Item("lineartxmin")
    Set pchannel = IE.document.all.Item("parentchannel")
    Set hlightflag = IE.document.all.Item("highlightflag")
    Set dsel1 = IE.document.all.Item("dateselector1")
    Set dsel2 = IE.document.all.Item("dateselector2")
    Set dsel3 = IE.document.all.Item("dateselector3")
    Set dsel4 = IE.document.all.Item("dateselector4")
    Set dsel5 = IE.document.all.Item("dateselector5")
    Set genr = IE.document.all.Item("genre")
    Set ageclas = IE.document.all.Item("ageclassification")
    Set txtyp = IE.document.all.Item("txtype")
    Set submit = IE.document.getelementbyid("update")
        ptitle.Value = ProTitle.Value
        etitle.Value = EpisoTitle.Value
        enumber.Value = EpisoNumber.Value
        snumber.Value = SeriNumber.Value
        dur.Value = DurationTime.Value
        pchannel.Value = ParChannel.Value
        dsel1.Value = TransStart.Value
        dsel2.Value = TransEnd.Value
        dsel3.Value = VisFrom.Value
        dsel4.Value = VisTo.Value
        dsel5.Value = LineTX.Value
        genr.Value = Gen.Value
        ageclas.Value = Classification.Value
        txtyp.Value = Format.Value
        With submit
            .Click
    
        End With

        Do While IE.busy
                DoEvents
            Loop

        With IE
            .navigate "http://privateworkwebsite.com/forms"
        End With
    End If
        
Next
End If
Next
    
End Sub

What is wrong? Isn't the syntax and instructions correct? What have I missed??? Is it something to do with my 'For each Row...' or what?

Any help would be much appreciated.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Your code seems to make the possibly incorrect assumption that the IE object coming out of the For Each IE in shell.Windows loop is an internet explorer, but that doesn't necessarily have to be so. I would set a breakpoint on the line where you currently encounter the error and then inspect the IE object.

Hope this helped,
Rolf
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
sometimes problem with vba (Error 400)
Replies
5
Views
590
Domenic
D
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
197
chazrab
C
R
  • Solved
Export excel file as PDF into a targeted pc folder based on cell value
Replies
2
Views
197
Ramadan2512
R
M
  • Solved
Navigate Userforms
Replies
6
Views
267
My Aswer Is This
My Aswer Is This
S
  • Question Question
VBA support : need to remove extra space
Replies
2
Views
173
sksanjeev786
S

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top