Solved

Import Webpage specific tables into specific cells in excel

Posted on 2006-07-09
12
763 Views
Last Modified: 2011-09-20
Hi,

Imagine I have a webpage which has standard notes which i copy and paste it to the Microsoft Outlook
by clicking New Mail in the Microsoft Outlook Application.  This is a very cumbersome process when we do it on a day to day basis.  I thought of an idea of copying all the correspondence (Around 50 Standard notes) to a Excel Spreadsheet and create a macro to open a particular note by click of a button in the Outlook compose box. (Please find the codes at the end of this page). It works fine.

But the problem I am facing is Whenever there is a change done in starndard notes or a new note is added to the webpage, I will have to either update the content in the exsisting cell or add the new note to a unique cell in the excel spreadsheet.  Changes done in the website very frequently.  I know using the Web Query option and programming in VB this can be done.

I came across this link
"http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp?_r=1"

which gets all the data from the tables stored in a webpage and exports it to excel.  But this code is not complete to work properly.

I need assistance on the Following.

1). to have the code in a running condition.

2). My webpage has many tables (with no labels) of which most of them has stardard notes in it. I want  tables containing standard notes be numbered and exported to a single cell (one table content or html codes within the table to be in a single cell, and not spread over multiple cells).

3). if a new table is added to the webpage, Unique number given to all existing tables should not change. If the table number changes, I will not get the note which i want when I click on a specific button.

This is All I want.

Please get back to me if the above notes are not clear.

Code to open notes stored in specific cell in outlook.

CODE STARTS HERE.

Private Sub CommandButton2_Click()

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    Dim strbody As String
    With ThisWorkbook.Sheets("Sheet1")
        strbody = "Hi" & vbNewLine & vbNewLine & _
                  .Range("A2")  " I want a specific table contents or codes will be excellent to be exported to this cell, Likewise it will be "A2".."A3" and so on upto 50 notes as I said before so that when I click on a particular button it should open the exact notes in the outlook.

 Cell "A2" will look like this <table border ="1"? <tr valign = "top"><td width = 614"><font size = "2" face = "Arial"> Standard notes will start here </td></tr></table>"

    End With
 
    With OutMail
        .To = "noname@xxx.com"
        .CC = ""
        .BCC = ""
        .Subject = "Notes"
        .Body = strbody & vbNewLine & vbNewLine
        .display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub


CODE ENDS HERE.
0
Comment
Question by:dgx2007
  • 7
  • 4
12 Comments
 

Author Comment

by:dgx2007
ID: 17077667
Hi All,

Is there anybody who can help me on this or Let me know if this is ever possible?.

I would appreciate a lot if I get a response on this.

Please let me know if this can be Worked out.

Best Regards,

0
 

Author Comment

by:dgx2007
ID: 17118393
Hi,

First of all I Apologize for any inconvinience caused from my actions. Will never repeat it again.

I have uploaded by files to a external site as advised by you.

Here is the Link below for the Sample Webpage (Cannot Provide the Original, is in the Intranet)...

http://www.nirmaludupa.homestead.com/index.htm

And Here is the Link below for the Excel Sheet...

http://www.nirmaludupa.homestead.com/smple.xls

Once again Thanks to all you people for the Support.

Best Regards,
DGX2007...
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 17123028
Hi DGX2007,

Since no one else has answered, I am going to offer an approach that works quite differently, though solves the stated problem. It uses formulas, rather than Macros. Perhaps you can customize to make suitable.

From your example file, "Sheet1" contains a Web Query, Tables only (,format None).  From the sample web link, this results in table content ("Notes") in A1, A3, etcetera.

The "Notes" sheet was simplified for illustration purposes, and uses the following formula starting in C4:

=HYPERLINK( "mailto:noname@xxx.com?subject=Notes&body="
 &SUBSTITUTE(Sheet1!A1,"&","and"),
 "Correspondence "&(TEXT((ROW(Sheet1!A1)+1)/2,0)) )

The "mailto:" URL syntax should invoke your Outlook automatically, and the URL parameters "body", "subject", or others can be constructed to pass appropriate values.  Because of this syntax, any "&"'s in the notes text must be replaced.

Copy/Paste C4 to C6, C8, etc.  The offsets and row numbers would have to be adjusted to suit your side-by-side arrangement.

   Hope this leads to a solution (I learn something every time I try ... :-),

/Sam M.
0
 

Author Comment

by:dgx2007
ID: 17125240
Hi Sam,

Thanks for your help on this, but that does not solve the problem.

Hi All,

It would be very helpful to me to take action if I come to know whether this idea of pulling the web tables to a excel cell will happen or not...

Please help.

Thanks & Regards,
DGX2007...
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 17125460
The first part of the MS article you reference shows a way to generate a Web Query, which, unfortunately puts each table (Note) in its own line/cell, but this shows how "pulling the web tables" is possible in a raw way, even without the Macro part. In a subsequent step, the reformating or merging of these cells (Notes) together can be achieved with a Macro (or formula).  That would leave the question of matching with the "buttons".

It is not clear how to achieve the following requirement "3) if a new table is added to the webpage, Unique number given to all existing tables should not change."  Is there something about each "Note" that would let it be identified and numbered in a unique way?  Are new Notes added as tables at the end (or beginning)?

If the most important part is to arrange the buttons two at a time, as in your example "Correspondence1  Correspondence2", then a Macro should be able to modify these straightforwardly if you can indicate the algorithm as to how they would match up with the "Notes" tables.

  Sincerely (if awkwardly),

/Sam M.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:dgx2007
ID: 17126135
Hi Sam,

This is how the code looks in the webpage...

<a name ="Correspondence1"></A><b><font size ="2" face = "arial">Correspondence1</font><br>
<table border ="1"><tr valign = "top"><td width = 316"><font size = "2" face = "Arial">This is Correspondence1. When I click button1 This Text (Content within the tag (table) & (/table) should go to the body of the Outlook Mail.</font> </td></tr></table> <br><br><br>


<a name ="Correspondence2"></A><b><font size ="2" face = "arial">Correspondence2</font><br>
<table border ="1"><tr valign = "top"><td width = 316"><font size = "2" face = "Arial">This is Correspondence2. When I click button2 This Text (Content within the tag (table) & (/table) should go to the body of the Outlook Mail.</font> </td></tr></table><br><br><br>

and so on...

There is a unique name for each and every correspondence, but unfortunately outside the table.

Is there any way from which the codes within the table can be pulled to excel cell from the name above the table.

Best Regards,
DGX2007...
0
 

Author Comment

by:dgx2007
ID: 17129942
Hi All,

I think My imagination was too high leave this concept.

Please see below and let me know Whether Can this be Done?...

http://www.nirmaludupa.homestead.com/index.htm (This is the webpage I want to pull codes within <table></table>).

Take a Excel Spreadsheet (I will refer to the above webpage in this Example)

Column A and Column B, In Column A, I will enter the name of the Correspondence (Correspondence1 in Cell A2, Correspondence2 in A3, Correspondence3 in A4 and so on. Is is possible to pull codes within <Table></Table> to the Corresponding cells. i.e. If Correspondence1 is entered in Cell A2, when I click a button, the codes within <table></table> should be imported to Cell B2... which looks like this below...

This should be stored in cell B2 - "<tr valign = "top"><td width = 316"><font size = "2" face = "Arial">This is Correspondence1. When I click button1 This Text (Content within the tag (table) & (/table) should go to the body of the Outlook Mail.</font> </td></tr>"...

If this is possible... I can easily assign buttons and put this code into it...

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    Dim strbody As String
    With ThisWorkbook.Sheets("Sheet1")
        strbody = "Hi" & vbNewLine & vbNewLine & _
                  .Range("B2")

    End With
 
    With OutMail
        .To = "noname@xxx.com"
        .CC = ""
        .BCC = ""
        .Subject = "Notes"
        .Body = strbody & vbNewLine & vbNewLine
        .display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing



Best Regards,
DGX2007...
0
 
LVL 7

Accepted Solution

by:
SamIDRC earned 500 total points
ID: 17131368
DGX2007,

From further testing, the Web Query can be made to pull in the line before the tables by selecting "The entire page".  From your sample, this results in ("Sheet1" tab):

A1: Correspondence1
A2: This is ...

A6: Correspondence2
A7: This is...

etc

On the "Notes" tab, the follow formula retrieves this (just as raw text)

B2: =OFFSET(Sheet1!A$2:A$999,(ROW()-2)*5,0,1,1)

Copy/Paste down

This formula retrieves the title:

A2: =OFFSET(Sheet1!A$1:A$999,(ROW()-2)*5,0,1,1)

Copy/Paste down

If the web page keeps the same format, then the "spacing" should stay the same and offset calculations will work.  If its more variable, we can find further formula refinrements to correct for it.

Our site has disabled Excel Macro's for security, so I cannot test that part for you.  From your code, you may be able to figure this out, or ask the group for specific help.

As an aside, I did manage to have this work as a Hyperlink (for me, though I understand that is not what you need):

A2: =HYPERLINK( "mailto:noname@xxx.com?subject=Notes&body=Hi,%0C%0C"&
  SUBSTITUTE(B2,"&","and")&"%0C%0A",
  OFFSET(Sheet1!A$1:A$999,(ROW()-2)*5,0,1,1))

A sample file has been posted at: http://archive.idrc.ca/examples/mailto.xls

  Cheers,

/Sam M.
0
 

Author Comment

by:dgx2007
ID: 17131678
Hi Sam,

Thanks for your Efforts to Help Me on this. Though This is not what I want, I learnt something from you.

Here goes your 500 Points.

Thanks and Regards,
DGX2007...
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 17131923
Thanks for the sentiment (points optional).

if you can phrase the problem based on cell contents, then I'm sure others can fhelp with the macoo part to feed Outlook.  This type of data-to-email problem would seem to be a common need, so hopeful there will be an increasling number of ways to address this someday.

  Good luck,

/Sam M.
0
 

Author Comment

by:dgx2007
ID: 17132110
Hi Sam,

I think one or the other day You Guys may find out solution to it.

Have a Great Day.

Thanks and Regards,
DGX2007...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is an explanation of a simple data model to help parse a JSON feed
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now