macro needs some tweaking

C

congokin

Board Regular
Joined
Oct 31, 2010
Messages
74
i am trying to download all archive links from the excel archive but my have a problem with the following macro
Rich (BB code): 
Sub MrexcelArchive()
'
' MrexcelArchive2 Macro
' www.mrexcel/forum/archive

Dim LastRow As Object
Dim x As Integer

Set LastRow = Range("A" & Rows.Count).End(xlUp)
For x = 1 To 5
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.mrexcel.com/forum/archive/index.php/f-10-p-x.html", _
        Destination:=LastRow.Offset(2, 0))
        .Name = "MrExcelArchive"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next x
    
End Sub
i want to be able to change the x on the following line
"URL;http://www.mrexcel.com/forum/archive/index.php/f-10-p-x.html"
so i the macro can loop from 1 to 5 in this example
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Change
Code: 
"URL;http://www.mrexcel.com/forum/archive/index.php/f-10-p-x.html"
to this
Code: 
"URL;http://www.mrexcel.com/forum/archive/index.php/f-10-p-" & x & ".html"
 
Upvote 0
can someone explains to me why the macro above display the result like this
Excel Workbook
AB
1251. Matching data and returning count501. Function that determines formatting of cells
2252. passing a range between modules502. Highlighting Highest value in a row
3253. summing a standard range down a column503. Conditional format with multiple indirects
4254. Can you conditionally hide a cell?504. Restrict the information that can be written in a range
5255. windows script host for NT reading Excel505. sorting 3 seperate sheets simultaneously (is this tough?)
6256. Sum if with multiple options506. Passing an array between modules
7257. Excel won't open507. How to put lines on a column chart
8258. Function to calculate a markup508. Any tutorials online for Excel BEGINNER?
9259. macro help509. Timesheets
10260. Excel custom function510. Read PDF files with Excel
11
Sheet6
Excel Workbook
ABCDEF
11. HELP!!!!!!Urgent
22. Vlookup, not worrking as described.
33. HELP Urgent (sugar1525)
44. Vlookup, not working as described.
55. Vlookup not working properly.
66. Remove data from a text string in a single cell and place in
77. COMBO BOX Drop List Function
88. Protecting & Unprotecting multiple sheets.
99. limitations within excel!!
1010. Comparing Excel Sheets
11
12
13251. Matching data and returning count
14252. passing a range between modules
15253. summing a standard range down a column
16254. Can you conditionally hide a cell?
17255. windows script host for NT reading Excel
18256. Sum if with multiple options
19257. Excel won't open
20258. Function to calculate a markup
21259. macro help
22260. Excel custom function
23
Excel 2007 instead of Sheet7
Excel 2007
since i have stated in the macro to copy the next data 2 rows after the previous data?
 
Upvote 0
thats because you only pick up the Lastrow once!, try this
Code: 
Sub MrexcelArchive()
'
' MrexcelArchive2 Macro
' www.mrexcel/forum/archive

Dim LastRow As Object
Dim x As Integer


For x = 1 To 5
Set LastRow = Range("A" & Rows.Count).End(xlUp)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.mrexcel.com/forum/archive/index.php/f-10-p-" & x & ".html", _
        Destination:=LastRow.Offset(2, 0))
        .Name = "MrExcelArchive"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next x
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Scraping a table from a website, VBA code needs to be updated, running into issues
Replies
1
Views
557
Dan_W
D
A
  • Question Question
URL in cell used in VBA code to import web data
Replies
8
Views
1K
johnnyL
J
A
  • Question Question
Getting table from a link from a cell value
Replies
0
Views
538
adipat
A
A
  • Question Question
Table is not downloading to excel
Replies
0
Views
534
adipat
A
S
  • Question Question
Macro Button
Replies
1
Views
504
offthelip
O

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top