help on modifying a macro

[congokin]

i am working with the following sheet and need some help

Excel Workbook
	A	B
1	HELP!!!!!!Urgent	http://www.mrexcel.com/forum/showthread.php?t=4994
2	Vlookup, not worrking as described.	http://www.mrexcel.com/forum/showthread.php?t=4995
3	HELP Urgent (sugar1525)	http://www.mrexcel.com/forum/showthread.php?t=4996
4	Vlookup, not working as described.	http://www.mrexcel.com/forum/showthread.php?t=4997
5	Remove data from a text string in a single cell and place in	http://www.mrexcel.com/forum/showthread.php?t=4804
6	COMBO BOX Drop List Function	http://www.mrexcel.com/forum/showthread.php?t=4933
Sheet1

Excel 2007

and i got the following code from mr excel podcast
http://www.youtube.com/watch?v=K9V5E_LPiKs<code>
Sub LoopThrough()
' this macro is taken from
' http://www.youtube.com/watch?v=K9V5E_LPiKs
Dim wso As Worksheet
Set wso = ActiveSheet

For Each Cell In wso.Range("B1:B5")
ActiveWorkbook.Worksheets.Add
ThisURL = "URL;" & Cell.Value

With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$B$1"))
.Name = "f-10"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'xlWebFormattingAll All formatting is imported.
'xlWebFormattingNone No formatting is imported.
'xlWebFormattingRTF Rich Text Format compatible formatting is imported.
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next Cell

End Sub</code>

what i need is to modify the above macro to display info on all the urls (colA) on a single master sheet instead of opening 5 worksheets as per the above code

 

[congokin]

any help?

 

[mirabeau]

Just a suggestion, since I haven't tried it on test data.

Sub LoopThrough()
' this macro is taken from
' http://www.youtube.com/watch?v=K9V5E_LPiKs
Dim wso As Worksheet, x as integer
Set wso = ActiveSheet

ActiveWorkbook.Worksheets.Add.Name = "mastersheet"
For Each Cell In wso.Range("B1:B5")
x = x + 1
ThisURL = "URL;" & Cell.Value

With Sheets("mastersheet").QueryTables.Add(Connection:= _
    ThisURL, Destination:=Range("$B$1").Offset(, 4 * x - 4))
.Name = "f-10"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'xlWebFormattingAll All formatting is imported.
'xlWebFormattingNone No formatting is imported.
'xlWebFormattingRTF Rich Text Format compatible formatting is imported.
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next Cell

End Sub

Although trying to sort out code written by others is usually a pretty thankless task.

 

[congokin]

i have tried the macro but it only takes the info of the first url not all other url

 

[congokin]

I tried it again and the macro WORKS. thanks...
But i was wondering if the webpage info could show up in rows instead of colums...

 

[congokin]

any takers???

 

[congokin]

to be more clear...the macro above works great but the info are shown columns by columns (ie colB to colF) has info on url1, then colG to colK has info on url2 and so on...
what i would like to see is having info of url1 from a1 to a20(as an example), and info of url2 from a21 to a31 (as an example) and so on
url2 should start on the last row of info from url1. i hope this helps

 

[mirabeau]

Hey,

I don't know that anything I suggest would work, coz I don't have test data to check it against.

And, as noted, trying to sort out code written by others is never much fun.

However as a suggestion for producing transposed results, you might like to try modifying the code as indicated:

Sub LoopThrough()
' this macro is taken from
' http://www.youtube.com/watch?v=K9V5E_LPiKs
Dim wso As Worksheet, x as integer
Set wso = ActiveSheet

ActiveWorkbook.Worksheets.Add.Name = "mastersheet"
For Each Cell In wso.Range("B1:B5")
x = x + 1
ThisURL = "URL;" & Cell.Value

With Sheets("mastersheet").QueryTables.Add(Connection:= _
    ThisURL, Destination:=Range("$B$1").Offset(, 4 * x - 4))
'May need to modify the Offset(...) in above line to allow for transposition. Try experimenting with this to suit you.
'anything before the comma is row offset (here it's blank, i.e default=unchanged)
'after the comma is column offset
'maybe, if it otherwise transposes ok, just remove the comma to get row offset as you want it
'you can safely delete these green lines whenever you like
.Transpose = true
.Name = "f-10"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'xlWebFormattingAll All formatting is imported.
'xlWebFormattingNone No formatting is imported.
'xlWebFormattingRTF Rich Text Format compatible formatting is imported.
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next Cell

End Sub

 

[congokin]

getting a run time error '438'. tried to play with the offset option but could not get it to work...

 

[mirabeau]

OK.

So let me suggest the following.

1. Use the code I posted in post #3 with the following modification

With Sheets("mastersheet").QueryTables.Add(Connection:= _
    ThisURL, Destination:=Range("$B$1").Offset(, 7 * x - 7))

(that is, use 7 instead of 4). But don't use the transposition inclusion that I suggested.

2. Wait until the queries have finished downloading; they should all be there with a single column between each table.

3. I understand you next want these tables transposed, instead of keeping them as they are.

4. Rather than writing more code myself, could you then look at the Microsoft link http://support.microsoft.com/kb/182822 and follow the steps there.

5. This should give you the transposed result you want. If all looks OK and you then want to code it with some VBA code, try following the Microsoft steps while using the macro recorder and see how you get on.

6. Good luck!