Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Grab info

  1. #1

    Grab info



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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; 2011-10-13 at 02:24 PM. Reason: error

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    So do you have thousands of web pages that you need to import? Sorry, I'm just trying to get a feel for where the issue is... Can you make a fake webpage with sample data that you can upload here for us?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    RE: Grab info

    i can do that and the query i made is these:
    Code:
    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.
    Last edited by Ken Puls; 2011-10-20 at 04:46 PM. Reason: Added code tags

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    I can't test this, as I can't access the page, but try this. It should try and grab the hyperlink data for all cells in column C:

    Code:
    Sub GetHyperlinks()
        Dim cl As Range
        Dim rng As Range
        With Worksheets("Abertura Conta")
            Set rng = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
        End With
        For Each cl In rng
            Call RetrieveHyperlinkdata(cl)
        Next cl
    End Sub
    Sub RetrieveHyperlinkdata(rngSource As String)
        Dim rngTarget As Range
        With Worksheets("Sheet2")
            Set rngTarget = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
            With .QueryTables.Add(Connection:="URL;" & rngSource.FormulaR1C1, _
                                  Destination:=Range(rngTarget.Address))
                .Name = Right(rngSource.FormulaR1C1, _
                              Len(rngSource.FormulaR1C1) - _
                              InStr(1, rngSource.FormulaR1C1, "80_Clientes/") - 11)
                .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 With
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I've tried and i recieve a erro info about a "cl":

    Compile error: ByRef argument type mismatch

    Code:
    Sub GetHyperlinks()
        Dim cl As Range
        Dim rng As Range
        With Worksheets("Abertura Conta")
            Set rng = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
        End With
        For Each cl In rng
            Call RetrieveHyperlinkdata(cl)
        Next cl
    End Sub
    Sub RetrieveHyperlinkdata(rngSource As String)
        Dim rngTarget As Range
        With Worksheets("Sheet2")
            Set rngTarget = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
            With .QueryTables.Add(Connection:="URL;" & rngSource.FormulaR1C1, _
                                  Destination:=Range(rngTarget.Address))
                .Name = Right(rngSource.FormulaR1C1, _
                              Len(rngSource.FormulaR1C1) - _
                              InStr(1, rngSource.FormulaR1C1, "80_Clientes/") - 11)
                .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 With
    End Sub
    Last edited by JudahRaion; 2011-10-20 at 05:24 PM.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Ooops...

    Change this:
    Code:
    RetrieveHyperlinkdata(rngSource As String)
    To this:
    Code:
    RetrieveHyperlinkdata(rngSource As Range)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    new error:
    "
    Run-time erro '-2147024809 (80070057)':
    The destination range is not on the same worksheet that the Query table is being created on.
    "

    Code:
    Sub GetHyperlinks()
       Dim cl As Range
       Dim rng As Range
       With Worksheets("Abertura Conta")
           Set rng = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
       End With
       For Each cl In rng
           Call RetrieveHyperlinkdata(cl)
       Next cl
    End Sub
    Sub RetrieveHyperlinkdata(rngSource As Range)
       Dim rngTarget As Range
       With Worksheets("Sheet2")
           Set rngTarget = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
           With .QueryTables.Add(Connection:="URL;" & rngSource.FormulaR1C1, _
                                 Destination:=Range(rngTarget.Address))
               .Name = Right(rngSource.FormulaR1C1, _
                             Len(rngSource.FormulaR1C1) - _
                             InStr(1, rngSource.FormulaR1C1, "80_Clientes/") - 11)
               .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
    thank u very much

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Try this:

    Code:
    Sub GetHyperlinks()
        Dim cl As Range
        Dim rng As Range
        With Worksheets("Abertura Conta")
            Set rng = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
        End With
        For Each cl In rng
            Call RetrieveHyperlinkdata(cl)
        Next cl
    End Sub
    Sub RetrieveHyperlinkdata(rngSource As Range)
        Dim rngTarget As Range
        With Worksheets("Sheet2")
            Set rngTarget = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
            With .QueryTables.Add(Connection:="URL;" & rngSource.FormulaR1C1, _
                                  Destination:=rngTarget)
                .Name = Right(rngSource.FormulaR1C1, _
                              Len(rngSource.FormulaR1C1) - _
                              InStr(1, rngSource.FormulaR1C1, "80_Clientes/") - 11)
                .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 With
    End Sub
    Last edited by Ken Puls; 2011-10-20 at 06:26 PM. Reason: Minor code edit
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    I cant do that now...

    Can we try these tomorrow? My workday as ended today, so can we go foward tomorrow?

    Thank you very much about these... if these work you will help a lot.

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Sure thing.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

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
  •