Click here to Skip to main content
15,843,727 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Dear All,

I am trying to extract data from website to excel using VBA macro. But getting a error as
"runtime error:1004
Application defined or object defined error".


mystr url :http://www.x.com/490462

A bit newbie fro VBA So please help.
Thanks in advance.

VB
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
For x = 1 To 10
Worksheets("Sheet4").Select
Worksheets("Sheet4").Activate
mystr = Cells(x, 1)
Worksheets("Sheet1").Select
With ActiveSheet.QueryTables.Add(Connection:=mystr, Destination:=Range("$A$1"))
.Name = "490462_1"
        .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 = xlWebFormattingRTF
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Range("A6:A9").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A" & x + 5).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("A387:A388").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B" & x + 5).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A6").Select
    Sheets("Sheet1").Select
    Cells.Select
    Range("A371").Activate
    Application.CutCopyMode = False
    Selection.QueryTable.Delete
    Selection.ClearContents
    Sheets("Sheet1").Select
Next x
End Sub
Posted
Updated 2-Mar-14 9:31am
v2
Comments
Maciej Los 2-Mar-14 14:32pm    
Which line causes error?
Sergey Vaselenko 3-Mar-14 13:02pm    
If you need to import web data in Excel, try the SaveToDB add-in at http://www.savetodb.com/download.htm.
It allows getting data from HTML, XML, CSV, and JSON webpages.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900