Startup Notepad from Excel???

[Robert Crandal]

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 are: 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!

 

[Gary Keramidas]

try this

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

 

[ryguy7272]

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...

 

[Robert Crandal]

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

 

[Robert Crandal]

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??

 

[RB Smissaert]

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]

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

Open strFile For Output As #hFile

 

[RB Smissaert]

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]

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]

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

 

[Peter T]

Guess you've got Vista or later. Try -

strFile = curDir & "\testfile.txt"

Regards,
Peter T

 

[Robert Crandal]

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!

 

[Peter T]

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]

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!