Convert a worksheet to a text delimited file

S

Simon Jelinek

Is it possible to convert workheet to a text delimited
with pipes|to seperate the data?

If so how can i do this.

Thanks

Simon
 
S

Stace

Simon Jelinek said:
Is it possible to convert workheet to a text delimited
with pipes|to seperate the data?

If so how can i do this.

Thanks

Simon
Click to expand...

You could save it as a comma seperated variable (CSV) file then use an
editor like TextPad (http://www.textpad.com/) to convert all the commas to
pipes.

Stace.
 
D

Dave Peterson

There are textboxes from the Drawing toolbar and textboxes from the control
toolbox toolbar:

You can extract the text from each like this:

Option Explicit
Sub testme01()

Dim myTB As Shape
Dim OLEObj As OLEObject
Dim myStr As String

'from drawing toolbar
For Each myTB In ActiveSheet.Shapes
If myTB.Type = msoTextBox Then
myStr = myTB.DrawingObject.Caption
'do something with that string
MsgBox myStr & vbLf & myTB.Name
End If
Next myTB

'from control toolbox toolbar
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
myStr = OLEObj.Object.Text
'do something with that string
MsgBox myStr & vbLf & OLEObj.Name
End If
Next OLEObj

End Sub
 
G

grasping@straws

There are textboxes from the Drawing toolbar and textboxes from the control
toolbox toolbar:

You can extract the text from each like this:
Click to expand...

(Help) So, okay - I got this to work - subject to the following
limitation - and I thank you very kindly for your help, Dave P.

The following, tagged onto the end of my routine, does the job -

Dim myTB As Shape
Dim myStr As String
Dim MyDataObj As New DataObject

'from drawing toolbar
For Each myTB In ActiveSheet.Shapes
If myTB.Type = msoTextBox Then
myStr = myTB.DrawingObject.Caption
MyDataObj.SetText (myStr) 'do something with that string
MyDataObj.PutInClipboard
End If
Next myTB

Then goes on to open Word & paste into a previously created
file....BUT....truncates the last few characters I need to copy over
(The text as it has to be formatted runs right around 258 chatacters,
what with spaces & such). Comes out just the wat it did when I
originally tried putting the text in a cell.
I know it's not the clipboard's character limit I'm up against here -
I'm thinkin' it happens when I create it as a Data Object (then it's
subject to Excel '97's 255 char. limit).

Lookin' for more wisdom...hopefully a way around..TIA.

tm
 
D

Dave Peterson

How about this:

Option Explicit

Sub testme()

Dim myTB As Shape
Dim myStr As String
Dim MyDataObj As New DataObject

'from drawing toolbar
For Each myTB In ActiveSheet.Shapes
If myTB.Type = msoTextBox Then
myStr = Mid(myTB.AlternativeText, 11)
MyDataObj.SetText myStr 'do something with that string
MyDataObj.PutInClipboard
End If
Next myTB

End Sub

It seems to work ok in xl2002 (not sure when it .alternativetext was added,
though.) It looks like it's mostly used for web stuff.
 
G

grasping@straws

How about this:
Click to expand...

Wow - thanks for the speedy reply, Dave (...and onna weekend, too!)
This' a project I've initiated @ work...I'll give it a try tomorrow
(Monday) and letcha know.
Now..get away from the damn computer & go outside and enjoy the rest
of the weekend, willya? <VBG>

tm
 
G

grasping@straws

How about this:
Click to expand...

Ahhh...no; XL '97 didn't buy the "AlternativeText" idea - probably not
implimented 'til later...

Got frustrated (again) - looked back on my history with this deal one
last time before giving up & decided hell, the only thing I haven't
tried is a TextBox from the Control Toolbox instead of the one I'd
been dealing with from the Drawing toolbar. So I wiped the old text
box off the worksheet & replaced it with the OLE control one, swapped
out your "DrawingObject" code for the "OLEObject" variant....worked
like a charm !!!!

I played around to make sure - added a couple of good paragraphs to
the new TextBox - and it ALL came over to the text file nicely; the
only real difference being that from the Control TextBox, the breaks &
carriage returns don't make the crossing (as they did from the Drawing
TextBox) - but a quick Word Wrap in the resultant text fle smooths
that out nicely.

Thanks (BIG TIME!) for all the help,Dave. This is what I love about
usenet - when things get shared & stuff gets done, one helping
another...it's what it's for....


tm
 
G

grasping@straws

I bet that this would have worked for you, too. (maybe go back???)

http://support.microsoft.com/default.aspx?id=148815
How to Copy Text to TextBoxes Using the Characters Method

(sorry, I didn't mention it before.)
Click to expand...
Hmmm...no. Gave it a try, but looping through the characters, 250 per,
and copying to the Clipboard resulted in only the last 250 being
swapped over to my text file - each loop replaces the previous one.
Could work in different circuimstances, I s'pose, requiring different
results - say if you wrote a line at a time over to incremental line
numbers.

tm
 
D

Dave Peterson

How about looping through the contents 250 at a time--but instead of plopping
them into the clipboard, concatenate them yourself into a giant string:

Stealing from the code in the KB and your original code:

Option Explicit
Sub textboxToClipBoard()

Dim iCtr As Long
Dim txtBox1 As TextBox
Dim myStr As String
Dim MyDataObj As New DataObject

Set txtBox1 = ActiveSheet.TextBoxes("text box 1")

myStr = ""
For iCtr = 1 To txtBox1.Characters.Count Step 250
myStr = myStr & txtBox1.Characters(Start:=iCtr, Length:=250).Text
Next iCtr

MsgBox Len(myStr) & vbLf & myStr

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub

=======
Now, you're going to be mad that you converted all your textboxes????
 
G

grasping@straws

Now, you're going to be mad that you converted all your textboxes????
Click to expand...

Heh....no, 'cause I haven't converted 'em all, yet...
And I.ve been saving incrimental versions as I go, so I cal always fal
back to the last that worked best. I'll give yer latest idea a try &
let ya know.
Oh - and thanks, again!

tm
 
Top