473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Excel: Looping Through ComboBox List

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
9 4814
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
Fair enough. As long as you're sorted that's the main thing :-)
Dec 4 '11 #10

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

Similar topics

0
by: Ron Ford | last post by:
I am trying to format the strings in a combobox dropdown list into columns. I can use a fixed font like Courier New and make it work, but I would like to use the SetTabStops method of the...
0
by: Susan Bricker | last post by:
The following error: "The current field must match the join key '?' in the table that seves as t the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the...
2
by: Thomaz | last post by:
Hi i need some help to solve a problem with the DataSource in a COMBOBOX. The question is "How can i clear all the itens in a Combobox if i use the DataSource to browse data in a database????"....
5
by: Keith G | last post by:
I am using Visual Studio 2003. In the standard combobox control it would appear that only 1 column of data can be displayed in the list (as stipulated in the DisplayMember property). In VBA it was...
2
by: gleadams | last post by:
I have databound a Windows form ComboBox control to a DataSource and DataMember and it is properly showing the data as I navigate through the records. My question concerns the choices in the...
2
by: mnms | last post by:
Hi, I'm wondering if it's possible "manually" add an extra value to a combobox list. At the moment I have two fields, one "transparent" is a checkbox that lets you define a colour as...
2
by: DesCF | last post by:
I have a textbox and a combobox on a toolstrip. The user enters either an ID in the textbox or selects a name from the combobox. When the user selects a name from the combobox the textbox is...
0
by: Maninder Karir | last post by:
Hi, Im not very experienced in using VBA in Excel but im having a go. Im stuck with getting data from another worksheet into a Combobox. Basically I have around 10 worksheets each is a...
0
by: SteveArmstrong | last post by:
This code is used in conjunction with an excel spreadsheet, i need to be able to remove each item from the comboBox list as the item is selected this can be in any order and if and when the last item...
0
by: wizofoz777 | last post by:
Ok here is my problem : I was creating comboboxes and I wanted to be able to use the same event for all of them. So I decided to create a combobox collection containing the event in a wrapper. When...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.