# Using a Macro in MS Excel to produce a report



## peteyb (Apr 20, 2007)

Hi all

I have created an excel spreadsheet that has a series of questions, each having a drop down list in the answer column. Yes, No and N/A are the only answers available.

Is it possible to set up a button that prints a report of all the questions that have been answered No?

If this is possible, how?

Thanks


----------



## Glaswegian (Sep 16, 2005)

Hi

Yes, entirely possible.

You would create some code that would check the values in the relevant column, and if all are "No" then print a report. What version of Excel are you using? Is the 'Report' already pre-formatted and ready for printing? What are the relevant ranges? What is the relevant sheet name? What case are the answers - upper case sentence case, lower case? How did you create the drop downs - forms toolbar or Control Toolbox?

Lot's of info required...:grin:


----------



## peteyb (Apr 20, 2007)

Hi

I will clarify what I need then answer your questions.

1. The code should not pick out if ALL are No. The code should see which ones are NO, see the adjacent question then create a statements in the report based around those questions.

2. I am using Excel 2003

3. I have not started the report.

4. I have uploaded a copy of the workbook which should help you understand the ranges, case and sheet names.

5. The dropdown boxes were created using the *Data > Validation > List *option


Once created the report must be enabled for each following column.


----------



## Glaswegian (Sep 16, 2005)

Hi

Sorry I didn't get a chance to reply sooner.

I've amended your workbook and added a button that will generate a report on a separate sheet. The code looks to see if the answer is no. It then copies that value to column B and copies the corresponding question to column A.

Hope this helps get you started.


----------



## peteyb (Apr 20, 2007)

many thanks for your help. im probably being a dope but the button does not do anything when I press it and when i double click it I am sent to visual basic.

A further little guidance if you could?


----------



## Glaswegian (Sep 16, 2005)

Sounds like you may still be in Design Mode. Look on the Control Toolbox toolbar for a blue triangle - you'll need to click it to turn off Design Mode.


----------



## peteyb (Apr 20, 2007)

many thanks, I have now got it working. 

Is it possibe to have the report open in MS Word instead of an excel sheet?


----------



## Glaswegian (Sep 16, 2005)

It's possible, but will take me a bit of time and is slightly more complex. I'll get back to you with something whenever I possibly can.


----------



## peteyb (Apr 20, 2007)

Okey dokes, it would be greatly appreciated.

Ive amended the existing code so that each column has its own create report button. Im fairly advanced at HTML and PHP and a little bit of MySQL and JS so can kinda read between the lines of code of Visual Basic and amend as nec.


----------



## peteyb (Apr 20, 2007)

Hi Glaswegian

Have you been able to look at the code? 

Forgive me if I come across rude or impatient.

Cheers


----------



## Glaswegian (Sep 16, 2005)

Hi

Apologies - haven't forgotten - real life has rather intruded a bit. I've just got a code glitch to iron out. Should have that sorted in a while.


----------



## Glaswegian (Sep 16, 2005)

Hi again

Had an Excel glitch but got around it. I appreciate you've already made some code changes, but this is based on what we had. There are some things to note - there is no error checking, you'll have to manually save the Word doc, there are no headers etc included in the Word doc. If you need any of these things let me know.

```
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim c As Range
Dim repRng As Range
Dim wdApp As Word.Application


Set myRng = Range("C6:C35") 'the range on the question sheet that contains Yes/No etc

For Each c In myRng
    If c.Value = "No" Then 'if the answer is No
        With Sheets("Report")
            .Range("B65536").End(xlUp).Offset(1, 0).Value = c.Value 'write the answer to column B
            .Range("A65536").End(xlUp).Offset(1, 0).Value = c.Offset(0, -1).Value 'write the question to column A
        End With
    End If
Next c

Sheets("Report").Columns("A:B").AutoFit 'adjust the cell widths

Worksheets("Report").Range("A2").Select
ActiveCell.CurrentRegion.Copy 'copy the report

Set wdApp = New Word.Application
'create a new instance of Word and add a blank document
With wdApp
    .Documents.Add
    .Visible = True
End With

'paste the data
wdApp.Selection.Paste

Application.CutCopyMode = False

Set wdApp = Nothing

End Sub
```
Nothing too fancy - still writes the data to the Report sheet, but then opens Word and drops the data into a new Word doc. Just replace your existing code.


----------



## peteyb (Apr 20, 2007)

Hi

Many thanks, will have a blast at it now. 

I know im gonna be pushing my luck, but is there anyway you can reference the report to a word template, instead of a new document?


----------



## peteyb (Apr 20, 2007)

Im getting a user-compile error, on line

Dim wdApp As Word.Application


----------



## Glaswegian (Sep 16, 2005)

peteyb said:


> Hi
> 
> Many thanks, will have a blast at it now.
> 
> I know im gonna be pushing my luck, but is there anyway you can reference the report to a word template, instead of a new document?


 I think so...is it a new template or an existing one? I think I can do new, but I'm not so sure on existing. I'm not that good on Word VBA.



peteyb said:


> Im getting a user-compile error, on line
> 
> Dim wdApp As Word.Application


Ah - you need to have a reference in Excel to Word's Object Model. Open Excel and press Alt+F11 to open the VB Editor. On the menu bar click on Tools > References and in the dialog box look for *Microsoft Word 10.0 Object Library.* Check the box and click OK. That should be OK now.


----------



## peteyb (Apr 20, 2007)

Ive done that, it's number 11.0 on mine.

I am now getting a runtime error '1004' and the debug shows:

Worksheets("Report").Range("A2").Select


----------



## peteyb (Apr 20, 2007)

oh and yes, it is a new template.


----------



## Glaswegian (Sep 16, 2005)

peteyb said:


> Ive done that, it's number 11.0 on mine.
> 
> I am now getting a runtime error '1004' and the debug shows:
> 
> Worksheets("Report").Range("A2").Select


This is where I had the earlier problem, but in a slightly different way. It's something to do with the way I'm referencing that sheet, but for the life of me I can't work out what it might be.:4-dontkno

Leave this with me and I'll work on it over the weekend. I'll also try the template part, but I think I know how to do that. :grin:


----------



## Glaswegian (Sep 16, 2005)

Hi

Having now realised where I'd made the code blooper :grin:, see if this works for you.

```
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim c As Range
Dim repRng As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document


Set myRng = Range("C6:C35") 'the range on the question sheet that contains Yes/No etc

For Each c In myRng
    If c.Value = "No" Then 'if the answer is No
        With Sheets("Report")
            .Range("B65536").End(xlUp).Offset(1, 0).Value = c.Value 'write the answer to column B
            .Range("A65536").End(xlUp).Offset(1, 0).Value = c.Offset(0, -1).Value 'write the question to column A
        End With
    End If
Next c

Sheets("Report").Columns("A:B").AutoFit 'adjust the cell widths

With Worksheets("Report")
    .Range("A1", .Range("B65536").End(xlUp)).Copy
End With

Set wdApp = New Word.Application
'create a new instance of Word
wdApp.Visible = True

Set wdDoc = wdApp.Documents.Add(newtemplate:=True) 'add a new template
wdDoc.Activate

'paste the data
wdDoc.Application.Selection.Paste

Application.CutCopyMode = False

Set wdApp = Nothing

End Sub
```


----------



## peteyb (Apr 20, 2007)

Hi

Many thanks for the work on the code. This is working as expected.

I didnt quite fully explain myself when I said 'New Template'. I meant New, as in I havent created it yet, not new as in create a new template everytime someone runs a report.

Do you know how to reference an existing template and insert the NO answers into the document? If so, can the template be referenced to a file server, or does it have to be local on the machine?


----------



## Glaswegian (Sep 16, 2005)

Hi

We're getting close to the end of my knowledge of Word VBA. :grin:

I need the template path and name - on a local machine would certainly be easier, but if you want to give me the server path and location as well I can have a try at that (no guarantees though...).


----------



## peteyb (Apr 20, 2007)

Hi

the server path and file name is: 

J:\FSA\MONITORING\File Checking Report Template.dot

I have attached the word document, but am unable to attach it as a template. 

What would be really good is if it grabs the info from cell 1B and inserts it before the line of writing. i.e. where the employee's name will go.

Then, following this it would insert the NO statements below the line of text.

I must say, VBScript is a very intriguing code language. I think I will be looking to learn the various aspects over the coming months/years. I really like to debugging aspect.


----------



## Glaswegian (Sep 16, 2005)

Hi again

Well, this seems to work quite well. I've re-attached your Word file, this time saved as a template (*.dot*). I've also created a bookmark, just under your last line of text. This is where the table data will be pasted. I've tested using the path to my own documents and it works fine. You'll need to change the path to your server file location. One thing to remember though, it won't be called 'J' - you'll need to use the correct URL format, such as

*\\myserver1\Fsa\....*

Note that your file template name is also slightly different.

Here's the Excel code - I've made sure there are comments for the important bits.

```
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim c As Range
Dim repRng As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document


Set myRng = Worksheets("Temp").Range("C6:C35") 'the range on the question sheet that contains Yes/No etc

For Each c In myRng
    If c.Value = "No" Then 'if the answer is No
        With Sheets("Report")
            .Range("B65536").End(xlUp).Offset(1, 0).Value = c.Value 'write the answer to column B
            .Range("A65536").End(xlUp).Offset(1, 0).Value = c.Offset(0, -1).Value 'write the question to column A
        End With
    End If
Next c

Sheets("Report").Columns("A:B").AutoFit 'adjust the cell widths

With Worksheets("Report")
    .Range("A1", .Range("B65536").End(xlUp)).Copy
End With


Set wdApp = New Word.Application
'create a new instance of Word
wdApp.Visible = True
'make Word visible

'open the existing Template
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Iain\My Documents\File Checking Report Template-2.dot")  'add a new template
wdDoc.Activate

'paste the data
wdDoc.Bookmarks("report").Range.Paste

Application.CutCopyMode = False 'clear the copy and paste

Set wdApp = Nothing 'release the variable

End Sub
```
Hope this works for you.


----------



## peteyb (Apr 20, 2007)

Thats perfect!!! 

How would I go about copying cell B1 into the word document. I understand how to bookmark and can grasp the code to paste but am unsure of how to copy a singular cell, instead of a range.


----------



## peteyb (Apr 20, 2007)

Hi

Ive managed to manipulate the code to copy the name and also remove the data from the report worksheet after for the next time it is used. It works but is it good code?


```
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim c As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set myRng = Worksheets("Temp").Range("C6:C35") 'the range on the question sheet that contains Yes/No etc

For Each c In myRng
    If c.Value = "No" Then 'if the answer is No
        With Sheets("Report")
            '.Range("B65536").End(xlUp).Offset(1, 0).Value = c.Value 'write the answer to column B
            .Range("A65536").End(xlUp).Offset(1, 0).Value = c.Offset(0, -2).Value 'write the question to column A
        End With
    End If
Next c

Sheets("Report").Columns("A").AutoFit 'adjust the cell widths

With Worksheets("Report")
    .Range("A2", .Range("A65536").End(xlUp)).Copy
End With

Set wdApp = New Word.Application
'create a new instance of Word
wdApp.Visible = True
'make Word visible

'open the existing Template
Set wdDoc = wdApp.Documents.Open("J:\FSA\MONITORING\File Checking Report.doc")  'add a new template
wdDoc.Activate

'paste the data
wdDoc.Bookmarks("report").Range.Paste

Application.CutCopyMode = False 'clear the copy and paste


' this will paste the name cell into the word document
Dim myName As Range

Set myName = Worksheets("Temp").Range("B1") 'the range on the question sheet that contains Yes/No etc

With Worksheets("Temp")
    .Range("B1", .Range("B1").End(xlUp)).Copy
End With

wdDoc.Bookmarks("name").Range.Paste

Application.CutCopyMode = False 'clear the copy and paste

Set wdApp = Nothing 'release the variable

Worksheets("Report").Range("A2:A65536").Clear

End Sub
```

As you will notice at the bottom there are two variations of wrong answers, i.e. FSA Compliant and TIG Compliant

There are 5 cells in the range that are linked to the FSA compliant formula.

Is it possible to reference 

Set myRng = Worksheets("Temp").Range("C6:C35")

as

Set myRng = Worksheets("Temp").Range("C9,C15,C21")

I am gonna play but any ideas would be appreciated.


----------



## Glaswegian (Sep 16, 2005)

Hi

Nice job - there are a couple of things I would change...

To reference a single cell, all you need is the cell address, so change this

With Worksheets("Temp")
.Range("B1", .Range("B1").End(xlUp)).Copy
End With

to this

Worksheets("Temp").Range("B1").Copy

When clearing a range, your code will clear every cell from the bottom. There's no need to do that, just look for the last cell with data, like this

Worksheets("Report").Range("A2", Range("A65536").End(xlUp)).ClearContents

Yes, you can reference individual cells in the way you have described.

Looks like you've got the hang of VBA already!


----------



## peteyb (Apr 20, 2007)

im looking to start an open uni degree in computing and design of which some of the modules I can choose are either Java or VBA orientated.

Looking at jobs in the market place I would say that Java holds more weighting (i.e. bigger salaries!!!!)


----------



## peteyb (Apr 20, 2007)

Im getting a runtime error 1004 on the ClearContents code?


----------



## Glaswegian (Sep 16, 2005)

peteyb said:


> Im getting a runtime error 1004 on the ClearContents code?


lol - if I could just sort out my dyslexia with sheet references...

Try this

With Worksheets("Report")
.Range("A2", .Range("A65536").End(xlUp)).ClearContents
End With



> Looking at jobs in the market place I would say that Java holds more weighting (i.e. bigger salaries!!!!)


 Probably true, but don't forget there are around 400 million MS Office users worldwide - and not all know VBA...:grin:


----------



## peteyb (Apr 20, 2007)

Morning

tried and tested and the code, cheers.



> Probably true, but don't forget there are around 400 million MS Office users worldwide - and not all know VBA...


True.

Forgive me for being forward, may i ask, are you in the industry? and if so why do you choose to moderate these forums?


----------



## Glaswegian (Sep 16, 2005)

peteyb said:


> Forgive me for being forward, may i ask, are you in the industry? and if so why do you choose to moderate these forums?


No I'm not. I needed to automate some tasks I was doing in Excel some years back and found that VBA was the way to do it. I got pretty good at it too, but my job role changed and I don't do so much now. I still try to keep my hand in though.


----------



## peteyb (Apr 20, 2007)

thats cool. I really appreciate your help.

Taking the report a step further; as you will have realised there were enough columns for 15 files checks.

The next plan of action is to create a second report that analyses any trends in the checks. i.e. if there were more than x number of NO's in the same question field across all the checks than copy that question into a word report.

Does this seem feasable?


----------



## Glaswegian (Sep 16, 2005)

Hope you can afford my fees. :grin:

I get the idea. I would expand the Report sheet to include the answers from all 15 columns. You could then simply count the number of 'No' answers and filter accordingly. How does that sound? Do you have a number of 'No' answers in mind?


----------



## peteyb (Apr 20, 2007)

mwhahahahahahhahahahaha

fees!!! check it out!!??!!

I do believe i've managed to do it myself. 

If you've got 5 mins let me know what you think of the code. Obviously you will have to change where the documents are referenced from, but Im sure you knew that!!


----------



## Glaswegian (Sep 16, 2005)

Good work!

The only thing I would suggest changing is where you've declared a variable and set it to a range. e.g.


```
Dim myName As Range

Set myName = Worksheets("Temp").Range("B1") 

Worksheets("Temp").Range("B1").Copy
```
but you don't actually use the variable in the code. Excel will still create the variable and assign a range to it, because that's what you've instructed it to do. But if you're not going to use it, it's just taking up valuable memory. If you're not going to use the variable, just work directly with the range. Therefore the only line you need is

Worksheets("Temp").Range("B1").Copy


On a personal note, I always declare all my variables at the start of the procedure - throwing them in at odd times can make the code difficult to read, and therefore difficult to debug later.


Other than that, welcome to the world of VBA coders!


----------



## peteyb (Apr 20, 2007)

Thanks, I will apply those changes and remember referenceing all ranges at the beginning - do you mean the beginning of the sheets code or the beginning of the sub command.

I set up the project on Excel 2003 and set the Preference to Word 11.0 Library but now when I go to use it on Excel 2000 it says that Word 11.0 is missing. Furthermore, I cannot find Word 10.0 Library.

Any idea?

sorry, you gotta mean at the beginning of the sub command.


----------



## Glaswegian (Sep 16, 2005)

Not sure on that one. XP is Office version 10.0 and O2003 is version 11.0. However, I would have thought that if you had library 11.0 listed then library 10.0 would not matter.

I'll need to do some investigation on that.

Yes, I do mean at the start of the sub.

Hi again

Make sure the following are also checked in the References Dialogue box:

Microsoft Office XX Object Library
Microsoft Visual Basic for Applications Extensibility
Microsoft Scripting Runtime
OLE Automation


Let me know if that helps.


----------



## peteyb (Apr 20, 2007)

The MS Scripting Runtime was not ticked but did not fix it. I removed the 11.0 Library, logged out then back in to VB. On my return the 9.0 Word Library was available and after checking this box the form worked.

It also works on excel 2003 with the 9.0 checked.


----------



## Glaswegian (Sep 16, 2005)

Doh! Of course. Excel 2000 = version 9.0.

As long as it now works, that's the main thing.


----------



## peteyb (Apr 20, 2007)

I think thats all for now. 

Many thanks for all your help.


----------

