MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

"New Web Query" question

  Asked By: Ernesta    Date: Feb 04    Category: MS Office    Views: 718

This info is copied/pasted from Microsoft Excel Help:
Create a new Web query

Start in Microsoft Excel

On the Data menu, point to Import External Data, and then click New
Web Query.

In the New Web Query dialog box enter the URL for the Web page from
which you want to get data. You can type the URL, paste it from a
copied address, or click the arrow next to the Address list and select
a recently used address.

Click Go.
Since I have a multitude of "new web queries" to create, it is
impractical to either type in the URL or select a recently used address.

If I could, in fact, paste it from a copied address, my problems would
be solved. But what happens is that I copy the URL into notepad, go
to the New Web Query dialog box, right-click to paste it in, and find
that MY RIGHT-CLICK MENU IS DISABLED. In other words, I don't have a
keystroke paste option.

Please tell me how to copy a URL off of my worksheet and insert it
into the "New Web Query" dialog box. Preferably with VBA code or
keystrokes which are macro-able. Any help much appreciated . . .



7 Answers Found

Answer #1    Answered By: Ulfah Hashmi     Answered On: Feb 04

I recorded the process and it produced a macro like this:

Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.mydearfriend.net", Destination:=Range("D4"))
.Name = "www.mydearfriend"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

You could use vba  to put whatever values you wanted into this command and it
would probably work to put the data  into your spreadsheet.

Answer #2    Answered By: Adaulfo Fischer     Answered On: Feb 04

I created my macro with your code  and it worked as you said.
Then I replaced your URL with one which is typical of the pages I want
to download from the net. When I run it there are no error messages,
but when it is finished there is no data  on the sheet. Any idea
what's wrong?

Answer #3    Answered By: Nicholas Wells     Answered On: Feb 04

I tried inserting the page  you are referring to. Excel would not select  just
the table but I was able to import  the whole page.

I think Excel may rely on the page having named tables and the score table has
no name. (To generate the web  page it does not need one)

The first sub below is the one originally generated.

Then I tried a bit of editing and came up with the amendments (marked with
'*** at the end of the code  line) which actually just call in the data
required (I think).

If all the pages you are calling are in the same format this approach may be
useful to you. If they are all different then it will be a total pain to try
to set them all up.

Answer #4    Answered By: Lily Brown     Answered On: Feb 04

the code  you just posted meets my needs perfectly--once I get
the entire page(s) loaded onto the worksheet, I can easily add macros
to chop off the garbage and manicure what I want to use.

Answer #5    Answered By: Umaiza Hashmi     Answered On: Feb 04

That is really good.

Now ...
What have we learned?

I hope you have learned that when you need to do a macro for a repeated
action, you can record the macro while you do the action manually and then
alter it to suit. That is all I did.

What have I learned?

(1) Perhaps I should teach rather than providing answers. If I had told you
to record the macro you would probably have arrived at a working answer faster
than I did.

(2) I have also learned a lot about the workings of the web  data import

Answer #6    Answered By: Barachias Levi     Answered On: Feb 04

I'm going to work on this
today and will post my results--Again many thanks!

Answer #7    Answered By: Naomi Lee     Answered On: Feb 04

Unfortunately I have the same problem whether I open the text file in Excel,
or import  the data  using the wizard. If I import the data in general format,
I get the #NAME? error, and if I import it in text format I don't get the
error, but the numbers end up being formatted as text and I need to convert
them back to numbers.

The solution would be simple if the text and numbers ended up in separate
columns when I import the data, because then I could treat the columns
differently according to their specific content, but unfortunately there
will always be text in the same column as the numbers.

I had a personal reply from PY & Associates who have asked me to give them
the first 10 lines in the text file as an example (attached is a very
simplified sample of the sort of data involved), so I'm hoping they may have
an idea as to what I can do.

Didn't find what you were looking for? Find more on "New Web Query" question Or get search suggestion and latest updates.