Using a Macro in MS Excel to produce a report

peteyb · 06-29-2007, 04:49 AM

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** · 06-29-2007, 06:27 AM

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

peteyb · 06-29-2007, 06:37 AM

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** · 07-01-2007, 02:50 PM

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 · 07-02-2007, 02:19 AM

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** · 07-02-2007, 06:12 AM

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 · 07-02-2007, 06:44 AM

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** · 07-02-2007, 01:40 PM

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 · 07-03-2007, 01:59 AM

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 · 07-05-2007, 09:54 AM

Hi Glaswegian

Have you been able to look at the code?

Forgive me if I come across rude or impatient.

Cheers

**Glaswegian** · 07-05-2007, 12:59 PM

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** · 07-05-2007, 01:29 PM

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.

Code:

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 · 07-06-2007, 02:01 AM

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 · 07-06-2007, 02:09 AM

Im getting a user-compile error, on line

Dim wdApp As Word.Application

**Glaswegian** · 07-06-2007, 06:17 AM

Quote:

Originally Posted by peteyb

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.

Quote:

Originally Posted by peteyb

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 · 07-06-2007, 06:23 AM

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 · 07-06-2007, 06:25 AM

oh and yes, it is a new template.

**Glaswegian** · 07-06-2007, 02:59 PM

Quote:

Originally Posted by peteyb

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.

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.

**Glaswegian** · 07-08-2007, 03:37 PM

Hi

Having now realised where I'd made the code blooper

, see if this works for you.

Code:

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 · 07-09-2007, 02:17 AM

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?

06-29-2007, 04:49 AM	#1 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Using a Macro in MS Excel to produce a report 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

06-29-2007, 06:27 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,497 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Using a Macro in MS Excel to produce a report 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... __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

07-02-2007, 02:19 AM	#5 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report 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?

07-02-2007, 06:12 AM	#6 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,497 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Using a Macro in MS Excel to produce a report 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

07-02-2007, 06:44 AM	#7 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report many thanks, I have now got it working. Is it possibe to have the report open in MS Word instead of an excel sheet?

07-02-2007, 01:40 PM	#8 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,497 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Using a Macro in MS Excel to produce a report 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

07-03-2007, 01:59 AM	#9 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report 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.

07-05-2007, 09:54 AM	#10 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report Hi Glaswegian Have you been able to look at the code? Forgive me if I come across rude or impatient. Cheers

07-05-2007, 12:59 PM	#11 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,497 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Using a Macro in MS Excel to produce a report 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

07-05-2007, 01:29 PM	#12 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,497 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Using a Macro in MS Excel to produce a report 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. Code: 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

07-06-2007, 02:01 AM	#13 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report 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?

07-06-2007, 02:09 AM	#14 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report Im getting a user-compile error, on line Dim wdApp As Word.Application

07-06-2007, 06:23 AM	#16 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report 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

07-06-2007, 06:25 AM	#17 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report oh and yes, it is a new template.

07-09-2007, 02:17 AM	#20 (permalink)
peteyb Registered User Join Date: Apr 2007 Posts: 60 OS: xp pro	Re: Using a Macro in MS Excel to produce a report 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?