Examples using SHELL to invoke Notepad, a browser or other application. You can invoke a specific application or you can invoke the application associated with the file type if you use “Run” or “Start” as the application.

SHELL invoked from VBA

Location: http://www.mvps.org/dmcritchie/excel/shell.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

OneRowPerCell -- SHELL to Notepad or IExplore (#----)

This should work, it will output one line per selected cell cells are numbered left to right and down, so selecting a single column should work.

In Excel 2000 you can have multiple selection ranges if you have multiple selection ranges each range will be processed independently in the order that it each was selected:
i.e. A1:A5, A3:C3, C2:D5

If you do not want to incude cells that are blank or only include spaces you can include your own test.
IF cell.text <> "" THEN ...

Option Explicit
Sub OneRowPerCell()
    Dim newrange As Range
    Dim cell As Range
    Dim filename As Variant
    Dim retVal As Variant
    Dim suffix As String
    suffix = ""

 ' Range("B:B").Select   'for testing, normally manually select a column/cells

    Set newrange = Intersect(Selection, ActiveSheet.UsedRange)
                filename = "c:\temp\myfile.txt"
    filename = InputBox("Supply filename for HTML generated from " _
       & "selected range", "Filename for myfile", filename)
    If UCase(Right(filename, 4)) = ".HTM" Then suffix = "<br>"
    Close #1
    Open filename For Output As 1

    For Each cell In newrange
       Print #1, cell.Text & suffix
    Next cell
    Close #1

    '------------------ view results --------------------------.
    If UCase(Right(filename, 4)) = ".HTM" Then
      retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & filename, vbNormalFocus)
    Else
          retVal = Shell("NOTEPAD.EXE " & filename, vbNormalFocus)
    End If
End Sub

One Row Per Cell Per Column (#----)

Sub OneRowPerCellPerColumn()
  Dim newrange As Range
  Dim cell As Range
  Dim filename As Variant
  Dim retVal As Variant
  Dim suffix As String
  suffix = ""
  Dim iCol As Long
  Dim Col_id As String
  Dim colRange As Range
  For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
       Set newrange = Intersect(Cells.Columns(iCol), ActiveSheet.UsedRange)
      Col_id = Left(Cells(1, iCol).Address(0, 0), _
           Len(Cells(1, iCol).Address(0, 0)) - 1)
      filename = "c:\temp\myfile_" & Col_id & ".txt"
      If UCase(Right(filename, 4)) = ".HTM" Then suffix = "<br>"
      Close #1   'standard practice, close before opening
      Open filename For Output As 1
      For Each cell In newrange     'newrange
        If Trim(cell.Text) <> "" Then
           Print #1, cell.Text & suffix
        End If
      Next cell
      Close #1
  Next iCol
End Sub

Simple Read with Input Statements (#read)

Simple example provided by Dermot Balson in programming 2001-08-04

'Code to read file until finished
Open "c:\temp\outdoor.csv" for input as #1
do while not eof(1)
  input #1,x
loop
close

uses the Line Input # statement to read a line from a sequential file and assign it to a variable.
Line Input #1, MyString

Simple Write to a variable or list of variables (#write)

HELP, answer wizard, line write, "Write # Statement"

Open "TESTFILE" For Output As #1    ' Open file for output.
Write #1, "Hello World", 234    ' Write comma-delimited data.
Write #1,    ' Write blank line.
MyDate = #February 12, 1969# : MyNull = Null
Write #1, MyDate ; " is a date"
Close #1    ' Close file.

Write to a TextStream object (#stream)

HELP, ans.wiz, line write, WriteLine Method, "TextStream Object"

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test.")
a.Close

Simple .BAT file to concatenate files (merge files) to new file (#concatenate)

A simple .BAT file to copy 3 files into one output.

     copy part1.html +passlist.html +part3.html  composite.html

Importing a text file into Active Sheet (#import)

This worked with worked with a CSV file with no end of line returns in the file, 6 fields per row and field 4 is a date. Tom Ogilvy, 2001-07-29, programming

Sub ReadCommaSepFile_NoReturns()
Dim myVar As Variant
Dim i As Long
Dim rw As Long
'Open the file for Input
    Open "C:\Test1.TXT" For Input As #1
'Read each line of the text file into the list of variables
    'until the end of the file is reached
    i = 0
    rw = 1
    Do While Not (EOF(1))
        Input #1, myVar
        i = i + 1
        If i = 4 Then
         Cells(rw, i).Value = CDate(myVar)
        Else
         Cells(rw, i).Value = myVar
        End If
        If i = 6 Then
         rw = rw + 1
         i = 0
        End If
    Loop
'Close the file
    Close 1
End Sub

For more information on Importing and Exporting Text files with your own delimiters see Chip Pearson's web pages on export and import.

Save As Text type file, .csv, .txt, .htm, .html (#type)

The followinjg will create a text file for the printer, without any double quotes added.

Sub Macro23()
   dim retval as variant
   ActiveWorkbook.SaveAs filename:="C:\temp\text.txt", _
      FileFormat:=xlTextPrinter, CreateBackup:=False
   '--- show results ----
   retval = Shell("NOTEPAD.EXE " & _
      "c:\temp\text.txt", vbNormalFocus)
End Sub

"FileFormat Property" in Help

Some if not all of the text file choices include the following, interestingly they did not include xlText (seems odd).

xlCSV: Generates commas between cells, quotes around cells with commas.
xlCSVMac
xlCSVMSDOS>
xlCSVWindows
xlHTML: Generates horrid large HTML files.
xlText: (wasn't on properties) Generates print text with quotes around cells with commas.
xlTextMac
xlTextMSDOS
xlTextPrinter: Generates tabbed text for printer without added doublequotes.
xlTextWindows
xlUnicodeText: Looks same as xlTextPrinter

Starting an application based on Filetype (#filetype)

filetypes -- AutoCad (.cad), notepad (.txt), HTML (.htm, .html), Adobe (.pdf), etc.

The file extensions that you have set up for you (or by you) in My Computer, View, Folder Options, File Types, can be used with SHELL so you do not have to specify the program in your code.

If the filename contains spaces, you will have to include it within double quotes. (start Runs a Windows program or an MS-DOS program.) E.g.,

From DOS:
  start c:\copiedsite\dmcritchie\excel\excel.htm
  start "c:\copiedsite\dmcritchie\excel\excel.htm"
  start http://www.mvps.org/dmcritchie/excel/excel.htm
From DOS starting Excel with switches: (291288 - Startup Switches for Microsoft Excel (2002: 291288, 2000: 211481; 97: 159474)
(SAFE Mode, Re-register/Reregister Excel extensions)  )
  Excel.exe /r "C:\test\file.xls"
From VBA:
  Shell "start ""s:\new projects\drawing.dwg"" "
  Shell "start c:\copiedsite\dmcritchie\excel\excel.htm"
  Shell "start ""c:\copiedsite\dmcritchie\excel\excel.htm"""
also look at  GetObject in VBA help.

More VBA:
  Dim pblnHelp As Boolean
  pblnHelp = Shell("start " & " """ & _
     ActiveWorkbook.path & "\help.pdf"" ", vbMaximizedFocus)

Worksheet Solution, thanks to Scott Rubin
                =HYPERLINK("help.pdf","Help and usage information")

Shell vs use of the API ShellExecuteA

Shell requires you to choose an application such as Notepad, or a browser. You can use “Run” though as the application and application associated with the file type will be used.
ShellExecuteA, uses the application associated with the file type, but your VBA will need extra coding.
Examples of both uses in code for XL2HTML macros.

DOS commands that have VBA equivalents (#dos)

YOu do not have to use a DOS command for such things as

DIR Function,: Dir[(pathname[, attributes])]
see dirlist Example. Also see HELP for CurDir. http://www.microsoft.com/office/techinfo/productdoc/2002/en/excel/default.asp?URL=/office/techinfo/productdoc/2002/en/excel/vblr6/vafctdir.htm
ChDrive Statement -- Change drive, ChDrive uses only the first letter, Example: ChDrive "d"
ChDir Statement -- Change Directory: ChDir "D:\WINDOWS\SYSTEM"
CurDir: Current Directory MyPath = CurDir ' Returns "C:\WINDOWS\SYSTEM".
MyPath = CurDir("C") ' Returns "C:\WINDOWS\SYSTEM"
Kill Statementt -- Delete File(s), used like Del,: Example: Kill "*.TXT"
MkDir Directory Statement -- Make Directory
RmDir Directory Statement -- Remove Directory


Change directory or folder. ChDir
Change the drive. ChDrive
Copy a file. FileCopy
Make directory or folder. MkDir
Remove directory or folder. RmDir
Rename a file, directory, or folder. Name
Return current path. CurDir
Return file date/time stamp. FileDateTime
Return file, directory, label attributes. GetAttr
Return file length. FileLen
Return file name or volume label. Dir
Set attribute information for a file. SetAttr

Excel to HTML uses Shell to view results after creating HTML code. [code]
Starting a MS Word document (.doc) with a Shell, replies by Hank Scorpio and Graham S.
Replace text in a text file using VBA in Microsoft Excel, exceltip.com, shell not involved, you specify the file to change.
Shell invoked from VBA (ShellExecuteA), Ivan Moala, explains the ShellExecuteA API function but use of constants may make it more complicated for some people, and easier for others. (viewable in IE, otherwise try a reduced font size)
Browse for Folder, Chip Pearson, ability to specify a starting folder requires using a Windows API to select a folder, and you will also need to set a VBA reference as indicated within.

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on July 27, 2001.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments