grab info

[JudahRaion]

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)

[si_the_geek]

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.

[westconn1]

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

[JudahRaion]

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.

[westconn1]

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

lastrow = sheets("sheet1").cells(rows.count, 3).end(xlup).row
for each c in sheets("sheet1").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

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

[JudahRaion]

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.

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.

[westconn1]

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

[JudahRaion]

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

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

[JudahRaion]

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

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"

[westconn1]

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?