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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
oh ya, sorry Absolution but the code checking for bad file names needs the line

On Error resume Next

to work properly. Apologies!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top