By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

Excel: Looping Through ComboBox List

P: 5
Hi there, I've created a activeX combobox list which is mapped from an area which is called 'Regions' and is located in cells AA14-AA29. The problem is how do I incorporate this into my existing code which is displayed below so that when I run the report macro, it creates a word document for each unique lable within the combobox list and labelled correctly. At the moment the code creates a word documents 8 times as there are 8 unique entries within my combobox list. However, if the first area is Bath, it will place this area into the remaining 7 documents. In an ideal situation I would like the code to create a word document for each unique entry so it's actually going through the combobox list. I'm hoping the code only requires slight tweaking because at the moment it's doing everything I want successfully except this.
As always any help you can provide is much appreciated,

Milan

Code is;

Expand|Select|Wrap|Line Numbers
  1. Sub Report()
  2.  
  3. Dim x As Integer
  4. For x = 0 To Sheets("Representation").combobox1.ListCount - 1
  5.  
  6.     Dim DTAddress   As String
  7.     Dim wdApp       As Object
  8.     Dim wd          As Object
  9.     Dim sFil        As String
  10.  
  11.     DTAddress = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & Application.PathSeparator
  12.  
  13.     On Error Resume Next
  14.     Set wdApp = GetObject("Word.Application")
  15.     If Err.Number <> 0 Then
  16.     Set wdApp = CreateObject("Word.Application")
  17.     End If
  18.     On Error GoTo 0
  19.  
  20.     Set wd = wdApp.Documents.Add
  21.  
  22.     wdApp.Visible = False
  23.     Range("Milsys").CopyPicture xlScreen, xlPicture
  24.     wd.Range.PasteSpecial Link
  25.     wdApp.ActiveDocument.SaveAs Filename:=DTAddress & Workbooks("M2").Sheets("Representation").combobox1.List(x) & ".doc"
  26.     wdApp.Quit
  27.  
  28.  
  29. Next x
  30.  
  31. End Sub
Nov 27 '11 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,709
While I'm looking at this you can check out When Posting (VBA or SQL) Code to avoid wasting time with any of your future posts.
Nov 27 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
I have a slightly updated (tidier) version of your code. Your question mentions Range("Regions"), but your code refers to Range("Milsys") instead. The reason it always creates the same contents is because your CopyPicture() code always references the same range.

With the limited information available I changed the code as a suggestion of how it might be fixed to handle your problem :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub Report()
  4.     Dim intX As Integer
  5.     Dim DTAddress As String, sFil As String
  6.     Dim wdApp As Word.Application
  7.  
  8.     DTAddress = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & _
  9.                 Application.PathSeparator
  10.     For intX = 0 To Sheets("Representation").Combobox1.ListCount - 1
  11.         sFil = DTAddress & Me.Combobox1.List(intX) & ".doc"
  12.         On Error Resume Next
  13.         Set wdApp = GetObject("Word.Application")
  14.         If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application")
  15.         On Error GoTo 0
  16.         With Range("Regions").Range("A" & intX - 1)
  17.             Call .CopyPicture(xlScreen, xlPicture)
  18.         End With
  19.         With wdApp
  20.             With .Documents.Add
  21.                 Call .Range.PasteSpecial(Link:=True)
  22.                 Call .SaveAs(Filename:=sFil)
  23.             End With
  24.             Call .Quit
  25.         End With
  26.     Next intX
  27. End Sub
Nov 27 '11 #3

P: 5
Thanks NeoPa, I'm grateful for the assistance. Firstly, the range 'Milsys' refers to my print area and 'Regions' to my list of items for the combobox.
I have tried using your code and pasted it into a module but I'm encountering the following errors;

1) Under 'Dim wdApp As Word.Application', I'm encountering user defined type not identified so I amended the code to Dim wdapp as object.

2) Under 'Me.combobox1... it says there's an invalid use of the me keyword which I dont understand.

Would it be helpful if I sent you an example of my spreadsheet?

Milan
Nov 27 '11 #4

P: 5
Hello again NeoPa, The code seemed to be working until line,

'With Range("Regions").Range("Milsys" & intX - 1)' where i'm getting a 1004 runtime error which states an application-defined or object defined-error.

Please help,

Milan
Nov 27 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
Milan Shah:
Would it be helpful if I sent you an example of my spreadsheet?
Yes. I think that might help - as you seem to have left out a few important details from your question. It's your responsibility to cover these details properly in the question, but I think waiting for that might take more effort than seeing it directly. I only have Excel 2003 so you will need to ensure that what you post is in a format no later than that.

For working with Word it is usually helpful (necessary) to ensure you have a reference set to Word in your project (In the VBA IDE select it from Tools | References). Of course you can use Object variables instead, but only if you like causing extra work and confusion. It was no accident that I got rid of any such variables in the code for you.

PS. Application Automation is a good place to start when trying to deal with such things in code.
Nov 28 '11 #6

P: 5
Thanks Neo, would you be able to provide an email address so that I can provide an example in excel?

Milan
Nov 28 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
No Milan, but if you follow any relevant instructions found in Attach Database (or other work) (Ignore anything specifically for databases) then you can happily attach it here.
Nov 28 '11 #8

P: 5
Hello again Neo and apologies for the delay in responding. I've figured out the code alhtough I'm publish it as PDFs rather than word documents now so all is well. Thanks for your assistance and I'll no doubt be in contact for any other related queries.

Milan
Dec 4 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
Fair enough. As long as you're sorted that's the main thing :-)
Dec 4 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.