Docstoc

Vba Attach Excel Worksheet

Document Sample
Vba Attach Excel Worksheet Powered By Docstoc
					Manipulating data files in Excel
Introduction.
The purpose of this document is to show how you can, with a few simple macros, manipulate
data files which you have created or been given. The files may be in Excel already or may be
Comma Separated Variable (CSV) files. Most computers default to opening CSV files as a
single worksheet in Excel so, apart from remembering to save them as an Excel workbook
they can be treated the same.
The layout of the file may contain all or
some of the items shown in the diagram.
We will only be concerned with the Data         General Information
and its associated column headings.
The data section will contain one or            Column Headings
more columns and one or more rows. In
some cases all the rows will have data,         Data
in some cases there will be blanks. Our
macros will have to be able to deal with
all possibilities.
There will be times when the problem to
be solved is very specific and times
when there are several sets of data that
need to be processed so it is important to
be clear what your macro will do and
what it won‟t. This document will               General Information
contain several macros which you can
copy, paste and modify.
Types of problems.
      Extracting a subset of data.
      Merging two sets of data into one
      Splitting a data set into two or more parts.
      Performing calculations on each record in the data.
                        A Generic vie w of data processing programs

The following diagram shows the layout of a typical Data Processing Program. Each part is
simple or more complicated depending on the particular problem and the data given

1: Start up operations include such things
                                                                        Start
as finding where the data starts, copying
over column headings if required and
handling the first record(s) if these require         1: Perform any start up operations and
a different procedure from the rest                            look for first record
2: This type of process is repetitive so
before we enter the loop we check whether
we have a record to process. Although it                           2: Do I have
may seem odd we check the first record as                          any records                   No
well so the macro will work even with an                            to process
empty list. Deciding if there are any more
records can be simple if there are no
blanks in the list or there is a known last
record. Otherwise some rule has to be                                   Yes
devised.
3: Depending on the job not all records
may be required, so we ignore any we                               3: Do I want
aren‟t interested in and skip to step 5          No                 to process
                                                                   this record?
4: Processing may include joining fields or
doing arithmetic on values. If appropriate,
we write out the result of this processing
remembering where we write each result                                 Yes
as it may not be on the same line as the
incoming data, or even the same sheet.
                                                        4: Perform any processing required
5: Getting the next record in this type of              Write the result in the next available
process is usually looking at the line below                            space
the current one.
6: When all the records have been                         5: Look for next record and loop
processed then there may still be some
                                                                   back to step 2
more information to be written. Eg
Number of records written or column
totals. In the case of merging two lists
when the end of one list is reached all the      6: Perform any finishing operations
remaining records in the other can be
copied en masse.                                                      Stop
Excel Basics
Opening and saving files.
If your data has been sent to you as a CSV (Comma Separated Variable) File the if you
double click on it in Windows Explorer it will automatically be opened up in Excel. To open
it from within Excel, the in the File Open window, change the Files of Type option at the
bottom of the window to Text Files. Having opened the CSV file it is best to save it as an
Excel File or to move the sheet created into an Excel File.
Moving and/or copying worksheets.
At the bottom of the Excel screen there are a row of tabs, one for each sheet. Right Clicking
on this tab brings up a sub menu which enables you to rename the sheet and or move or copy
it to a new location. Thus if you data comes as several CSV files you can open an Excel
Work book, then open the CSV files in turn and move them into your main work book.
Creating a place for your Macros
On then Menu line click on Tools then Macro (click the double arrow if Macro doesn‟t
appear) then Visual Basic Editor. (Or hold down the <Alt> key and press the <F11> key)
This opens a separate program linked to the workbook. From its menu line choose Insert and
then Module. This brings up a sheet on which you can write your macros.
Important rule of macros
There is never just one solution to creation a macro
If it works it’s right if it doesn’t it’s wrong
Writing a macro
All Macros in this course will be based on the file data.xls.
For the First Macro we will use the data on the names sheet
Every macro has to have a single „word‟ name. Let us begin by writing a macro to count the
number if items in a list.(There may be simpler ways of doing this but this is just an example
of how macros work.) We‟ll call the macro count. So in your module type
sub count
and press <Enter>
                                                                              Start
Excel will respond by changing this to
Sub count()                                                 1: Perform any start up operations and
                                                                     look for first record


End Sub
                                                                         2: Do I have
Note that some words are in blue. These are                              any records                   No
special words which have meaning to Excel                                 to process
and these words can only be used for that
purpose.
                                                                             Yes
Following the plan to the left we begin with
step one, start up operations and getting the
first record. Let us assume that the data is in
                                                                         3: Do I want
column C starting at row 5. We will start at           No                  to process
this location and go down column C looking                                this record?
at rows 6,7,8,….. until we find an empty cell
counting as we go. To do this we need to
remember two things, the row we are on and                                   Yes
how many records we have seen so far. We
give a name to each of these pieces of                        4: Perform any processing required
                                                              Write the result in the next available
information, rw for the row and nr for the
                                                                              space
number of records. When we start we have
seen no records so nr = 0 and we are at
                                                                5: Look for next record and loop
row 5 so rw = 5.We enter this information                                back to step 2
in our macro.
Sub count()                                            6: Perform any finishing operations
nr = 0
rw = 5                                                                     Stop
End Sub
As we want to process (ie count) all records step 3 is omitted and step 4 will not need to write
anything as we only write out the answer when we have finished. When we find another
record we can increase the number of records (nr) by one using the statement
nr = nr + 1
How do we know that we have found a record? A record exists if the cell on the worksheet
that we are looking at is not empty. In this case if we do find an empty cell then we have
reached the end of our list.
To look at a cell on the worksheet we refer to it as
Cells(rownumber,columnnumber)
Note that we use column numbers rather than letters so our first record in cell C5 would be
Cells(5,3) or as we remember which row we are in by using rw we can refer to this
first record as Cells(rw,3)The column won‟t change so we just use its number. There is
a special phrase we use to find out if a cell has a value or is empty: this is
IsEmpty(Cells(row,column))
This returns an answer True or False.
The program runs through stages 2,3,4,5, ,2,3,4,5, ……as long as there are records. Ie steps
2,3,4 & 5 are repeated. This is called a loop and the statements we use are as follows
Do While some condition is True


Loop
or
Do Until some condition becomes True


Loop
Getting the next record is a matter of increasing the row number rw by one.
Putting all this together we get.
Sub count()
nr = 0
rw = 5
Do Until IsEmpty(Cells(rw,3))
     nr = nr+1
     rw = rw+1
Loop
End Sub
We now need to perform the finishing operations which in this case is to write out the
answer. In this example we write the words „Number of Records‟ in cell B3 and the Value in
C3 giving the final macro as
Sub count()
nr = 0
rw = 5
Do While IsEmpty(Cells(rw,3))
     nr = nr+1
     rw = rw+1
Loop
Cells(3,2).Value = “Number of Records”
Cells(3,3).value = nr
End Sub
Question: What changes would you make to this macro if the data started in cell A1?
Proble m 1: You have some data starting in cells A1 and B1 consisting of the header line
Name and Grade . Below that there is a list of names (always present) and grades A to E or
blank. You want to make the names of all people with an A grade Bold. Copy all the count
macro and paste it into the module below where it is and rename it bolda as follows
Sub bolda()
nr = 0
rw = 5
Do Until IsEmpty(Cells(rw,3))
     nr = nr+1
     rw = rw+1
Loop
Cells(3,2).Value = “Number of Records”
Cells(3,3).value = nr
End Sub
Delete the unwanted lines referring to the number of records and the answers thus
Sub bolda()
rw = 5
Do Until IsEmpty(Cells(rw,3))
     rw = rw+1
Loop
End Sub
We will be starting from row 2 (row one is the header) in column 1 (we can‟t use column 2 as
it may contain blanks)
Sub bolda()
rw = 2
Do Until IsEmpty(Cells(rw,1))
     rw = rw+1
Loop
End Sub
We now need to introduce a Section 2 Do I need to process this record? This is done with an
IF statement as follows
Sub bolda()
rw = 2
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “A” Then

  End If
     rw = rw+1
Loop
End Sub
Finally we need to make the name Bold which is done as follows.
Sub bolda()
rw = 2
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “A” Then
     Cells(rw,1).Font.Bold = True
  End If
     rw = rw+1
Loop
End Sub
Problem 2: Using the same data as in problem 1 create a list of A grade students in columns E
and F. Call this macro alist.
Proceed as before by copying, pasting, renaming and removing unwanted lines to give
Sub alist()
rw = 2
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “A” Then

  End If
     rw = rw+1
Loop
End Sub
In this problem we have some start up jobs, ie copy the headers to a new location and we will
need to keep track of which row we are going to use to write the current A student. We could
call this information arw Given that the headers are in row 1 then will be row 2 to begin
with.
Sub alist()
rw = 2
arw = 2
Cells(1,5).Value = Cells(1,1).Value
Cells(1,6).Value = Cells(1,2).Value
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “A” Then

  End If
     rw = rw+1
Loop
End Sub
When we do find an A student we want to copy the value to our new location and increase
the value of arw by one as follows.
Sub alist()
rw = 2
arw = 2
Cells(1,5).Value = Cells(1,1).Value
Cells(1,6).Value = Cells(1,2).Value
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “A” Then
           Cells(arw,5).Value = Cells(rw,1).Value
           Cells(arw,6).Value = Cells(rw,2).Value
           arw =arw+1
  End If
     rw = rw+1
Loop
End Sub
How do you know how to make the font bold? What if you wanted to make it Red?
When you want to achieve in your macro some effect which is fairly eas y to do in Excel and
you don‟t know how it is done, create a macro by recording your actions and adapting that.
For example if we had wanted to make the A‟s red rather than bold we could proceed as
follows.
From the menu line choose [Tools] [Macro] [Record New Macro] Accept the defaults by
clicking [OK] A little tool bar will appear on the sheet.
Select a cell change its colour to red and click on the [Stop Recording] button on the small
toolbar.
Go to the Visual Basic Editor, This new macro will be in a module of its own. Find this and
you should see something like the code below.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 04/10/2007 by Jerry
'

'
    Range("A2").Select
    Selection.Font.ColorIndex = 3
End Sub
Lines which begin with an apostrophe („) are merely comments or notes and can be ignored.
The other two lines are the actions you took, first to select a cell and then to change its colour
to red. In our macro we had the line
    Cells(rw,1).Font.Bold = True
If we had wanted it to change the colour to red it would have read
    Cells(rw,1).Font.ColorIndex = 3
To find out which colour has which value go to Visual Basic Help and search for
PatternColorIndex
Proble m 3: We have a list of names, each accompanied by two marks. We wish to add the
marks together and grade the result according to the rule Below 80 “E”, 80 -89 “D”, 90-99
“C”, 100-110 “B” above 110 “A”. Using the work sheet Name-Marks we can see the Data
starts on row 2, the marks are in columns E and F (5 and 6) and the results are to go in
columns G and H (7 and 8) on the same row as the data. So start by copying and pasting
bolda renaming it grade and removing the lines about grade A and the font change. (Note we
use bolda again rather than alist as alist was writing out the answers on different rows.
Sub grade()
rw = 2
Do Until IsEmpty(Cells(rw,1))

       rw = rw+1
Loop
End Sub
The Processing part of the operation is to add together the two marks and write the result to
column 7. This gives us
Sub grade()
rw = 2
Do Until IsEmpty(Cells(rw,1))
     Cells(rw,7).value = Cells(rw,5).value + cells(rw,6).value
     rw = rw+1
Loop
End Sub


Now we have to grade the result according to the rule above and to do this we use a new
Visual Basic Statement Select as follows.
Sub grade()
rw = 2
Do Until IsEmpty(Cells(rw,1))
     Cells(rw,7).value = Cells(rw,5).value + cells(rw,6).value
     Select Case Cells(rw,7).value
        Case 0 to 79
           Cells(rw,8).value = “E”
        Case 80 to 89
           Cells(rw,8).value = “D”
        Case 90 to 99
           Cells(rw,8).value = “C”
        Case 100 to 109
           Cells(rw,8).value = “B”
        Case Else
           Cells(rw,8).value = “A”
     End Select
     rw = rw+1
Loop
End Sub
Alternative solution to Problem 3.
You will have noticed that adding the two cells together could have been don in Excel
without writing a macro IE by either entering a formula =E2+F2 (on row 2) and extending
down or by using the Function SUM as in =SUM(E2:F2). The Grading part can also be done
by using a function but a function that you write yourself. In your module write Function
grade(x)
Excel will respond by adding the end line as follows
Function grade(x)

End Function

Using the lines from the Sub above with minor alterations the Function becomes
Function grade(x)
   Select Case x
       Case 0 to 79
        grade = “E”
       Case 80 to 89
        grade = “D”
       Case 90 to 99
        grade = “C”
       Case 100 to 109
        grade = “B”
       Case Else
        grade = “A”
   End Select
End Function

Then in cell h2 enter =grade(g2) and extend down.
Proble m 4. Sheet Subjects has a list of names each accompanied by two subjects. The aim is
to create a class list for each subject on a separate sheet. There is a blank sheet for each of the
four possible subjects.
Again start with the basic Macro copied and adapted from bolda and called split.
Sub split()
rw = 2
Do Until IsEmpty(Cells(rw,1))

     rw = rw+1
Loop
End Sub
We now look at the values in columns 3 and 4 in turn and write them out on the correct sheet
in the correct place. As in the alist macro we need to remember where we can write the result
but this time we need four counters one for each sheet. For convenience we name the
counters after the courses.
Sub split()
rw = 2
cabc50=1
cabc65=1
cpqr51=1
cpqr73=1
Do Until IsEmpty(Cells(rw,1))

     rw = rw+1
Loop
End Sub
Now using the Select statement again we look at course 1 (column 3) then Course 2 (Column
4)
Note that you can copy and paste parts of the macro and then edit the small changes
necessary. If you are using an electronic version of this document you can copy and paste the
macro from Word to Excel)
Sub split()
rw = 2
cabc50=1
cabc65=1
cpqr51=1
cpqr73=1
Do Until IsEmpty(Cells(rw,1))
   Select Case Cells(rw,3).value
      Case “ABC50”
         Sheets(“ABC50”).Cells(cabc50,1).value   = Cells(rw,1).value
         Sheets(“ABC50”).Cells(cabc50,2).value   = Cells(rw,2).value
         Cabc50=cabc50+1
      Case “ABC65”
         Sheets(“ABC65”).Cells(cabc65,1).value   = Cells(rw,1).value
         Sheets(“ABC65”).Cells(cabc65,2).value   = Cells(rw,2).value
         Cabc65=cabc65+1
      Case “PQR51”
         Sheets(“PQR51”).Cells(cpqr51,1).value   = Cells(rw,1).value
         Sheets(“PQR51”).Cells(cpqr51,2).value   = Cells(rw,2).value
         cpqr51=cpqr51+1
      Case “PQR73”
         Sheets(“PQR73”).Cells(cpqr73,1).value   = Cells(rw,1).value
         Sheets(“PQR73”).Cells(cpqr73,2).value   = Cells(rw,2).value
         cpqr73=cpqr73+1
   End Select

  Select Case Cells(rw,4).value
     Case “ABC50”
        Sheets(“ABC50”).Cells(cabc50,1).value    = Cells(rw,1).value
        Sheets(“ABC50”).Cells(cabc50,2).value    = Cells(rw,2).value
        Cabc50=cabc50+1
     Case “ABC65”
        Sheets(“ABC65”).Cells(cabc65,1).value    = Cells(rw,1).value
        Sheets(“ABC65”).Cells(cabc65,2).value    = Cells(rw,2).value
        Cabc65=cabc65+1
     Case “PQR51”
        Sheets(“PQR51”).Cells(cpqr51,1).value    = Cells(rw,1).value
        Sheets(“PQR51”).Cells(cpqr51,2).value    = Cells(rw,2).value
        cpqr51=cpqr51+1
     Case “PQR73”
        Sheets(“PQR73”).Cells(cpqr73,1).value    = Cells(rw,1).value
        Sheets(“PQR73”).Cells(cpqr73,2).value    = Cells(rw,2).value
        cpqr73=cpqr73+1
  End Select

   rw = rw+1
Loop
End Sub
Proble m 5: Processing in two directions. Sheet Weather has some data showing the condition
at four periods in a day for a month. The aim is to create a list of the times it was raining.
Once again this is a problem of going down a list, processing each line but the processing
involves looking a list of values in successive columns.
As it involves writing out a list of answers we can start with the alist macro deleting those
lines which are specific to that problem.
Sub raining()
rw = 2
arw = 2
Do Until IsEmpty(Cells(rw,1))
  If           Then


               arw =arw+1
  End If
     rw = rw+1
Loop
End Sub


The processing involves looking at the value in column 2, if it is rain the write out the date
(column 1) and Morning, Then look at column 3 if it is rain the write out the date (column 1)
and Afternoon, and so on. If we were only looking at col 2 the macro could look like this
Sub raining ()
rw = 2
arw = 2
Do Until IsEmpty(Cells(rw,1))
  If Cells(rw,2).Value = “Rain” Then
     Cells(arw,7).Value = Cells(arw,1).Value
     Cells(arw,8).Value = “Morning”
     arw =arw+1
  End If
  rw = rw+1
Loop
End Sub
We could then copy the lines from IF to End IF three times changing the column number and
the output. But there is a simpler method. Remembering that “Morning” is in Cells(1,2),
“Afternoon” in Cells (1,3) etc we write
Sub raining ()
rw = 2
arw = 2
Do Until IsEmpty(Cells(rw,1))
  For cl = 2 To 5
     If Cells(rw,cl).Value = “Rain” Then
        Cells(arw,7).Value = Cells(rw,1).Value
        Cells(arw,8).Value = Cells(1,cl).Value
        arw =arw+1
     End If
  Next
  rw = rw+1
Loop
End Sub
The For Loop works by doing everything inside with CL set to 2, Then 3 and so on until 5
We could have used the For Loop in the previous example to give us looking at cols 3 then 4
Sub split2()
rw = 2
cabc50=1
cabc65=1
cpqr51=1
cpqr73=1
Do Until IsEmpty(Cells(rw,1))
   For cl = 3 to 4
   Select Case Cells(rw,cl).value
      Case “ABC50”
         Sheets(“ABC50”).Cells(cabc50,1).value                = Cells(rw,1).value
         Sheets(“ABC50”).Cells(cabc50,2).value                = Cells(rw,2).value
         cabc50=cabc50+1
      Case “ABC65”
         Sheets(“ABC65”).Cells(cabc65,1).value                = Cells(rw,1).value
         Sheets(“ABC65”).Cells(cabc65,2).value                = Cells(rw,2).value
         cabc65=cabc65+1
      Case “PQR51”
         Sheets(“PQR51”).Cells(cpqr51,1).value                = Cells(rw,1).value
         Sheets(“PQR51”).Cells(cpqr51,2).value                = Cells(rw,2).value
         cpqr51=cpqr51+1
      Case “PQR73”
         Sheets(“PQR73”).Cells(cpqr73,1).value                = Cells(rw,1).value
         Sheets(“PQR73”).Cells(cpqr73,2).value                = Cells(rw,2).value
         cpqr73=cpqr73+1
   End Select
   Next
   rw = rw+1
Loop
End Sub
Proble m 6: You wish to create a list of files and the folders they are in, retaining information
about file size and date created. Whilst Windows Explorer can show you this information you
can‟t easily print out a list of files in a given folder and its sub folders.

Using the Dir at a command prompt (See Appendix 2) prints out a list as a text file which
can be imported into Excel (See sheet doclist).

We are going to write a macro which finds the files and the name of the folder they are in,
combines the results and prints out a list with the required information.

Rather than adapting an existing Macro I am going to go through the process from start to
finish building up the macro stage by stage. Most of the code will be code that has been used
before but where new commands are introduced I‟ll offer explanation. Note also that
Directory and Folder are synonymous and I‟ll use both.

The data looks some thing like this

Volume in drive C has no label.
Volume Serial Number is 88DC-005F


Directory of C:\docs


02/11/2004      09:18             <DIR>                  .
02/11/2004      09:18             <DIR>                  ..
18/12/2001      12:29                        65,024      afm.doc
20/02/2002      14:11                        55,296      afm2.doc
06/06/2000      07:01                     2,949,120      Win98 modems - jan00.doc
3 File(s)             3,657,216                          bytes


Directory of C:\docs\books\access


15/10/2003      07:51             <DIR>                  .
15/10/2003      07:51             <DIR>                  ..
11/09/2001      07:55                     3,096,064      access.doc
11/11/1999      10:24                        55,296      Accessdata.xls
10/11/1999      13:46                           311      Courses.txt
3 File(s)             3,151,671                          bytes


And ends like this.

Total Files Listed:
267 File(s)            61,879,470                        bytes
71 Dir(s)      20,859,486,208                            bytes free



Things to note straight away is that there are blank lines in the data and some lines contain
one sort of information and other lines contain different sorts of information.
For Example: Looking at the data we see that we have the directory name in column 1
preceded by the words “Directory of”
 Directory of C:\docs\books\access

and then on subsequent rows we have date and size information in column 1 with file name
in column 2.
 11/11/1999        10:24                  55,296         Accessdata.xls

These are mixed in with some rows with the sub folder name in column2 and the word
<DIR> appearing in column 1.
 01/07/2004        11:01          <DIR>                  fax

When all files in a given folder have been listed there is a row of the form
 3 File(s)            3,151,671                          bytes

There are also some blank lines. Right at the end there is a line “Total Files Listed”
 Total Files Listed:



What we have to do therefore is to

Start at the top

Look for a line beginning Directory of.

Remember this name as until this type of line is repeated all subsequent lines with file names
in them are in this folder.

Search subsequent rows for files, when found combine directory name and file name, write
the result somewhere and attach file information.

Continue changing directory name as appropriate until we reach a line with Total Files in
column 1.

We have to decide where to put the results, so let us choose column 4 for the filename and
column 5 for the file information.

We need variables to store which row we are searching, which row we are using for the
answer and what the current directory name is. We shall use rin, rout and direct for these

To start with then we must set the two counters to 1 (the default is 0) and create a loop to
look at all lines stopping when we reach on starting Total in column 1

Note any thing in Bold Italic is not code, but just a note to remind us that we have to write
code in that place.
Sub filefind()
     rin = 1
     rout = 1
     Do Until (row starts with Total)
        Do something
        rin = rin + 1
     Loop
End Sub


There are many functions in Visual Basic (See Appendix 1 for some of these

The function Left(string,number) will give the first number of characters of a string

So the Macro would now read

Sub filefind()
     rin = 1
     rout = 1
     Do Until Left(Cells(rin,1).Value,5) = “Total”
        „Do something
        rin = rin + 1
     Loop
End Sub

The single quote „ turns the rest of that line into a comment so the macro above could be
entered into Excel and would work, albeit apparently doing nothing. Just to prove to
ourselves that it is working we will add another line to tell us the row number where t he
process stops.

Sub filefind()
     rin = 1
     rout = 1
     Do Until Left(Cells(rin,1).Value,5) = “Total”
        „Do something
        rin = rin + 1
     Loop
     Msgbox rin
End Sub

The Function Msgbox prints out the message, in this case the value of rin and pauses until
you click OK Copy the Macro below and run it from the doclist worksheet. Make a note of
the value and check that that row is the one we were looking for.

Now for the do something

   1. If the line begins “Directory of” we want to remember the value ie change the value
      of direct and the go on to next line

   2. If column 1 contains the word <DIR>, File, Volume or is blank we wish to ignore that
      line
   3. Other wise we wish to copy the relevant info into columns 4 and 5

First stage is to pick up the lines with Directory of in them

Sub filefind()
     rin = 1
     rout = 1
     Do Until Left(Cells(rin,1).Value,5) = “Total”
        If Left(Cells(rin,1).Value,9) = “Directory” Then
          direct = Mid(Cells(rin,1).Value,14)
          Msgbox direct
        Else
          „Do something
        End if
        rin = rin + 1
     Loop
     Msgbox rin
End Sub


This Version contains two new things.

The Function Mid(string,value) which returns all the characters of a string starting and the
position given by value, in our case 14. Ie it ignores the 13 characters “Directory of “
(spaces count as characters) and returns every thing else.

We have added and Else clause into the If statement. This is what happens if the condition is
not True

Run this Macro and study the output of the Msgboxes. You should notice that it seems to
repeat the folder name C:\docs\misc\chris\nextye several times. This must be an
error as each Folder name should be unique. So we need to examine the data to see what is
happening. If you look at row 234 you will notice that the directory name is too long for
column 1 and has spilt over into column 2. This is a result of the way the data was read in.
We need therefore to add on to the value of direct anything that is in column 2 of that row. As
Follows

Note we use & to join together two string values
Sub filefind()
     rin = 1
     rout = 1
     Do Until Left(Cells(rin,1).Value,5) = “Total”
        If Left(Cells(rin,1).Value,9) = “Directory” Then
           direct = Mid(Cells(rin,1).Value,14) & Cells(rin,2).Value
           Msgbox direct
        Else
           „Do something
        End if
        rin = rin + 1
     Loop
     Msgbox rin
End Sub


We now need to identify the rows that we wish to process. In fact it is easier in this case to
identify the rows we wish to ignore. These are Lines containing in column 1 the words
Volume, DIR or File or lines which are blank. Blank lines can be found using the IsEmpty
function we have used before

To see if a string contains another string we can use the Instr function which returns a value
of 0 if the second string doesn‟t exist of the value of the starting point if it does See Appendix
1

So we start with a variable called process which we set to True, then make the four tests, and
set the value of process to false if any of the tests fail as follows.

       Process = True
       If IsEmpty(Cells(rin,1)) Then process = false
       If Instr(Cells(rin,1).Value, “Vol”) > 0 Then process = false
       If Instr(Cells(rin,1).Value, “DIR”) > 0 Then process = false
       If Instr(Cells(rin,1).Value, “File”) > 0 Then process = false

If after those lines process is still True we can write out the values
Sub filefind()
rin = 1
rout = 1
Do Until Left(Cells(rin,1).Value,5) = “Total”
   If Left(Cells(rin,1).Value,9) = “Directory” Then
      direct = Mid(Cells(rin,1).Value,14) & Cells(rin,2).Value
   Else
      Process = True
      If IsEmpty(Cells(rin,1)) Then process = false
      If Instr(Cells(rin,1).Value, “Vol”) > 0 Then process = false
      If Instr(Cells(rin,1).Value, “DIR”) > 0 Then process = false
      If Instr(Cells(rin,1).Value, “File”) > 0 Then process = false
      If process = True then
          Cells(rout,4).value = direct & “\” & Cells(rin,2).Value
          Rout =rout + 1
      End if
   End if
   rin = rin + 1
Loop
End Sub
APPENDIX

Code Examples
Arithmetic
Addition             a+b
Subtraction          a–b
Multiplication       a*b
Division             a/b
To the power of      a^b

Some Arithmetic functions.

Function     Action                                     Example
INT(A)       Returns nearest whole number less than A   INT(5.34) = 5
                                                        INT(-5.34) = -6
A MOD B      Returns the integer remainder when A is    7 MOD 2 = 1
             divided by B                               50 MOD 5 = 0
ABS(A)       Returns the absolute value of A            ABS(5) = 5
                                                        ABS(-5) = 5
RND()        Returns a random number between 0 and 1.   INT(N * RND() + 1)
             Combined with the INT function you can
             generate random integers between 1 and N
Actions based on conditions
If...Then...Else
Used when you want the program to do different            Syntax
things depending on certain conditions.                   If Condition1 Then
Example                                                       Series of instructions
                                                          [Else
if a = 5 then                                                 Series of instructions]
  form.print “a is 5”                                     End If
else
  form.print “a is not 5”
end if

The If Statement can have 3 basic forms
1 Single Line
    If c < 0 Then c = 0
2 Actions only if condition is true
    If c > 0 then
        c=c-1
        x = True
    End If
3 Full version as example above

Select Case
Runs one of several series of instructions according to the value of Expression. Expression is
compared with each CaseExpression in turn. When a match is found, the instructions
following that Case CaseExpression are run, and then contro l passes to the instruction
following End Select. If there is no match, the instructions following Case Else are run.

Example                                               Select Case Expression
Select Case left(text,1)                                 Case CaseExpression
   Case “0” To “9”                                           Series of instructions
      form.print “Digit”                                 [Case Else
   Case “a”,”e”,”i”,”o”,”u”                                  Series of instructions]
      form.print “Vowel”                              End Select
   Case Else
      form.print “Consonant”
End Select
Repeated actions
For...Next
Repeats the series of instructions between For and Next while increasing CounterVariable by
1 (default) or the value of Increment until CounterVariable is greater than End. If Start is
greater than End, then Increment must be a negative value in which case CounterVariable
decreases by Increment until it is less than End.

For i = 1 to 10 step 2                For CounterVariable = Start To End [Step Increment]
  form.print i                           Series of instructions
Next                                  Next [CounterVariable]



Do … Loop
                                                   Do {While | Until} condition
x= 1                                                  statements
                                                      [Exit Do]
Do Until x = 9
                                                      statements
   form.print x
   x = x + 2                                       Loop
Loop
                                                   Or
The condition can be either with the Do
command in which case it is checked before         Do
entering the loop or with the Loop command in         statements
which case the loop is executed at least once         [Exit Do]
before reaching the condition.                        statements
                                                   Loop {While | Until} condition
The optional Exit Do statement enables you to break out of the loop even if the condition has
not been reached.

A similar older version is below

While...Wend
Repeats a series of instructions between While and Wend while the specified condition is
true.
                                                While Condition
x= 1
                                                  Series of instructions
While x < 11
                                                Wend
   form.print x
   x = x + 2
Wend
                                Strings and Numbers
Asc()     Asc(string)
          Returns the character code of the first character in string.
Str ()    Str (n)
          Returns the string representation of the value n. If n is a positive number, Str(n) returns a
          string with a leading space. To remove the leading space, use LTrim().
Chr ()    Chr (CharCode)
          Returns the character whose ANSI character code is CharCode. Character codes in the
          range 0 (zero) to 31, inclusive, match the nonprinting characters of the standard ASCII
          code. For example, Chr(13) is a carriage return character and Chr(9) is a tab character.
          The following table lists a few of the special characters you can produce using Chr().
          Value           Character returned
          Chr(9)          Tab character
          Chr(11)         Newline character (SHIFT+ENTER)
          Chr(13)         Carriage return
          Chr(32)         Space character
          Chr(34)         Quotation mark

Val()     Val(string)
          Returns the numeric value of string. A common use of Val() is to convert strings
          containing digit characters to numbers so they may be used in mathematical formulas. If
          string does not begin with a digit character, Val() returns 0 (zero).
Len()     Len(string)
          Returns the number of characters in string.
InStr()   InStr([Index,] Source, Search)
          Returns the character position in Source at which Search begins, where 1 corresponds to
          the first character, 2 to the second character, and so on. If Source does not contain
          Search, InStr() returns 0 (zero).
          Argument         Explanation
          Index            The character position in Source at which to begin the search. Optional
                           the default is 1
          Source           The text to be searched.
          Search           The text to search for.
Left()    Left(Source, Count)
          Returns the leftmost Count characters of Source.
Mid()     Mid(Source, Start [, Count])
          Returns a portion of Source starting at a given character position.
          Argument Explanation
          Source        The original string.
          Start         The character position in Source where the string you want to return begins.
          Count         The number of characters in the string you want to return. If you do not
                        specify Count, the number of characters to the end of the string is assumed.
Right()   Right(Source, Count)
          Returns the rightmost Count characters of Source.
Lcase()   Lcase(Source)
          Returns a string in which all letters of Source have been converted to lowercase.
Ucase()       ditto uppercase
                                     Communication
InputBox ()   InputBox(Prompt [, Title] [, Default])
              Displays a dialog box requesting a single piece of information and returns the text
              entered in the dialog box when the user chooses the OK button. If the user chooses the
              Cancel button, an error occurs. You can use the On Error statement to trap the error.
              Argument Explanation
              Prompt        Text displayed in the dialog box indicating the kind of information
                            requested.
              Title         Text displayed in the title bar of the dialog box (if omitted, Word uses
                            the title "Microsoft Word").
              Default       Text that initially appears in the text box of the dialog box. This value is
                            returned if the user types nothing before choosing OK.


MsgBox,            MsgBox Message[, Type] [, Title]
MsgBox()           MsgBox(Message [, Type] [, Title])
                   The MsgBox statement displays a message in a message box. You can also
                   display a message with the MsgBox() function, which returns a value according
                   to the command button the user chooses in the message box. Use MsgBox() if
                   you need your application to take action based on the user's response.
                   Argument Explanation
                   Message       The message to be displayed in the message box. If Message is
                                 longer than 255 characters, an error occurs.
                   Type          A value representing the symbol and buttons displayed in the box
                   Title         The title of the message box. If omitted, "Microsoft Word" is the
                                 default title.
                   .
                   Type is the sum of three values, one from each of the following groups.
                   Group Value           Meaning
                   Button 0 (zero) OK button (default)
                             1           OK and Cancel buttons
                             2           Abort, Retry, and Ignore buttons
                             3           Yes, No, and Cancel buttons
                             4           Yes and No buttons
                             5           Retry and Cancel buttons
                   Symbol 0 (zero) No symbol (default)
                             16          Stop symbol
                             32          Question symbol
                             48          Attention symbol
                             64          Information symbol
                   Button action         0 (zero) First button is the default
                             256         Second button is the default
                             512         Third button is the default
Because the MsgBox statement does not return a value, the use of button values
other than 0 (zero) is not recommended. To make use of buttons other than the
OK button, use the MsgBox() function. MsgBox() returns the following values.
Return value Button chosen              Button text
-1              First (leftmost) button OK
                                        Yes
                                        Abort
0 (zero)        Second button           Cancel
                                        No
                                        Retry
1               Third button            Cancel
                                        Ignore
Appendix 2 Creating a list of files

Click on Start – Run

Type in cmd and press Enter, A
command window will appear.




Depending on the set up of the machine this may be pointing to the M: drive, so select the
drive you require by typing a command such as C: and press enter.

Change to required folder by typing a command such as cd docs

The type the command dir /s/n > c:\temp\filelist.txt (This creates a list of files in the given
folder and all sub folders and writes the output to a file called filelist.txt in the temp folder.
Obviously you could change the name and location to wherever you like.)

Start up Excel in the normal way and
open this file. You will have to tell
Excel that you are looking for a text
file.



A window like the one to the Right
should appear

After selecting Fixed Width press
Next




Double click on all break lines to
remove them, then scroll down the list
to find a file name
Click just to the left of the file name to
add a column break



Click Finish

When the data has loaded, select the
first two columns and change the font
to Courier new and widen the columns
to see the data

Save as an excel spreadsheet.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:68
posted:11/14/2010
language:English
pages:29
Description: Vba Attach Excel Worksheet document sample