Import data from xls file on the web

[Svilen Pachedzhiev]

Can anyone help me with guidelines how to import data from web based xls
file?
Is it possible to import sheet from such file and then update it
automatically?

Tahnks,

Svilen

 

[Ron de Bruin]

Hi Svilen

Do you want to update the file on the web directly
Do I understand you correct ?

 

[Svilen Pachedzhiev]

Hi Ron,

A few lines to make myself more clear:
There is a xls file in Internet with data. I want to import data or entire
sheet from this file into a xls file on my computer. So maybe the right
topic is "Import data from xls file FROM the web".

Regards,
Svilen


Hi Svilen

Do you want to update the file on the web directly
Do I understand you correct ?

 

[Svilen Pachedzhiev]

I thought that simple thing like direct link to the file could work but it
doesn't :-(
(like: =[http://www.www.org/www/file.xls]Sheet!$a$1)

Svilen

 

[Ron de Bruin]

Use it like this

='http://www.rondebruin.nl/files/[test.xls]Sheet1'!A1

This link is working

 

[topola]

Hi, that's an interesting topic, for me too.
I haven't succeeded hovewer. While pasting web addres has changed to
the following:

http://www.rondebruin.nl/files/[test.xls]Sheet1'!A13
(Edit Hyperlink window)

Message sais: "Unable to open
http://www.rondebruin.nl/files/[test.xls]Sheet1'!A13. The Internet
site reports that the item you requested could not be found. (HTTP/1.0
404)"

 

[Svilen Pachedzhiev]

Thanks Ron! Direct links work now and it solves a part of my problem. But I
stell need to have some idea how to import data from xls file from web via
some kind of query.
Because with this method I can't control refreshment process, and I would
like to have such solution if I need to import csv or txt files.

Svilen

 

[Ron de Bruin]

Hi topola

It is worksheetfunction no web address

Copy this in a cell
='http://www.rondebruin.nl/files/[test.xls]Sheet1'!A1

 

[Ron de Bruin]

Hi

Maybe you can use this ?
You can use this to copy a range from a file from a website (this is working, try it in a new workbook)

Maybe others have a better solution for you

Sub Getvalues()
ActiveWorkbook.Names.Add Name:="testing", _
RefersToR1C1:="='http://www.rondebruin.com/[Book2.xls]Sheet1'!R1C1:R10C4"

Application.ScreenUpdating = False
Range("A110").Formula = "=testing"
MyWait 1
Range("A110").Value = Range("A110").Value
ActiveWorkbook.Names("testing").Delete
Application.ScreenUpdating = True
End Sub

Sub MyWait(PauseSeg As Double)
' Orlando Magalhães Filho
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub

 

[Ron de Bruin]

Hi Svilen

Strange, I get this private mail
Where have you post this question Svilen ?

Sorry, you are not authorized to post messages to this list
Reason ([email protected] is not a member of the list)
Subject: Re: Import data from xls file on the web

 

[Svilen Pachedzhiev]

I'm posting in my Outlook Express in
msnews.microsoft.com -> microsoft.public.excel
as a reply to your post.
I don't know why you have such mail?!?

Thanks for the solution, it works for xls files.

Svilen

 

[Ron de Bruin]

Hi Svilen

I'm posting in my Outlook Express in
msnews.microsoft.com -> microsoft.public.excel

Very strange

 

[Svilen Pachedzhiev]

I've just received the same mail:

Sorry, you are not authorized to post messages to this list
Reason ([email protected] is not a member of the list)
Subject: Re: Import data from xls file on the web


Svilen

 

[Ron de Bruin]

3 mails now for me Svilen

Thanks for the feedback

 

[topola]

Dank je Ron,
It works great now !
Following this path of thinking - can you suggest some way of getting
data from XML files.
Here is the example. Each day I get currencies exchange from the NBP -
National Bank of Poland.

Data tables can be reached:
http://www.nbp.pl/Kursy/TabArch.aspx?n=A235z051206
http://www.nbp.pl/Kursy/TabArch.aspx?n=A236z051207 and so on.

<tr valign="middle">
<td align="left" class="ar1">dolar amerykanski</td>
<td align="right" class="ar1">1&nbsp;USD</td>
<td align="right" class="ar1">3,2742</td>
</tr>

A236 dollar rate was 3.2742 PLN. How to get this?
Groetjes

 

[Ron de Bruin]

Hi topola

Why don't you do this every day

Sub test()
Workbooks.Open "http://www.nbp.pl/Kursy/TabArch.aspx?n=A235z051206"
End Sub

Save this file with the same name every day with the macro
You can now use links to that book or use Vlookup to search for the currencies in all other workbooks

Groeten Ron

 

[Ron de Bruin]

Another way is to use Data>Import External data.....New web query

--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi topola

Why don't you do this every day

Sub test()
Workbooks.Open "http://www.nbp.pl/Kursy/TabArch.aspx?n=A235z051206"
End Sub

Save this file with the same name every day with the macro
You can now use links to that book or use Vlookup to search for the currencies in all other workbooks

Groeten Ron

 

[topola]

My previous information might have been misleading to you. The
document type is text/html - maybe that's why I can not open it in
Excel.

This is the file info:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-2">
<link rel="stylesheet" href="/css/kw.css" type="text/css">
<style type="text/css">

I looks like I should try to find a mechanism for searching withing
strings.
Groeten,
Topola

 

[topola]

You're right Ron, this is XML link.

Workbooks.Open "http://www.nbp.pl/Kursy/xml/a236z051207.xml"

I do it every day to update daily fund's transactions to other
currencies.

Thanks for help,
Tomek

 

[topola]

OK, here is my questions - can I vlookup on a file on the Internet?
That would solve my problem. Thanks,
Topola