How to get external data and Import to excel ?

Absolution

New Member
Joined
Feb 7, 2005
Messages
27
Hello all. Hopefully some of you gurus out there can help me in my current venture.

What I am wanting to do is write a script which will tell Excel to go into a html file, search for specific values, and then return a portion of the data based on what it finds in the document.

I have no code yet as I am trying to find a start point. I can probably get the rest of the code to do the searching and copying (I Hope) but my stumbling block is the first hurdle of how I am going to tell Excel to get this data.

Two points to mention.
1. The files have different names and I would rather just be able to save them to a specific directory and set the thing running on a few of them, but am prepared to do it the slightly longer way around if needs must, by either saving them as a different name, or having the file open when I run the event.
2. The files have the same format, so once I have the starting point, the code should be the same in all cases and I can find the information via keywords.

If anyone has any tips or comments they feel would be useful to me in this task, please feel free to speak your mind as I appreciate all the help I can get.
 

mikeyusa

New Member
Joined
Nov 22, 2005
Messages
3
Same problem

Hey absolution, i'm currently trying to do a very similar thing where i want a vb program running behind excel to consult an IE page for file addresses contained in 3 textboxes.

It would be fine if i had VB running as a standalone program but excel's vb doesn't support the clipboard object as far as i can see.

apparently it's difficult to do the opposite i.e. get the HTML or javascript or whatever code to paste the data back to Excel because the languages don't allow it. Something to do with hacking i think, you can't have client side code writing to files...

This is all absolutely no help to you i know but hopefully someone might see this activity and help us out!

later
 

Absolution

New Member
Joined
Feb 7, 2005
Messages
27
Fingers Crossed, C'mon forum Guru's, I also got a call from my wife who is wanting to do similar in Access, but she is getting data from a saved outlook mail message.

Any hints on these will be much appreciated.
 

mikeyusa

New Member
Joined
Nov 22, 2005
Messages
3
hey absolution, found a solution to my problem while messing around in VB, the command

ActiveSheet.Paste

in Excel actually pastes whatever's on the clipboard, regardless of whether it was put on the clipboard in IE, Excel, or whatever. Good news for me, not sure about you though. However, I read through your problem again, you might find the following code from a few old expts of mine handy.

If they're simple HTML files Excel will have no problem going in and reading them and searching them and returning what you want. As far as i know.

'CODE TO CHECK FOR DODGY FILE NAMES
Filepath="C:\BlahBlah.html"
Open FilePath For Input As #1
If Err <> 0 Then
MsgBox ("Bad File Name. Re-enter")
Err = 0
End If
Close #1

'CODE TO OPEN A GOOD FILE
Dim Lines(100) As String 'Each Array Entry takes a Full Line Of the File

Open "BlahBlah.html" For Input As #1
Do Until EOF(1)
Input #1, Lines(i)
i = i + 1
Loop
Close #1

'Now Loop through the Lines() array and do whatever you need


Hope this is relevant.
Later
 

Absolution

New Member
Joined
Feb 7, 2005
Messages
27
Thanks Mikey, I will give this a shot after Pizza (You have to get your priorities right in this game lol)

But it looks like you have got the idea of where I am heading, thanks mate. If I can get that to work, then the rest of the code is no problem.
 

mikeyusa

New Member
Joined
Nov 22, 2005
Messages
3
oh ya, sorry Absolution but the code checking for bad file names needs the line

On Error resume Next

to work properly. Apologies!
 

Forum statistics

Threads
1,078,070
Messages
5,338,035
Members
399,198
Latest member
PsycholoJackal

Some videos you may like

This Week's Hot Topics

Top