"In here", "from here", and "at here"

I just read the discussed topic "look here vs. look at here": Which one is correct? "Look here" or "Look at here"? It's got me wondering.

What is the reason for not using the preposition at before here?

Is it because here is an adverb and it is wrong to use a preposition before an adverb? What if we use here with its nominal meaning, as in "get away from here", or "It's really hot in here", and then say "Look at here" meaning "Look at this place"?

If it is wrong and there is some other reason why at here is incorrect but in here and from here are correct, what is it?

As Edwin Ashworth explains in his extremely well-informed comments above, the wording "look at here" simply isn't used in standard idiomatic English. But it certainly has been used in various nonstandard and informal varieties of English. Harold Wentworth, American Dialect Dictionary (1949) has this relevant entry:

Look-a-here, Lookit(here), Looky (here), Look 'ee here. Look here; also looka, look at, & used with there & yonder, & independently (Looky, Lookit).

The entry then lists dozens of instances of this cluster of terms from 1852 to 1942, and from Massachusetts to California. With regard to the common wording "Look ahere," it says

This intrusive a is prob[ably] a survivial of the pron[oun] ye or you, "look ye here."

Nevertheless, in writing, it has been expressed as "look at here" on multiple occasions. From Thomas Halliburton, The Attaché: or Sam Slick in London (1856):

"Isn't it the way they speak to each other?" said he ; "doesn't Wellington say, 'Stanley, shall I take wine with you?' and if they do, why shouldn't I? It mayn't be proper for a common Britisher to say so, because they ain't equal; but it's proper for us, for we are, that's a fact ; and if it wa'n't boastin', superior, too, (and look at here, who are these bigs bugs now, and what was they originally?) for we have natur's nobility. Lord, I wish you could hear Steverman talk of them and their ceremonies."

From Rudyard Kipling, Captains Courageous (1897):

"Dunno as thet would ha' made any differ. We're both scared out o' ten years' growth. Oh, Harve, did ye see his head?"

"Did I'? I'll never forget it. But look at here, Dan; it couldn't have been meant. It was only the tide."

From Edith Warton, Ethan Frome (1911):

"Gentleman friend gone back on you? Say, Matt, that's tough! No, I wouldn't be mean enough to tell the other girls. I ain't as low-down as that!" (How Frome hated his cheap banter!) "But look at here, ain't it lucky I got the old man's cutter down there waiting for us?"

From Ring Lardner, The Young Immigrunts (1920):

Wilst participateing in the lordly viands my father halled out his map and give it the up and down.

Look at here he said at lenth they seams to be a choice of 2 main roads between here and Syracuse but 1 of them gos way up north to Oswego wilst the other gos way south to Geneva where as Syracuse is strate east from here you might say so it looks to me like we would save both millage and time if we was to drive strate east through Lyons the way the railway gos.

From James Joyce, Ulysses (1922):

He pushed past them to the files.

Look at here, he said turning. The New York World cabled for a special. Remember that time?

...

Never mind Gumley, Myles Crawford cried angrily. Let Gumley mind the stones, see they don't run away. Look at here. What did Ignatius Gallaher do? I'll tell you. Inspiration of genius. Cabled right away.

From Shelby Foote, Jordan County: A Landscape in Narrative (1954):

"You had this house, Mrs Simmons, and all that went with it. What can I do, me with these bad legs?

"Do? Goodness. Just look at here.” They were sitting face to face, Mrs Lowry holding Ella on her lap, and Mrs Simmons leaned forward and fingered the hem of the baby's dress. She pursed her lips as she did so, nodding positively. “Let me tell you, thats as nice a piece of stitching as ever I saw. Thats one thing you can do."

So even though "look at here" seems to have emerged as a mistaken interpretation of "look-a-here" and "looky here" and "lookit here" (which probably actually arose from "look you [or ye] here"), rather than from an original idiomatic use of the preposition at in the phrase "look at here," people have said it and written it in English for a long time.

Still, if you are trying to sound like a mainstream, normally educated English speaker, you should probably go with "look here" instead of "look at here" when you want someone to look (literally or figuratively) at a particular thing.

Usage of "at home" provides an interesting contrast to usage of "at here." From Kenneth G. Wilson, The Columbia Guide to Standard American English (1993):

home 1 (adv.) is Standard when used with verbs of motion, such as run, go, or return, as in I ran [went, returned] home after lunch, but it is also Standard meaning "at home," where no motion is involved, as in She's been home all day. Some Americans (especially at more elevated levels) and many of the British use at home for this purpose.

So according to Wilson, "She's been at home" is the high-toned way to say "She's been home all day." Nevertheless, "She's been at here all day" is very unlikely to appeal to elevated speakers and writers as an alternative to "She's been here all day."