Home » Forums » Open Source Learning Center » Question and Answer

VBA code driving me mad!!!

Submitted by milkys on 26 November, 2011 - 16:04
Hi there,

I've produced some code which goes down a drop-down list and creates a word report for every entry but i'm looking to modify it so that it incorporates a combo-box rather than a drop-down list but i'm struggling to make my code work properly so any assistance you can provide would be extremely appreciated.

The code is 

Sub Report()
 
Dim Cell As Range
For Each Cell In Range("Regions")
Range("A4").Value = Cell.Value
 
    Dim wdApp       As Object
    Dim wd          As Object
    Dim sFil        As String
 
    On Error Resume Next
    Set wdApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set wd = wdApp.Documents.Add
 
    wdApp.Visible = False
    Range("Milsys").CopyPicture xlScreen, xlPicture
    wd.Range.PasteSpecial Link
    wdApp.ActiveDocument.SaveAs Filename:="C:\Documents and Settings\Milan\Desktop\Reports\" &  _
       Workbooks("M").Sheets("Representation").Range("a4").Value & ".doc"
    wdApp.Quit
 
 
Next Cell
 
End Sub
‹ Help need on VB VBA Code to reference a cell from a closed workbook to a cell in another workbook ›
»
Vishesh's picture

The code is actually working

Submitted by Vishesh on 27 November, 2011 - 15:04.

The code is actually working fine; its just that the screen is not getting refreshed. So, the following is a workaround.

Before running the following code set linkedcell property of Milkys combobox to A2. 

Sub Report()
 
Dim x As Integer
ThisWorkbook.Worksheets("Representation"). _
   OLEObjects("Milkys").Visible = False
For x = 0 To Sheets("Representation").Milkys.ListCount - 1
 
Dim DTAddress As String
Dim wdApp As Object
Dim wd As Object
Dim sFil As String
 
DTAddress = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")  _
    & Application.PathSeparator
 
On Error Resume Next
Set wdApp = GetObject("Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
 
Set wd = wdApp.Documents.Add
 
wdApp.Visible = False
 
ThisWorkbook.Worksheets("Representation"). _
    OLEObjects("Milkys").Object.ListIndex = x
 
Range("Milsys").CopyPicture xlScreen, xlPicture
wd.Range.PasteSpecial Link
 
wdApp.ActiveDocument.SaveAs Filename:=DTAddress &  _
ThisWorkbook.Worksheets("Representation"). _
    OLEObjects("Milkys").Object.List(x) & ".doc"
wdApp.Quit
 
Set wdApp = Nothing
Set wd = Nothing
Next x
ThisWorkbook.Worksheets("Representation").OLEObjects("Milkys").Visible = True
End Sub
»

Thanks Vishesh and sorry to

Submitted by milkys on 27 November, 2011 - 15:53.

Thanks Vishesh and sorry to be a pain but rather than having a linked cell as it would be nice to show the dropdown, is it not possible for the code to go down the combobox list, update the charts accordingly and create a word document for that specific area?

Milan

»
Vishesh's picture

The code is definitely going

Submitted by Vishesh on 27 November, 2011 - 16:02.

The code is definitely going down the combobox list but for some reason its not showing or refreshing the screen. You can run the code step by step by pressing F8 and see that the combobox is getting updated correctly and also its reflecting the same in word files as well. I might be missing a trick here.

»