Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reference cells (2000 sr 1)

    this is piece of code, i dont understand why the macro insert data initialize into column B...
    Where i change the reference cells to init the insertion of data in column A?

    .................................
    For NbPages = 65 To 90
    LINEA = 1
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate "http://antfat/antfat/ewaf/elencoSbilanci;jsessionid=TkmfJxApZQ8Z6V4GAjSiStN= " & Chr(NbPages)
    Do Until IE.readyState = READYSTATE_COMPLETE
    DoEvents
    Loop

    Set maPageHtml = IE.document
    Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table

    For X = 2 To Htable.Length - 1

    If X = 2 And NbPages = 65 Then

    Y = 2

    Else
    Y = 2

    End If

    Set maTable = Htable(X)

    For I = Y To maTable.Rows.Length 'rows in table

    LINEA = LINEA + 1

    For J = 1 To maTable.Rows(I - 1).Cells.Length 'each cell in row
    Cells(LINEA, J) = maTable.Rows(I - 1).Cells(J - 1).innerText

    Next J

    Next I

    Next X

    DoEvents
    IE.Quit
    Set IE = Nothing

    Next NbPages

    ............................

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference cells (2000 sr 1)

    The inner loop that fills the cells is

    For J = 1 To maTable.Rows(I - 1).Cells.Length 'each cell in row
    Cells(LINEA, J) = maTable.Rows(I - 1).Cells(J - 1).innerText
    Next J

    It starts with J = 1, i.e. in the first column. But if the first column in the source table is blank, the code will fill column A with blanks.
    If so, you could suppress the blank column by changing the code to

    For J = 1 To maTable.Rows(I - 1).Cells.Length - 1 'each cell in row
    Cells(LINEA, J) = maTable.Rows(I - 1).Cells(J).innerText
    Next J

    But if the first column in a source table contains data, it won't be copied to Excel.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reference cells (2000 sr 1)

    perfect naturally!!!!!!!!!!!!!
    ... but if i would want to use, inseted cell reference, the range of cells.... similar:

    Range("a"+linea)= ...
    Range("b"+linea)= ...

    Because i would want to work during import on a single range or is possible to work with the cells reference?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference cells (2000 sr 1)

    Since you don't know the number of columns in advance, it is easier to work with Cells(row_number, column_number)

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reference cells (2000 sr 1)

    Hans have a disaster!!!!!!!!!!!!! naturally work wrong!

    Range("A" & LINEA) = maTable.Rows(i - 1).Cells(J).innerText
    Range("B" & LINEA) = maTable.Rows(i - 1).Cells(J).innerText

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference cells (2000 sr 1)

    So use the original code.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reference cells (2000 sr 1)

    ... No help me, now? <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference cells (2000 sr 1)

    There is no easy way to use Range("B" & LINEA) etc. here, so you should stick with using Cells(LINEA, J).

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reference cells (2000 sr 1)

    i Have found here Post: 499,832 your old code... i can use the same range posted...see in text file you attached me.

    ................
    Range("C" & lngRow) = UCase(.document.all.tags("table").Item(1).Rows(0). Cells(3).innerText)
    Range("D" & lngRow) = UCase(.document.all.tags("table").Item(1).Rows(1). Cells(3).innerText)
    Range("E" & lngRow) = UCase(.document.all.tags("table").Item(1).Rows(2). Cells(1).innerText)
    Range("F" & lngRow) = UCase(.document.all.tags("table").Item(1).Rows(3). Cells(1).innerText)
    Range("G" & lngRow) = UCase(.document.all.tags("table").Item(1).Rows(4). Cells(1).innerText)
    ...............

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference cells (2000 sr 1)

    Yes, but that is much less efficient than the loop using Cells(LINEA, J)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •