Not working... Why? ActiveSheet.QueryTables.Add(Connection:=ActiveCell.Offset(0,

[Shaka215]

Hi! Here is the code I am using...I don't understand why it's not
working. In ActiveCell.Offset(0, -1) I have a list of URLs. I need for
the results of the query to be dropped in the ActiveCell.Address. It
keeps crashing on me. It's complaining of the following...

Run-time error '1004':
Application-defined or object-defined error


ActiveCell.Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:=ActiveCell.Offset(0,
-1).Value, Destination:=Range(ActiveCell.Address))
.Name = "csv"
.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

 

[Madiya]

Hi! Here is the code I am using...I don't understand why it's not
working. In ActiveCell.Offset(0, -1) I have a list of URLs. I need for
the results of the query to be dropped in the ActiveCell.Address. It
keeps crashing on me. It's complaining of the following...

Run-time error '1004':
Application-defined or object-defined error

    ActiveCell.Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:=ActiveCell.Offset(0,
-1).Value, Destination:=Range(ActiveCell.Address))
        .Name = "csv"
        .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

Hi,
Assuming that you have url as http://www.abcxyz.com/
First line of your code should be as below.
With ActiveSheet.QueryTables.Add(Connection:="URL;" &
ActiveCell.Offset(0, _
-1).Value, Destination:=Range(ActiveCell.Address))

I have only added "URL;" & in your connection string.

Hope this helps.

Regards,
Madiya

 

[Ivyleaf]

Hi,

It looks like you are just sending QueryTables a raw url as the
connection string from your description / example. You might want to
try something along the lines of:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & ActiveCell. _
Offset(0,-1).Value, Destination:=Range(ActiveCell.Address))

This should format your url correctly as a connection string. Without
knowing what your URL will return, I can't comment on the rest.

Cheers,
Ivan.