?
Solved

Import Webpage specific tables into specific cells in excel

Posted on 2006-07-09
12
Medium Priority
?
772 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 

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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Progress
Introduction to Processes

765 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