Re: WOT: Excel Question

nd replace to

n into

Or you could have done it in Excel VBA in about 15 seconds....!! (Suitable for framing...)

Sub getlinks() ' ' GetLinks Macro ' Macro recorded 2/2/2008 by mpm

For Each hl In ActiveSheet.Hyperlinks Cells(hl.Parent.Row, 3).Value =3D hl.Address Next hl

End Sub

If I had Excel. Some of the files would have to be exported anyway, because they were for 10 year old, or older files. A few are over 25 years old, and in a different disk format for a 8 bit CPU.

All I do is add a blank column on the right of the database, and export it wit tab delimiters.

A quick search & replace to convert the double tabs to

A second search and replace converts the single tabs to

Then you cut from the end of the bottom line and paste it to the start of the first line. If the exported file is clean, it takes seconds to convert in Wordpad. If there are empty cells, you have to remove the extra but I avoid that problem by using a dash in any blank cell before it is exported. A third search & replace from

- to cleans that up, if you don't want to see the dashes.

I normally use a dash to show the cell is supposed to be empty, and it doesn't cause problems with sorts. My first database was program was written to run on a Commodore 64, and used to track the inventory for my shop. Because of the limited RAM (38 kB), I had to separate it into a couple categories. That program could load another that printed a drawer liner for the old metal framed Akro Mills parts cabinets. You just saved the changes and clicked "Need Drawer Liner?". It also printed labels for boxes by selecting "Need Labels?". It was inspired by another program that was a pain in the ASCII to use. Every task required a different program, and was written to drive only one COmmodore printer. It padded the lines to 80 columns, and didn't use any carriage returns or line feeds, so other printers tried to print the entire database onto a single line. It had no search feature, so you had to scroll through all the parts. Then you had the item number. Next, you had to load the edit module and enter the item number, and so on. I deleted over 80% of the original code and combined similar routines. By the time I finished, the entire program was smaller than the main module of the original. It ran about 20 times faster, and allowed you to use any function at any time. I used that program for almost 15 years, before I moved on to a PC.

--
http://improve-usenet.org/index.html

aioe.org, Goggle Groups, and Web TV users must request to be white
listed, or I will not see your messages.

If you have broadband, your ISP may have a NNTP news server included in
your account: http://www.usenettools.net/ISP.htm


There are two kinds of people on this earth:
The crazy, and the insane.
The first sign of insanity is denying that you\'re crazy.

Jeez. You could be a bit farther behind, but not in this life.

The top row of the spreadsheet, which most folks use as a "header field row", most certainly CAN be imported along with the data via comma delimited ASCII.

Yet it still was not the most efficient way of doing it.

Hey, don't pick on Mike!! It works. He should at least get a few points for resolve and determination... :) (Although I agree I would personally never do the task that way...)

Sounds like he's talking about scraping HTML web sites to make a database. If so, then the first tool of choice would be: Webscraper Lite (by Velocityscape). One of the coolest things I've ever seen. (Well, not really, but it is pretty awesome!)

Free to try, I think?

-mpm

Actually, I was working in the other direction. Scan a printed index. clean up the errors made when someone complied hundreds of separate indexes. Convert to a comma delimited text file, and import into a database. Sort the files, verify the data, and export as tab delimited. Use search & replace to convert that file into the body of a table. Past that into a HTML document, then create a PDF.

here is a sample: for old manuals for turntable and wire recorder published by Sams Photofacts, but no longer included in their printed, or online database.

95% of the time was spent converting the paper into the edited text files. I had to scan several pages from each manual, then use Paper port to convert the scan to text. Next, I had to paste all of it into one file, edit it. After that, it was a quick job to import it, sort it, export it, convert it, the print it to PDF.

I run into a lot of non standard databases, including 25 year old files on Commodore equipment. If I can read the file, I can save it and convert it. If I can't, I can print it, scan it and start over. Most of it is very trivial, and takes very little time if you attack the problem logically, from the start. Best of all, I don't have to keep buying software for a job or two. I can have the work completed in less time than it takes to install some bloated programs. :)

--
http://improve-usenet.org/index.html

aioe.org, Goggle Groups, and Web TV users must request to be white
listed, or I will not see your messages.

If you have broadband, your ISP may have a NNTP news server included in
your account: http://www.usenettools.net/ISP.htm


There are two kinds of people on this earth:
The crazy, and the insane.
The first sign of insanity is denying that you\'re crazy.

ld

=EF=BF=BD

I recall in a pinch once, I FAXED myself a document using dial-up, and my copy of WinFax Pro had OCR. It was ugly, but it worked.

Also, many of the new business-level photocopiers out there today have direct scan to PDF capability. Very, very, very cool. Last year, I scanned a 1,000+ page document in about 15 minutes total! (25 Megabytes, no sweat.) The photocopier even emailed it to me at my office!!

The document was TechNote-101 describing the Longley-Rice RF propagation model. Very rare document - hard to find. (Even Global doesn't have this version.) A colleague, who knew & worked with Rice at the Boulder Labs, loaned me his original.

I see on the radar you guys are about ready to start drying out.(?) Hope your property did not flood. Some of what's on the news looks pretty bad...

-mpm

I believe that. I generally scan at 400 DPI for OCR, then use the tool in Paper Port to straighten it as much as possible. Even a couple pixel skew can cause lots of errors. Also, 'Remove stray dots' makes a big difference. A failing or tired CFL lamp in the scanner can reduce the contrast. That's why I grab every free flatbed I can, for spare parts. Who cares if it's parallel port, if it's never been used? The CFL motors and other parts fit a lot of other scanners. :)

I can see some uses, but a lot of what I scan has to be cleaned up torn edges, notes and wear marks.

No flood, but my electric was out for a few minutes shy of 20 hours. Did you get things taken care of in JAX?

--
http://improve-usenet.org/index.html

aioe.org, Goggle Groups, and Web TV users must request to be white
listed, or I will not see your messages.

If you have broadband, your ISP may have a NNTP news server included in
your account: http://www.usenettools.net/ISP.htm


There are two kinds of people on this earth:
The crazy, and the insane.
The first sign of insanity is denying that you\'re crazy.