Run time error-1004 on query tables.add connections - need help.

[aflores41]

Sub macro1()
'Dim frm As UserForm
Dim I As Integer
'To could be as many as you want. I only put 25 as a limit.
For I = 1 To 25
Dim a As String
a = Sheet1.Cells(I, 1).Value
Dim b As String
b = ActiveSheet.Cells(I, 2).Value
'get URLs
'option after "URL;" & a
    'UserForm1.txtbox1.value
With ActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:="URL;" & a, Destination:=Range("$A$1"))
 'With ActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:="URL;" & a, Destination:=Range(b))
        .Name = _
        "its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439"
        .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
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        'MsgBox "Done!"
    End With
 Next I
 
'close all forms
    'For Each frm In UserForms
 
     '   Unload frm
 
    'Next frm
    End Sub
 

Hello,

Code above is having issue when ran from a form. The code works if it's ran from spreadsheet however when placed in a button in the form it fails with error code 1004. Please help.

I've researched and most of the blogs weren't very helpful.

Thanks.

I use this VBA to pull URLs from Sheet one (craigslist URL) then scrape it up to 25 sites using loop function. This works when manually placed in sheet but not in button of excel.

[zmbd]

what this is saying is that something isn't formatted correctly.

Just to be sure, this is the exact same code that works in the worksheet level module? What I would do here is instead of referencing "activesheet" I would reference the worksheet either by name or index. It's possible that the sheet is not being seen as active when the form has loaded.

[aflores41]

Not working. I would get the run-time error '-2147024809 (80070057)';
"The destination range is not on the same worksheet that query table is being created on."

What I'm trying to accomplish is as it scrapes craigslists URL 1st page, it pastes it in a worksheet then moves to creating a new sheet for the new scraped data.

Thank you.

-Al

[hvsummer]

don't use activesheet on another application that call for excel.
replace "ActiveSheet.Cells(I, 2).Value"
with fixed name that link to your range.
similar to another code that use activesheet.

if you run code in background of another application, don't use .ActiveSheet or .ActiveWorkbook ==

[zmbd]

aflores41,
Although the error "The destination range ... worksheet.." leads me to believe that my assumption that you are instancing the form from with Excel; never-the-less, will you please clarify this for us: are you instancing the form from within Excel, Access, or some other program?

What this error tells me is that the workbook or worksheet is losing the focus within your code or that the connection string in 13 is not resolving correctly.

You have to explicitly set the reference to these objects when called from the form.


hvsummer:
Take a look at the error, this appears to be an Excel generated failure, not one I would expect if called from within another application.

[hvsummer]

oh, sorry Aflores and zmbd, I have read this "This works when manually placed in sheet but not in button of excel"

So the first thing I have to say, you posted this question in wrong section...(should be excel section)
Secondly, where is that button on ? your original sheet that you did place code in or somewhere else ?

since this vba from excel, it will work different from Access vba...

[zmbd]

No worries, this is VBA related; thus, it is, technically, in the correct forum. =)
If it had not have been, then I would have moved it :)

There are some Excel specific objects and references; however, still VBA.

Anyway, I'll have to take another look thru the original code. A direct reference should have been the ticket.

aflores41:
I need you to open your form
Show the properties dialog for the control button you are attaching this to.
Is the property, "TakeFocusOnClick" set to "true"

The next thing I need is for you to post the exact code that the button us using not the Sub() you've posted we need to see the

Private Sub CommandButton1_Click()
''codehere
End Sub

If you have instead designed a "form" using a worksheet and inserting objects therein, then we have a whole other beast to work with and it should explain what is happening a bit more clearly.

[NeoPa]

Al, we've all assumed that the error is being triggered from line #13. It would be helpful if you'd confirm this, and also include any error message with your questions (rather than just a number), for those of us that don't carry the error table around in our heads ;-)

[aflores41]

Sub Scrape_sheet1_btn_Click()
Dim LResponse, I As Integer
Dim a, b As String
 
LResponse = MsgBox("You must list the URLs you are planning to scrape in sheet1 and create a blank sheet2. Once the scrape macro starts, each column is represented by individual URLs from sheet1." & Chr(13) & Chr(13) & "Do you wish to continue?", vbYesNo, "Continue")
If LResponse = vbYes Then
 
'To 25 could be as many as you want. I only put 25 as a limit.
    For I = 1 To 25
 
'make sure you have URLs in sheet1 from cell A1 listed.
    a = Sheet1.Cells(I, 1).value
'activesheet paste
    b = ActiveSheet.Cells(I, 2).value
 
 
'get URLs
 
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & a, Destination:=Range("$A$1"))
        '.Name = _
        '"its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439"
        .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
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next I
MsgBox "Done!"
Else: End
End If
End Sub

Hello,

Thank you for being patient with me. I apologize for a late response. Above is the vba code I used for the button I'm using as an add-on in excel. The vba code is encoded in the xlam file.

NeoPa, you're right for the line#13 as the error however, I don't know how to fix it. I've tried changing activesheets to sheet1 or sheet1(sheetname) and still the same it won't work.

Thank you.

Al

[NeoPa]

AFlores:
you're right for the line#13 as the error

OK Al. And the error message is ... ?

[aflores41]

Run time error-1004 (error in line 40) as activesheets.
run-time error '-2147024809 (80070057)' (error in line 19) as sheet1....
run time error 438 (error in line 19) as sheet1(sheet1)

[zmbd]

Referring to the code-block in Post#9

It shouldn't make a difference; however, I would break Line 3 apart or explicitly cast "a" as string. Starting with Acc2007, the comma designation no longer returns the same cast type for a group of variables. Variable "a" is being cast as type variant whereas "b" is actually being cast as type string.

(see example here : MS>Declaring Variables
You can declare several variables in one statement. To specify a data type, you must include the data type for each variable. In the following statement, the variables intX , intY , and , intY , and intZ are declared as type Integer.

Dim intX As Integer, intY As Integer, intZ As Integer 

In the following statement, intX and intY are declared as type Variant; only and intY are declared as type Variant; only intZ is declared as type Integer.

Dim intX, intY, intZ As Integer 

You don't have to supply the variable's data type in the declaration statement. If you omit the data type, the variable will be of type Variant.

Although one may still be able to "bulk" type variables in VB such does not hold true for VBA.

Line15 insert debug.print "Range-A: " & a
Line16 insert debug.print "Range-B: " & b
We need to see what is actually being returned by these calls as I suspect your connection string is malformed.
Line20/21 may also be giving you some issues with the non-alphanumeric values. It is usually advisable not to use anything other than alphanumeric and the underscore in connection names - also highly advisable not to use spaces in the name either.

errors reported for line40 are most likely related to line19.

Finally I always advise building the string first and then inserting that as needed, Thus line19 transforms

'...air code...
'...other code...
Dim zstrCN as String
'
'...other code...
'
zstrCN = "URL;" & a
'
 With ActiveSheet.QueryTables.Add(Connection:= zstrCN, _
   Destination:=Range("$A$1"))

This way you can place a debug.print zstrCN on the line following the definition to ensure that the connection string is resolving correctly. Even a single space, or even letter-case, can cause these to fail.

[NeoPa]

Hi again Al. I think there's a little confusion here still.

I don't want to flog a dead horse, but what I was talking about would have been some thing like "Object doesn't support this property or method" for error #438. I don't have the other messages to hand, but I hope that gives you the picture for what can be very helpful in getting meaningful answers to your questions.

[NeoPa]

ZMBD:
Starting with Acc2007, the comma designation no longer returns the same cast type for a group of variables. Variable "a" is being cast as type variant whereas "b" is actually being cast as type string.

FYI:
As far as I'm aware this is how it's always been and hasn't changed. Certainly that's true for 2003 that I can test, but I seem to recall it being the case from 97 which was when I started with Access again after a long gap. I would guess it's the same all the way from the start TBF.

[zmbd]

I remember that in either ACC2003 or 2010, we jumped from Office-95, I had a few modules break because I had "bulk" defined the variables... the more I think about it the more I sure it was the ACC2003 jump... in any case I was expecting "long" and the variables were coming thru as "strings" and of-course when storing the values to the table Error 13 - Type Mismatch. Just about pulled my hair out trying to find that error! :)

[NeoPa]

I can't imagine why they broke at that stage as an untyped variable is always set as Variant. This is generally like blood type O in that it works everywhere - more or less. There are a few standard procedures that may have changed from ByVals to ByRefs which would cause it to work happily before the change but not afterwards. I'm 99(.999999)% sure that there has never been a time when setting any one of a list of variables to a specific type would cause any of the others to be set that way too.

To be absolutely clear :

Dim A, B, C AS Integer

Has always resulted in :

A ==> Variant
B ==> Variant
C ==> Integer

[zmbd]

IDK either... the typecasting solved the issue.