PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
grab info-VBForums
Results 1 to 10 of 10

Thread: grab info

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    5

    grab info

    Good Afternoon Everyone

    My english is not perfect... sorry about that

    In my work we have a database of clients in html format so we can consult that information with clients numbers.

    What im trying is to import client info. to excel page:

    I can do that when i go to data>from web>put the link>select the table i want and clickin on import.

    But i need to do these to thousands of links, can anyone tell me how can i do these to all of my links when i have them in column C, without do that method one-on-one.

    Can anyone help me please?

    Ty a lot

    (and sorry if these is not the right place)
    Last edited by JudahRaion; Oct 13th, 2011 at 07:26 AM. Reason: tryin to change tags

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,265

    Re: grab info

    Welcome to VBForums

    As your question doesn't involve a proper database (SQL Server etc) the 'Database Development' forum isn't really the best place... so I've moved this thread to our 'Office Development/VBA' forum.

  3. #3
    Registered User
    Join Date
    Dec 2004
    Posts
    24,634

    Re: grab info

    try recording a macro of getting data from one source, then convert to a loop for each source

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    5

    Re: grab info

    i can do that and the query i made is these:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Sheets("Abertura Conta").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
    "http://80_Clientes/PagImpCli.asp?nCliente=500013"
    Sheets("Sheet2").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://80_Clientes/PagImpCli.asp?nCliente=500013", _
    Destination:=Range("$A$1"))
    .Name = "PagImpCli.asp?nCliente=500013"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "4"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub

    First problem: i want these query to look automatically the hyperlinks for column C in the "sheet1"
    Second problem: paste all the information in sequencial info. With these query he paste in A1 and ends in A64, and in A65 i want to paste all information about next client...

    I don't know if these can help you to help me...

    If i can do something more to help about these question.

    Thanks anyway for tryin to help me.

  5. #5
    Registered User
    Join Date
    Dec 2004
    Posts
    24,634

    Re: grab info

    First problem: i want these query to look automatically the hyperlinks for column C in the "sheet1"
    try like this to get all the first hyperlink in column c
    vb Code:
    1. lastrow = sheets("sheet1").cells(rows.count, 3).end(xlup).row
    2. for each c in sheets("sheet1").range("c1:c" & lastrow)  ' all cells in column c
    3. nextrow = sheets("sheet2").cells(rows.count, 1)row + 1
    4. With sheets("sheet2").QueryTables.Add(Connection:= _
    5.    c.hyperlinks(1), _
    6.    Destination:=.Range("$A$" & nextrow))
    7.    .Name = mid(c.hyperlinks(1), instrrev(c.hyperlinks(1), "\") + 1)
    8.    .FieldNames = True
    9.    .RowNumbers = False
    10.    .FillAdjacentFormulas = False
    11.    .PreserveFormatting = True
    12.    .RefreshOnFileOpen = False
    13.    .BackgroundQuery = True
    14.    .RefreshStyle = xlInsertDeleteCells
    15.    .SavePassword = False
    16.    .SaveData = True
    17.    .AdjustColumnWidth = True
    18.    .RefreshPeriod = 0
    19.    .WebSelectionType = xlSpecifiedTables
    20.    .WebFormatting = xlWebFormattingNone
    21.    .WebTables = "4"
    22.    .WebPreFormattedTextToColumns = True
    23.    .WebConsecutiveDelimitersAsOne = True
    24.    .WebSingleBlockTextImport = False
    25.    .WebDisableDateRecognition = False
    26.    .WebDisableRedirections = False
    27.    .Refresh BackgroundQuery:=False
    28. End With
    29. next
    i am sure some of this may not be correct, you will have to test and fix as needed, you may want to use some other value for the name of each querytable
    it would probably be better to set worksheet object variables for each sheet
    you can probably remove some of the querytable properties from the list, if they are the default values for the property

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    5

    Re: grab info

    can't run these macro because "Destination:=.Range("$A$" & nextrow)" give me a error: invalid or unqualified reference. And i dont know how to solve.

    HTML Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
       lastrow = Sheets("Abertura Conta").Cells(Rows.Count, 3).End(xlUp).Row
       For Each c In Sheets("Abertura Conta").Range("C1:C" & lastrow)  ' all cells in column c
       nextrow = Sheets("Sheet2").Cells(Rows.Count, 1).Row + 1
       With Sheets("Sheet2").QueryTables.Add(Connection:= _
          c.Hyperlinks(1), _
          Destination:=.Range("$A$" & nextrow))
          .Name = Mid(c.Hyperlinks(1), InStrRev(c.Hyperlinks(1), "\") + 1)
          .FieldNames = True
          .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlSpecifiedTables
         .WebFormatting = xlWebFormattingNone
         .WebTables = "4"
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
    
    End With
    Next
    End Sub
    Can u help me please. Sorry with all these questions.

  7. #7
    Registered User
    Join Date
    Dec 2004
    Posts
    24,634

    Re: grab info

    what is the value of next row at that point?
    try removing the leading . from the range, to see if it makes any difference

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    5

    Re: grab info

    I delete the point and then give me the next error: Run-time error '1004': Method 'Range' of object '_Global' failed.

    Code:
       With Sheets("Sheet2").QueryTables.Add(Connection:= _
          c.Hyperlinks(1), _
          Destination:=Range("$A$" & nextrow))

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    5

    Re: grab info

    I don't know if these can help but i use these

    Code:
    Sub Open_Hyperlinks()
    Dim i, LastRow
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
    If Cells(i, "A").Hyperlinks.Count > 0 Then
    Cells(i, "A").Hyperlinks(1).Follow
    End If
    Next
    End Sub
    When i need to open all Hyperlinks in a column "A"

  10. #10
    Registered User
    Join Date
    Dec 2004
    Posts
    24,634

    Re: grab info

    When i need to open all Hyperlinks in a column "A"
    but that will not work to put the linked data into querytables?

    is nextrow a valid row number?

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width