I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!

try this

shell("C:\windows\notepad.exe")

--


Gary Keramidas
Excel 2003


"Robert Crandal" wrote in message
...
I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!

I've done this many times! In Word (2003), click Insert Field Field Name
= DocVariables... name your variable. Enter a few more...
Hit Alt+F9 to see your DocVariables.


In Excel, Insert Name Define... name your range... NamedRange in Excel
must equal DocVariable in Word.

In Excel, create a Modeul and copy paste this code into the Module:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("BrokerFirstName" ).Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName") .Value =
Range("BrokerLastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

Save and run and life is good.

NOTE: Word 2007 is a bit different. I know it's in there, but I forget
where. Just search for it; you'll find it...
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Gary Keramidas" wrote:

try this

shell("C:\windows\notepad.exe")

--


Gary Keramidas
Excel 2003


"Robert Crandal" wrote in message
...
I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!



.

Hmmm, I will see if I can get your procedure to work for me.

However, your procedure assumes that the Word file already
pre-exists and it must contain these DocVariables. For my purposes
I will need to create a "new" Word file at run time and then transfer
data onto the blank Word file . Will your procedure work for my
scenario??


"ryguy7272" wrote in message
...

I've done this many times! In Word (2003), click Insert Field Field
Name
= DocVariables... name your variable. Enter a few more...
Hit Alt+F9 to see your DocVariables.


In Excel, Insert Name Define... name your range... NamedRange in Excel
must equal DocVariable in Word.

In Excel, create a Modeul and copy paste this code into the Module:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("BrokerFirstName" ).Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName") .Value =
Range("BrokerLastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

Cool, that's the easy part. How can I now transfer text data now
that Notepad is open?



"Gary Keramidas" wrote in message
...
try this

shell("C:\windows\notepad.exe")

--

Something like this will do the job:

Sub test()

Dim hFile As Long
Dim strFile As String
Dim strText As String
Dim lResult As Long

hFile = FreeFile
strFile = "C:\testfile.txt"
strText = "just testing"

Open strFile For Output As #hFile
'do Write here to enclose the text in double quotes
Print #hFile, strText
Close #hFile

'open the file in Wordpad
lResult = Shell("write " & """" & strFile & """", 1)

End Sub


RBS


"Robert Crandal" wrote in message
...
I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!

This code gave me a "Path/File Access Error at the following
line:

Open strFile For Output As #hFile


"RB Smissaert" wrote in message
...
Something like this will do the job:

Sub test()

Dim hFile As Long
Dim strFile As String
Dim strText As String
Dim lResult As Long

hFile = FreeFile
strFile = "C:\testfile.txt"
strText = "just testing"

Open strFile For Output As #hFile
'do Write here to enclose the text in double quotes
Print #hFile, strText
Close #hFile

'open the file in Wordpad
lResult = Shell("write " & """" & strFile & """", 1)

End Sub


RBS


"Robert Crandal" wrote in message
...
I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!

Is your main drive not called C by any chance?
If so pick a different path.
Bear in mind that the file will be produced if it is not already there, but
of course
only if the path makes sense. If the file is already there then a new one
will replace
the old one. If instead you want to keep the old file and append text to it
you will need
to do instead:

Open strFile For Append As #hFile


RBS


"Robert Crandal" wrote in message
...

This code gave me a "Path/File Access Error at the following
line:

Open strFile For Output As #hFile


"RB Smissaert" wrote in message
...
Something like this will do the job:

Sub test()

Dim hFile As Long
Dim strFile As String
Dim strText As String
Dim lResult As Long

hFile = FreeFile
strFile = "C:\testfile.txt"
strText = "just testing"

Open strFile For Output As #hFile
'do Write here to enclose the text in double quotes
Print #hFile, strText
Close #hFile

'open the file in Wordpad
lResult = Shell("write " & """" & strFile & """", 1)

End Sub


RBS


"Robert Crandal" wrote in message
...
I am interested in using VBA to transfer some of my
spreadsheet data as regular text into another application
such as Notepad (or even MS-Word). So, my two
questions a How can I use VBA to start up Notepad?
How can I tell Excel to write data into Notepad??

Or, do you guys think it would be easier to use VBA
to transfer text data from Excel into Word??

thank you!

I do have a C: drive on my computer....and the filename
that you specified "testfile.txt" does not exist in the root of
drive C:, so I believe that your code SHOULD have worked
with no problem. Im not sure why I would get the Path File
Acess error...... 8(


"RB Smissaert" wrote in message
...
Is your main drive not called C by any chance?
If so pick a different path.
Bear in mind that the file will be produced if it is not already there,
but of course
only if the path makes sense. If the file is already there then a new one
will replace
the old one. If instead you want to keep the old file and append text to
it you will need
to do instead:

Open strFile For Append As #hFile


RBS

I remember from years ago that on some PC's there can be a problem writing
text files this way to C:\
Try with a sub-folder.

RBS


"Robert Crandal" wrote in message
...
I do have a C: drive on my computer....and the filename
that you specified "testfile.txt" does not exist in the root of
drive C:, so I believe that your code SHOULD have worked
with no problem. Im not sure why I would get the Path File
Acess error...... 8(


"RB Smissaert" wrote in message
...
Is your main drive not called C by any chance?
If so pick a different path.
Bear in mind that the file will be produced if it is not already there,
but of course
only if the path makes sense. If the file is already there then a new one
will replace
the old one. If instead you want to keep the old file and append text to
it you will need
to do instead:

Open strFile For Append As #hFile


RBS

Guess you've got Vista or later. Try -

strFile = curDir & "\testfile.txt"

Regards,
Peter T



"Robert Crandal" wrote in message
...
I do have a C: drive on my computer....and the filename
that you specified "testfile.txt" does not exist in the root of
drive C:, so I believe that your code SHOULD have worked
with no problem. Im not sure why I would get the Path File
Acess error...... 8(


"RB Smissaert" wrote in message
...
Is your main drive not called C by any chance?
If so pick a different path.
Bear in mind that the file will be produced if it is not already there,
but of course
only if the path makes sense. If the file is already there then a new one
will replace
the old one. If instead you want to keep the old file and append text to
it you will need
to do instead:

Open strFile For Append As #hFile


RBS

Great, your code actually works if I change the strFile line to
the following:

strFile = CurDir & "\testfile.txt" ' Thanks PeterT for this!

I now have another question.....

Do I need to actually create "testfile.txt" to get this to work??
For my purposes, I would simply like to open Notepad and
insert some text, and let the user decide if they want to save
the text file or not. Would that be possible???

Thanks again!

"RB Smissaert" wrote in message
...
Something like this will do the job:

Sub test()

Dim hFile As Long
Dim strFile As String
Dim strText As String
Dim lResult As Long

hFile = FreeFile
strFile = "C:\testfile.txt"
strText = "just testing"

Open strFile For Output As #hFile
'do Write here to enclose the text in double quotes
Print #hFile, strText
Close #hFile

'open the file in Wordpad
lResult = Shell("write " & """" & strFile & """", 1)

End Sub

It is possible without creating the file but it's convoluted API stuff.
Don't bother, write to file as RBS suggests. If you Shell to Notepad you
could go on to Kill (ie delete) the file even with the file still open in
Notepad.

BTW, you don't have to use CurDir, could be to any folder to which you have
access, but note in newer Windows write access is much more limited.
Application.DefaultFilePath might be a better folder to start in. Also you
could name the file (almost) anything you want, doesn't need .txt

Regards,
Peter T


"Robert Crandal" wrote in message
...
Great, your code actually works if I change the strFile line to
the following:

strFile = CurDir & "\testfile.txt" ' Thanks PeterT for this!

I now have another question.....

Do I need to actually create "testfile.txt" to get this to work??
For my purposes, I would simply like to open Notepad and
insert some text, and let the user decide if they want to save
the text file or not. Would that be possible???

Thanks again!

"RB Smissaert" wrote in message
...
Something like this will do the job:

Sub test()

Dim hFile As Long
Dim strFile As String
Dim strText As String
Dim lResult As Long

hFile = FreeFile
strFile = "C:\testfile.txt"
strText = "just testing"

Open strFile For Output As #hFile
'do Write here to enclose the text in double quotes
Print #hFile, strText
Close #hFile

'open the file in Wordpad
lResult = Shell("write " & """" & strFile & """", 1)

End Sub

How about if I use those old school DOS "named pipe" or
output filters in the shell() command?? For example,
something like:

shell("string" notepad.exe) ' ?????

Hmmm, I'm probably barking up the wrong tree here, haha!

"Peter T" <peter_t@discussions wrote in message
...
It is possible without creating the file but it's convoluted API stuff.
Don't bother, write to file as RBS suggests. If you Shell to Notepad you
could go on to Kill (ie delete) the file even with the file still open in
Notepad.

BTW, you don't have to use CurDir, could be to any folder to which you
have access, but note in newer Windows write access is much more limited.
Application.DefaultFilePath might be a better folder to start in. Also you
could name the file (almost) anything you want, doesn't need .txt

Regards,
Peter T