Not working... Why? ActiveSheet.QueryTables.Add(Connection:=ActiveCell.Offset(0, -1).Value, Destination:=Range(ActiveCell.Address))

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shaka215@gmail.com - 26 Mar 2008 23:58 GMT

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

Reply to this Message

Madiya - 27 Mar 2008 07:42 GMT

On Mar 27, 3:58 am, Shaka...@gmail.com wrote:

> 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
[quoted text clipped - 28 lines]
> .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

Reply to this Message

Ivyleaf - 27 Mar 2008 07:45 GMT

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.

On Mar 27, 9:58 am, Shaka...@gmail.com wrote:

Reply to this Message