pick up a value in MOSS 2007 with a macro inExcel 2007

[Laurent]

Hi everybody,

I have a question. We have since last year MOSS 2007 as Portal for our
factory.
Many guys are still working with Excel 2007 and have done some macro to
gather information from different excel files.
Because we are moving everything step for step in Sharepoint we have the
problem that some time the excel file is not anymore an excel file but a list
in sharepoint.
So the question is :

Let say I have a Portal : http://myfactory
In that portal I have a list : http://myfactory/mylist
in myList have some column :
- "hours per Project" which is a value
- "Project number " which is a text
- and so on

I would like to get the info how can I make a macro in excel 2007 to reach
this file and import the value in a sheet or for a formule.
I do not want to import the all list ( which is pretty easy ) and I do not
want to write some value in this list ( i m doing it with InfoPath ). I just
want to be able to pick up the value that I need.

I will really be happy to get some help from you.

Best regards

 

[Joel]

If you have a webpage there ae two methods

1) Open an Internet Explorer Application
2) Perform a webquery. You have the choice of writing a macro or or just
use menu Data - Import External Data - New Webquery.

If you need a macro then turn on the macro recorder from menu Tools - Macro
- Record New Macro. The perform a Webquery (2 above). And finally stop
recording.

 

[Laurent]

Hi,

thanks very much for your quick answer but to be honest I have no realy
experience with Webquery...

Could you please give me more detail wath or where I have to make the query ?
My idea was to get some macro code that I can adapt for my need , because
the macro I m write is making much more.
I would like to implement this part of the macro in the complet one.

Thank for your help !

regards

 

[Joel]

Not all webpages support webqueries. but if yours does then do the following
from the worksheet (record a macro if you need one).

Data - Import External Data - New Webquery

Then put the URL in the address box of the query and press GO. If your page
support a query the press the Arrow with the yellow background for the table
you want the data. Next press the Import Button.

 

[Laurent]

Hi Joel,
unfortunatly it s not working... I can open a webquery and give the address.
than I ma chosing the yellow arrow corresponding to the column I m Looking
for and than nothing except e text in Excel ( in German... : IndexMit
UMSCHALT+EINGABETASTE öffnen Sie das Menü (neues Fenster). )
means something like "IndexMit shift + enter open the menu ( new window ))

I do not know what can I do ?..

BEst regards

 

[Don Guillett]

Perhaps you can post your url and tell what you want.

 

[Laurent]

Hi,
thank for your answer !

post my URL ? you mean from sharepoint ? unfortunatly it s intranet, you can
not reach it from outside.

What I m looking for is quite easy : I m want the code for a macro in excel
2007 to pick up on value in a sharepoint list.

Just give me an exemple of a macro doing this that I can understand how it
works. It would be really nice !

I do not want to import all the list or build some connection but I want to
have the possibility in a macro to chose when and what has to be read on
sharepoint without leaving Excel 2007.

tahnks very much for your help !!!

Best regards

 

[Joel]

Your share point is a webpage the you are accessing using a web browser. The
URL is the address in the webpage where your data is located. One method of
accessing the data is to launch a web browser from excel VBA. Here is very
simple example. You need to understand html objects and data to program
using this method. I can help, but it is difficult without getting direct
access to the html file. I have lots of example and can help. I'm not sure
your experience you ae in programming. It requires more than a novice
understanding of programming.


Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Set ZipCodebutton = Form(0).onsubmit

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub

Hi,
thank for your answer !

post my URL ? you mean from sharepoint ? unfortunatly it s intranet, you can
not reach it from outside.

What I m looking for is quite easy : I m want the code for a macro in excel
2007 to pick up on value in a sharepoint list.

Just give me an exemple of a macro doing this that I can understand how it
works. It would be really nice !

I do not want to import all the list or build some connection but I want to
have the possibility in a macro to chose when and what has to be read on
sharepoint without leaving Excel 2007.

tahnks very much for your help !!!

Best regards

 

[Laurent]

Hi,

thank very much for your help,
I think we are getting closer !

The function :

Set Table = IE.document.getElementsByTagname("Table")

Location = Table(0).Rows(2).innertext
IE.Quit

seems to be on the right direction.
let say if the list in Sharepoint is "Workhours"
the URL is "http://sharepoint/project"
and I want to put the value from the list "Workhours" row 2 column 2 in the
excel sheet cell A1 ( .range("a1") ).

Could give me an exemple ?

I do not need to open a window, this function can happen in background.
Best regards

 

[Joel]

Here is some more examples. I usually add break points into the code and add
Watch items and look at the results. You will see items like cells, rows,
and columns (varis with differentt webpages and tables). I also view the
source code from an internet explorer by going to menu View - Source code
which opens a notepad window.

Tags are
<Mytag ...................................... /MyTag>

or

<Mytag ...................................... />


A class is usually id=abcdef
Sub GetHtml1()

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://sharepoint/project"


'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

RowCount = 1
for each itm in IE.Document.All

.Range("A" & RowCount) = itm.TagName
.Range("B" & RowCount) = left(itm.innertext,1024)
.Range("C" & RowCount) = itm.classname
RowCount = RowCount + 1
next itm

End Sub


Sub GetHtml2()


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://sharepoint/project"


'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")


RowCount = 1
for each itm in Table

.Range("A" & RowCount) = itm.TagName
.Range("B" & RowCount) = left(itm.innertext,1024)
.Range("C" & RowCount) = itm.classname
RowCount = RowCount + 1
next itm

End Sub