| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write a macro 30 Nov?
Does anyone have any suggestions on how I can insert the Counter into the web
link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. .... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Eric,
I'm not entirely clear what your trying to do but this does what I think your asking. Note a couple of things. I 've stopped all the sheet selection it isn't necessary and I don't paste into columns "A:A" I user the variable 'counter to select the column to paste in to. Sheets("Sheet2").Columns(counter).PasteSpecial Sub sonic() For counter = 1 To 10 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:=Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Columns("F:F").Copy Sheets("Sheet2").Columns(counter).PasteSpecial Next counter End Sub "Eric" wrote: Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Thank you very much for suggestions
Could you please give me any suggestions on how to edit the link? "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" I would like to retrieve the value from counter and insert into 1 If counter is 1, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" If counter is 2, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=2" .... If counter is 10, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=10" Do you have any suggestions Thank everyone very much for any suggestions Eric "Mike H" wrote: Eric, I'm not entirely clear what your trying to do but this does what I think your asking. Note a couple of things. I 've stopped all the sheet selection it isn't necessary and I don't paste into columns "A:A" I user the variable 'counter to select the column to paste in to. Sheets("Sheet2").Columns(counter).PasteSpecial Sub sonic() For counter = 1 To 10 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:=Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Columns("F:F").Copy Sheets("Sheet2").Columns(counter).PasteSpecial Next counter End Sub "Eric" wrote: Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Assuming the link is stored in a variable named Link and the counter is
stored in a variable named Counter AND assuming the page value is ALWAYS located at the end of the link's text (that is very important), then this will change the page to the value of the counter... Link = Left(Link, InStrRev(Link, "=")) & Counter -- Rick (MVP - Excel) "Eric" wrote in message ... Thank you very much for suggestions Could you please give me any suggestions on how to edit the link? "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" I would like to retrieve the value from counter and insert into 1 If counter is 1, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" If counter is 2, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=2" ... If counter is 10, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=10" Do you have any suggestions Thank everyone very much for any suggestions Eric "Mike H" wrote: Eric, I'm not entirely clear what your trying to do but this does what I think your asking. Note a couple of things. I 've stopped all the sheet selection it isn't necessary and I don't paste into columns "A:A" I user the variable 'counter to select the column to paste in to. Sheets("Sheet2").Columns(counter).PasteSpecial Sub sonic() For counter = 1 To 10 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:=Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Columns("F:F").Copy Sheets("Sheet2").Columns(counter).PasteSpecial Next counter End Sub "Eric" wrote: Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Thank everyone very much for suggestions
Do you mean like following codes? There is some error on following coding Does anyone have any suggestions? Thank everyone very much for any suggestions Eric Sub sonic() Link = "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" For counter = 1 To 10 Link = Left(Link, InStrRev(Link, "=")) & Counter With ActiveSheet.QueryTables.Add(Connection:= _ Link, Destination:=Range("A1")) .Name = "Link" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Columns("F:F").Copy Sheets("Sheet2").Columns(counter).PasteSpecial Next counter End Sub "Rick Rothstein" wrote: Assuming the link is stored in a variable named Link and the counter is stored in a variable named Counter AND assuming the page value is ALWAYS located at the end of the link's text (that is very important), then this will change the page to the value of the counter... Link = Left(Link, InStrRev(Link, "=")) & Counter -- Rick (MVP - Excel) "Eric" wrote in message ... Thank you very much for suggestions Could you please give me any suggestions on how to edit the link? "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" I would like to retrieve the value from counter and insert into 1 If counter is 1, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1" If counter is 2, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=2" ... If counter is 10, then "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=10" Do you have any suggestions Thank everyone very much for any suggestions Eric "Mike H" wrote: Eric, I'm not entirely clear what your trying to do but this does what I think your asking. Note a couple of things. I 've stopped all the sheet selection it isn't necessary and I don't paste into columns "A:A" I user the variable 'counter to select the column to paste in to. Sheets("Sheet2").Columns(counter).PasteSpecial Sub sonic() For counter = 1 To 10 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:=Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Columns("F:F").Copy Sheets("Sheet2").Columns(counter).PasteSpecial Next counter End Sub "Eric" wrote: Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Thank everyone very much for suggestions
Could you please give me the example with loop and web link location? I get no idea where to place my web link, and loop Thank everyone very much for any suggestions Eric "Don Guillett" wrote: Establish your web query and then refresh INSTEAD of adding again by using this. Sub RefreshAndCopy() Sheets("sheet1").QueryTables(1).Refresh With Sheets("sheet2") lc = .Cells.Find(What:="*", _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column + 1 'MsgBox lc Sheets("sheet1").Columns("f").Copy .Columns(lc) End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
After you establish the FIRST one in sheet 1, then use this to loop thru the
10 to refresh and copy col F to the next available column in sheet 2. Hide the first 22 rows on sheet2. Contact me privately if desired. Sub queryloop() For i = 2 To 10 'number desired With Sheets("sheet1").QueryTables(1) .Connection = "URL;http://www2.uwants.com/forumdisplay.php?fid=845&page=" & i .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With With Sheets("sheet2") cc = .Columns.Count 'MsgBox cc lc = .Cells.Find(What:="*", _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column + 1 'MsgBox lc Sheets("sheet1").Columns("f").Copy .Columns(lc) End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Thank everyone very much for suggestions Could you please give me the example with loop and web link location? I get no idea where to place my web link, and loop Thank everyone very much for any suggestions Eric "Don Guillett" wrote: Establish your web query and then refresh INSTEAD of adding again by using this. Sub RefreshAndCopy() Sheets("sheet1").QueryTables(1).Refresh With Sheets("sheet2") lc = .Cells.Find(What:="*", _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column + 1 'MsgBox lc Sheets("sheet1").Columns("f").Copy .Columns(lc) End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro 30 Nov?
Actually, could be a little simpler Sub queryloop() For i = 2 To 4 With Sheets("sheet1").QueryTables(1) .Connection = "URL;http://www2.uwants.com/forumdisplay.php?fid=845&page=" & i .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Sheets("sheet1").Columns("f").Copy Sheets("sheet2").Columns(i) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... After you establish the FIRST one in sheet 1, then use this to loop thru the 10 to refresh and copy col F to the next available column in sheet 2. Hide the first 22 rows on sheet2. Contact me privately if desired. Sub queryloop() For i = 2 To 10 'number desired With Sheets("sheet1").QueryTables(1) .Connection = "URL;http://www2.uwants.com/forumdisplay.php?fid=845&page=" & i .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With With Sheets("sheet2") cc = .Columns.Count 'MsgBox cc lc = .Cells.Find(What:="*", _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column + 1 'MsgBox lc Sheets("sheet1").Columns("f").Copy .Columns(lc) End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Thank everyone very much for suggestions Could you please give me the example with loop and web link location? I get no idea where to place my web link, and loop Thank everyone very much for any suggestions Eric "Don Guillett" wrote: Establish your web query and then refresh INSTEAD of adding again by using this. Sub RefreshAndCopy() Sheets("sheet1").QueryTables(1).Refresh With Sheets("sheet2") lc = .Cells.Find(What:="*", _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column + 1 'MsgBox lc Sheets("sheet1").Columns("f").Copy .Columns(lc) End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Does anyone have any suggestions on how I can insert the Counter into the web link? and change the column location for pasting selection? Following code is generated by recording Macro, please see Coding section If the counter is 1, then paste into Columns("A:A").Select under Summary worksheet. If the counter is 2, then paste into Columns("B:B").Select under Summary worksheet. ... If the counter is 10, then paste into Columns("J:J").Select under Summary worksheet. Does anyone have any suggestions? Thanks in advance forany suggestions Eric =============== Coding =============== For Counter = 1 to 10 'Your code Next Counter With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1", Destination:= _ Range("A1")) .Name = "forumdisplay.php?fid=845&page=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.LargeScroll ToRight:=2 Columns("F:F").Select Selection.Copy Sheets("Sheet2").Select Columns("A:A").Select ActiveSheet.Paste End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to write a macro | Excel Programming | |||
| Help write Macro | Excel Discussion (Misc queries) | |||
| How to write a macro?? | Excel Discussion (Misc queries) | |||
| is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
| Need help to write macro please | Excel Programming | |||
Hybrid Mode
