?
Solved

Importing html data

Posted on 2012-08-29
16
Medium Priority
?
431 Views
Last Modified: 2012-08-29
Hi guys,

I a html page there is data organized as follows:

     Field1(text)  Field2(nb)      Field3 (date)      Field4(hyperlink)
              A      1      08/06/2011               A1.doc
              B      2      28/07/2010      
              C      3      27/07/2010              C3.doc
              D      4      19/03/2009      
               E      5      12/05/2003               E5.doc
 
I tried to import it using imports(Excel and Access 2010) and copy/paste methods but no luck.
For example in Excel when after importing the data I get something like this:

Field1
Field2
Field3
Field4
A
1
08/06/2011
A1.doc
B
2
28/07/2010
C
3
27/07/2010
C3.doc
D
4
19/03/2009
E
5
12/05/2003
E5.doc

Can someone give me a VBA code in Excel 2010 to arrange the data like in the initial table - any other solution would be appreciated?

Thank you
0
Comment
Question by:marian68
  • 8
  • 8
16 Comments
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38345352
Try Excel web query.

http://www.techrepublic.com/article/pull-data-into-microsoft-excel-with-web-queries/6115870

Open in new window

0
 

Author Comment

by:marian68
ID: 38346090
I doesn't work. The data is not posted as a table.
I found the following question, which is the same with mine, in another forum but without answer too.
The data on the web page is shown vertically and therefore the fields are put into separate Excel rows instead of columns when a run a data query. Is there a way to tell the query to make each line a new column?

Thank you
0
 

Author Comment

by:marian68
ID: 38346178
Any other ideas ?

Thank you
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.

 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38346243
What I normally do is Select All (CTRL + A) on the web page then paste straight (not paste special) in Excel. Most of those that look like tables paste as tables and you can just delete those around it.
0
 

Author Comment

by:marian68
ID: 38346279
I have already try it. No results. The data is shown vertically and therefore the fields are put into separate Excel rows instead of columns
My only hope is an Excel VBA code for transposing every record.

Thank you
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38346977
Here's a quick and dirty macro.
transposemacro.xlsm
0
 

Author Comment

by:marian68
ID: 38347527
Hi techfanatic,
 
Sorry for delay.
A little problem. After pushing the button some dates wrongly are changed.
Ex: from the date in original table 01/06/1982 I got 1982-01-06 after transposing,
from 11/02/1980 I got 1980-11-02. I changed the setting and I got 02-11-1980. So from 11/02/1980 I got 01/11/1980
So the code changes for some dates the order from day-month-year to year-day-month.
Can you please help me and solve the problem?
Thank you and sorry for my poor english
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38347539
Can you post sample workbook with those data causing errors so I can debug and make adjustments on the code?
0
 

Author Comment

by:marian68
ID: 38347642
Here you are the file.
Thank you
SAMPLE.xlsm
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38347796
Try this one.
SAMPLE.xlsm
0
 

Author Comment

by:marian68
ID: 38348362
Sorry for delay, I had to leave.
I tried your new code but I am receiving an run-time error 13 Type mismatch and the following part of the code is yellow:
"ws.Cells(lastrow, 4).Value = DateValue((Mid(cell.Value, 4, 2) & "/" & Left(cell.Value, 2) & "/" & Right(cell.Value, 4)))"

Thanks a lot,
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38348367
This procedure might have halted somewhere during the process. Can you try to identify the next cell it was supposed to transpose (before it stopped) and provide the value of the cell?
0
 

Author Comment

by:marian68
ID: 38348387
NO. 1 CN IS BELIEVING/C'EST CROIRE & DESIGN
552937
01/02/1990

Thank you
0
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 2000 total points
ID: 38348395
I hope this one works.
SAMPLE-1-.xlsm
0
 

Author Closing Comment

by:marian68
ID: 38348417
Yes. It works.
Thanks a lot, very much appreciated,
Have a nice evening,
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38348422
You're welcome. The code I've written like what I said is quick and dirty and in no way foolproof. However, you can use it as a starting point and improve as you learn. :) Have a good night as well.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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