Cleaning up Imported Text

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

I wrote a web query to get text data from a Federal Reserve Bank site.
Here's the site:
http://federalreserve.gov/releases/h10/Summary/indexnc_m.txt

The query window #1 contains the above address, window #2 is checked for
"Only the tables", #3 is set at "Full HTML formatting" (when set at "None",
everything went to one column, when set to "Full HTML formatting" results
were same as for Rich text). Under the "Advanced" button I left only "Import
<PRE> blocks into columns checked. The net result is that everthing flowed
into the spreadsheet across 4 columns. When I attempted to chart the
results, a screen told me the data range was too complicated. So I attempted
to delete the two empty columns B & C. They won't delete. I assume this is
why I can't get an accurate scale for my chart. I could sure use a pointer
about this time. Two hours and I've got nothing to show for it!
 
When I created web query in Excel 2002, all the data was imported into
column A. I used formulas in columns B and C to extract the date and the
amount.

For example, in cell B7: =DATEVALUE(LEFT(A7,6))
Format this as a date
IN cell C7: =MID(A7,SEARCH(".",A7)-5,10)+0

Copy the formulas down to the last row of imported data.
Base the chart on the date in columns B and C.
 
Thanks Debra. I apprecaiate that. Only problem is, this is going to be a
repetative process involving a macro that will seek that web site every week
and replace the entire sheet. Won't that whipe out the formula? I don't know
how much of the sheet get's affected when the macro replaces a page.
 
As part of the macro, you can add the formulas, and autofill to the end
of the imported range.
 
Do I just start a macro then copy and paste these instructs to the end of
the code I find when I see it in the editor?
 
John -

Press Alt+F11 to open the VB Editor. In the Project Explorer window,
which lists all the open workbooks, make sure your workbook is selected.
Choose Module from the Insert menu, and paste in this code.

Or you could use the code between Sub and End Sub near the beginning of
an existing macro that processes the data further.

- Jon
 
Back
Top