-
Oct 13th, 2011, 06:39 AM
#1
Thread Starter
New Member
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
-
Oct 13th, 2011, 12:50 PM
#2
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.
-
Oct 14th, 2011, 02:24 AM
#3
Re: grab info
try recording a macro of getting data from one source, then convert to a loop for each source
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 14th, 2011, 04:46 AM
#4
Thread Starter
New Member
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.
-
Oct 14th, 2011, 06:16 AM
#5
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:
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 14th, 2011, 06:56 AM
#6
Thread Starter
New Member
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.
-
Oct 14th, 2011, 07:05 AM
#7
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 14th, 2011, 07:29 AM
#8
Thread Starter
New Member
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))
-
Oct 14th, 2011, 08:25 AM
#9
Thread Starter
New Member
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"
-
Oct 15th, 2011, 04:03 AM
#10
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?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|