Nestled End If and End With possible issue

[TheBarman]

Hello,

I have a VBA to save multiple URL’s as a text file.
Column B is the 8 URL’s and column C is the 8 text file names to be saved.
When I run the code, it creates 8 text files, but the eight text files are an exact replica of the last URL used.

Example: If I run only two URL’s both files are the same data from URL #2
If I run eight URL’s, eight files are the same data from URL #8

I think it may have to do with the nestled "End If" and "End With" and where they sit in the code.
But I don’t know why each text file is not unique to the individual URL’s.

CODE:

Public Sub Save_As_TXT()

    Dim IE As Object
    Dim URL As String
    Dim filename As String
    Dim lRow As Long, LastRow As Long
    Dim Rng1 As Range, Rng2 As Range, cell1 As Range, cell2 As Range


Sheets("URL").Select
With Worksheets("URL")                                                   'Sheet with URL & .txt file names
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row                        ' last row in column B
    Set Rng1 = .Range("B2:B" & LastRow)                                     ' set the dynamic URL range to be searched
    Set Rng2 = .Range("C2:C" & LastRow)                                     ' set the dynamic .txt range to be searched


    folder = "C:\Users\Signature\Documents\Form Guide\2019\February\"    'File location of saved .txt files


    Set IE = CreateObject("InternetExplorer.Application")


'Load webpage
    With IE
        .Visible = True
        For Each cell1 In Rng1                                          'Cell one range is the new URL to load("B2:B" & lastRow)
           If cell1.Value <> "" Then
               .navigate cell1.Value
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        While .Document.ReadyState <> "complete": DoEvents: Wend
        Application.Wait (Now + TimeValue("0:00:10"))                   'buffer to give time for the website to load
        DoEvents
        Do: Loop Until IE.ReadyState = READYSTATE_COMPLETE




'Save to the text file
           For Each cell2 In Rng2    '("H2:H" & lRow)
           If cell2.Value <> "" Then
        Open folder & cell2.Value & ".txt" For Output As #1             'Cell two range is new .txt file name'("C2:C" & lastRow)
        Print #1, .Document.body.innerText
        Close #1
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        While .Document.ReadyState <> "complete": DoEvents: Wend
        DoEvents
        Do: Loop Until IE.ReadyState = READYSTATE_COMPLETE


End If
    Next
        End If
            Next
'***********************   Clean up & Quit   *******************************
    With IE
        .Visible = False
    End With


' Clean up
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
 
    Application.StatusBar = ""


'.Quit
End With
End With




End Sub

 

[DanteAmor]

Try this:

Public Sub Save_As_TXT()


    Dim IE As Object
    Dim URL As String
    Dim lRow As Long, LastRow As Long
    Dim Rng1 As Range, Rng2 As Range, cell1 As Range, cell2 As Range
    Dim FileTxt As String


    Sheets("URL").Select
    With Worksheets("URL")                                                   'Sheet with URL & .txt file names
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row                        ' last row in column B
        Set Rng1 = .Range("B2:B" & LastRow)                                     ' set the dynamic URL range to be searched
    
        folder = "C:\Users\Signature\Documents\Form Guide\2019\February\"    'File location of saved .txt files
        
        Set IE = CreateObject("InternetExplorer.Application")
    
        'Load webpage
        With IE
            .Visible = True
            For Each cell1 In Rng1                                          'Cell one range is the new URL to load("B2:B" & lastRow)
               If cell1.Value <> "" Then
                   .navigate cell1.Value
                    While .Busy Or .ReadyState <> 4: DoEvents: Wend
                    While .Document.ReadyState <> "complete": DoEvents: Wend
                    Application.Wait (Now + TimeValue("0:00:10"))                   'buffer to give time for the website to load
                    DoEvents
                    'Do: Loop Until IE.ReadyState = READYSTATE_COMPLETE
    
                    'Save to the text file
                    FileTxt = cell1.Offset(0, 1).Value
                    Open folder & FileTxt & ".txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]              'Cell two range is new .txt file name'("C2:C" & lastRow)
                    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , .Document.body.innerText
                    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
                End If
            Next
            '***********************   Clean up & Quit   *******************************
            With IE
                .Visible = False
            End With
    
            ' Clean up
           Set IE = Nothing
           Set objElement = Nothing
           Set objCollection = Nothing
        
           Application.StatusBar = ""
        '.Quit
        End With
    End With


End Sub

 

[Jeffrey Mahoney]

These are my suggestions based on what has worked for me. Take them as you will.

For every new FOR, DO, WITH statement, indent all the code that falls into that range. In other words, all code that falls between a FOR and NEXT pairs should never have any code to the left of it. This really helps to identify the beginning and ending of a loop or WITH.

I try to use WITH statements sparingly. I set up the variables I need to use and then end it quickly so I can use another WITH statement later.

Try to stay away from crossing your FOR, DO, and WITH statements. If you start one FOR statement, you can have more FOR/NEXT statements in between the first pair of FOR/NEXT statement, but don't end a new FOR/NEXT statement outside the first. It can get confusing when using WITH statements within other WITH statements. Do Loops are even worse

When you end a FOR/NEXT Statement, use the variable name in the NEXT command; as in:

FOR each cel in rng
  ...
  ...
  Code here
NEXT cel

I altered the code to what I think could help. I can't test it so please check my work.

Jeff

Public Sub Save_As_TXT()


  Dim IE As Object
  Dim URL As String
  Dim filename As String
  Dim lRow As Long, LastRow As Long
  Dim Rng1 As Range, Rng2 As Range, cell1 As Range, cell2 As Range
  Dim URLSht As Worksheet


  Set URLSht = Sheets("URL")
  URLSht.Activate
  With URLSht                                                   'Sheet with URL & .txt file names
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row                        ' last row in column B
    Set Rng1 = .Range("B2:B" & LastRow)                                     ' set the dynamic URL range to be searched
    Set Rng2 = .Range("C2:C" & LastRow)                                     ' set the dynamic .txt range to be searched
  End With


  folder = "C:\Users\Signature\Documents\Form Guide\2019\February\"    'File location of saved .txt files


  Set IE = CreateObject("InternetExplorer.Application")




  'Load webpage
  With IE
    .Visible = True
    For Each cell1 In Rng1                                          'Cell one range is the new URL to load("B2:B" & lastRow)
      If cell1.Value <> "" Then
        .navigate cell1.Value
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        While .Document.ReadyState <> "complete": DoEvents: Wend
        Application.Wait (Now + TimeValue("0:00:10"))                   'buffer to give time for the website to load
        DoEvents
        Do: Loop Until IE.ReadyState = READYSTATE_COMPLETE


        'Save to the text file
        For Each cell2 In Rng2    '("H2:H" & lRow)
          If cell2.Value <> "" Then
            Open folder & cell2.Value & ".txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]              'Cell two range is new .txt file name'("C2:C" & lastRow)
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , .Document.body.innerText
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
            While .Busy Or .ReadyState <> 4: DoEvents: Wend
            While .Document.ReadyState <> "complete": DoEvents: Wend
            DoEvents
            Do: Loop Until IE.ReadyState = READYSTATE_COMPLETE
          End If
        Next cell2
      End If
    Next cell1
    
    .Visible = False
  End With
'***********************   Clean up & Quit   *******************************




  ' Clean up
  Set IE = Nothing
  Set objElement = Nothing
  Set objCollection = Nothing


  Application.StatusBar = ""




End Sub

 

[TheBarman]

Jeffrey Mahoney - Hi Jeff, unfortunately, it was the same result of all files being a replica of the last URL.
However, the code from Dante was successful. Thank you for your time.

 

[TheBarman]

DanteAmor - Hi Dante, Thank you very much for your code - IT WORKED!
Thank you for your quick response.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.