What issue is in this program, this program is flashing 'run-time error 1004': application-defind or object-defind error please help me

Question

0.00/5 (No votes)

See more:

In this program i am trying to scrap data from the list of link which is in my shee1,And data should be copy in next other sheets.

VB

Public Sub SUM()
For x = 1 To 3
    Worksheets("sheet1").Select
    Worksheets("sheet1").Activate
    mystr = "URL;http://www.medifee.com/hospital/9144-kkasturi-medicare-pvt-ltd/"
    mystr = Cells(x, 1)
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = x
    With ActiveSheet.QueryTables.Add(Connection:= _
    mystr, _
        Destination:=Range("$B$1"))
        .Name = "9144-kkasturi-medicare-pvt-ltd"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Next x
End Sub

error massege: run-time error 1004':

Application-defind or object-defind error
line no: 8

What I have tried:

what issue is in this program, this program is flashing 'run-time error 1004': Application-defind or object-defind error please help me.

In this program i am trying to scrap data from the list of link which is in my shee1,And data should be copied in next other sheets.

Posted 11-Sep-16 22:19pm

Member 12734412

Updated 12-Sep-16 4:30am

Leo Chapiro

v4

Add a Solution

Comments

Patrice T 12-Sep-16 4:22am

Give exact error message and position !

Member 12734412 12-Sep-16 4:41am

error massege: run-time error 1004': Application-defind or object-defind error
line no: 8

Patrice T 12-Sep-16 5:12am

Ooops

Member 12734412 12-Sep-16 4:46am

i was trying to record macro in excel 2010

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2016-09-12T04:30:00

Check this:

VB

Public Sub SUM()
Dim wsh As Worksheet
Dim mystr As String
Dim qt As QueryTable
Dim x As Integer

On Error Resume Next


For x = 1 To 3
    mystr = "URL;http://www.medifee.com/hospital/9144-kkasturi-medicare-pvt-ltd/"
    'mystr = Cells(x, 1) 'this replaces original connection string!
    Set wsh = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    wsh.Name = x
    
    Set qt = wsh.QueryTables(x)
    If Not qt Is Nothing Then wsh.QueryTables(x).Delete
    
    With wsh.QueryTables.Add(Connection:=mystr, Destination:=Range("$B$1"))
        .Name = x
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next x

End Sub

I tested that and it works well. Please, read my comment inside code block.
Result:

Hospital Facility	Rates
General Ward	Rs. 1800.00 (Daily Expenses)
General Ward	Rs. 800.00
Semi Private Ward	Rs. 2300.00 (Twin sharing-Daily Expenses)
Semi Private Ward	Rs. 1200.00
Private Ward	Rs. 2800.00 (Daily Expenses)
Private Ward	Rs. 1500.00
X-Ray	Rs. 350.00 (Chest)
Sonography	Rs. 1200.00 (Whole Abdomen)
Sonography	Rs. 1000.00 (Upper / Lower Abdomen)
Vitamin D Test	Rs. 1400.00
Vitamin B12 Test	Rs. 1000.00
Blood Sugar Test	Rs. 50.00
Blood Group Test	Rs. 100.00
Lipid Profile	Rs. 600.00
Thyroid Test	Rs. 550.00
Urine Routine	Rs. 70.00
Urine Routine	Rs. 70.00
Stool Routine	Rs. 120.00
Folic Acid Test	Rs. 1000.00
HIV Test	Rs. 350.00
Iron Test	Rs. 700.00 (Profile)
ICU	Rs. 2000.00
ICU	Rs. 5000.00 (Daily Expenses)
ICU	Rs. 600.00 (Monitor charges)
ICU	Rs. 2000.00 (Bipep )
Amylase Test	Rs. 450.00
APTT (Activated Partial Thromboplastin Time) Test	Rs. 350.00
HbA1C Test	Rs. 1250.00
...
and so ...