So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.

Hi
This will parse out the four text strings ether side of the colons:
Dim TextStrings(1 to 4) as String
Temp = QueryString 'your query output
For i = 1 to 3
TextStrings(i) = Left(Temp, Instr(Temp, ":")-1)
Temp = Right(Temp,Len(Temp)-Instr(Temp, ":"))
next i
TextStrings(4) = Temp

You could do the same thing with each TextStrings(i) based on the " "
rather than the ":". Now extract the bits you want.
regards
Paul

Justin wrote:

So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.

Justin,
How far does TextToColumns get you ?
Or look into Regular Expressions.
http://visualbasic.about.com/od/usin...l/blregexa.htm

You want to resolve a host name from an IP address ?
http://vbnet.mvps.org/code/network/hostnamefromip.htm

NickHK

"Justin" wrote in message
ups.com...
So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.

TextToColumns gets me a #VALUE error.
The code is:

Public Function Text2Columns(ByVal input2use As Range)
Dim output As Range
output = Range("NATDump!B1")
input2use.TextToColumns output, , , , , , , True
End Function

NickHK wrote:
Justin,
How far does TextToColumns get you ?
Or look into Regular Expressions.
http://visualbasic.about.com/od/usin...l/blregexa.htm

You want to resolve a host name from an IP address ?
http://vbnet.mvps.org/code/network/hostnamefromip.htm

NickHK

"Justin" wrote in message
ups.com...
So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.

Justin,
Not sure how you are using that code, but recoding a macro will give you the
arguments.

NickHK
P.S. Your function does not return a value, which is all worksheet functions
can do.

"Justin" wrote in message
ups.com...
TextToColumns gets me a #VALUE error.
The code is:

Public Function Text2Columns(ByVal input2use As Range)
Dim output As Range
output = Range("NATDump!B1")
input2use.TextToColumns output, , , , , , , True
End Function

NickHK wrote:
Justin,
How far does TextToColumns get you ?
Or look into Regular Expressions.
http://visualbasic.about.com/od/usin...l/blregexa.htm

You want to resolve a host name from an IP address ?
http://vbnet.mvps.org/code/network/hostnamefromip.htm

NickHK

"Justin" wrote in message
ups.com...
So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.