form freezing up

[Pegasus0990]

I last touched this code a few months ago, and for some reason it no longer works. When I push a 'commandbutton' in Word it is suppose to extract data from Excel and insert it into Word. However now when i press the 'commandbutton' it appears it just freezes and no data is pressed. both files are in the correct place on my computer to call up.

Public Sub CommandButton1_Click()
HideListBoxes
 
Dim oDoc As Word.Document
 
Set oDoc = Application.Documents("C:\wordfile.doc")
Dim oExcel As Excel.Application, myWB As Excel.Workbook
    
Set oExcel = New Excel.Application
Set myWB = oExcel.Workbooks.Open("C:\excelfile.xls")
 
Dim b As Integer
 
b = CInt(myWB.Sheets("Sheet1").Range("B20").Value)
 
For i = 20 To b
    oDoc.Bookmarks("bookmark1").Range.Text = myWB.Sheets("Sheet1").Cells(i, 2)
    
Next i
 
Set myWB = Nothing
End Sub

Private Sub HideListBoxes()
 
CommandButton1.Visible = False
End Sub

[Pegasus0990]

a little more detail.

when i use to push the 'command button' all the data i was trying to get from excel was passed to word. however now, when the 'command button' is pushed, it just accepts getting pushed, then freezes.

[Pegasus0990]

i have the following referenced.

VB for apps
word 11.0 object library
excel 11.0 object library
office 11.0 object library
forms 2.0 object library
ole automation
normal

when i last worked on this code, i was using a different computer, so wondering if this could be the problem.

[Pegasus0990]

removing all reference to "hidelistboxes" doesnt work. when the commandbutton is pushed. the form still causes the program to freeze.

i swear, that 2 months ago, this same exact code worked.

[Pegasus0990]

the value of "B20" in this instance is the integer 18


could i maybe just not have something reference?

[Siddharth Rout]

b = CInt(myWB.Sheets("Sheet1").Range("B20").Value)
 
For i = 20 To b

if Range("B20").Value =18 then
your For loop will be 20 to 18?

is that what you want?

change the value of Range("B20").Value = 25 and then check

[Pegasus0990]

not exactly, the value of "B20" will be determined from VB located inside of Excel. The Excel VB is working fine.

when i do a watch inside the Word VB (the code I provided), the value of "B20" is indeed 18, like i intended. the issue is that accept i press my command button, the code never completes itself, hence freezes.

[Pegasus0990]

//here is the code for my Excel file//and attached is the code for my Word File//I deleted all non relevant data//the Word file contains a bookmark//enable 'view bookmark' to see it//when the Word Macro is ran, it is suppose to grab the infomation which in this case is "Test1" and "Test2" and insert it into the "bookmark1" location//in the file "wordfromexcel" is what my Excel doc looks like, "Test1" is in both the location "A18" and "D12," and "Test2" is in both "A19" and "D9." Basically, when a value of the C column="no" then the data is sent to the A column, where Word will extract it via bookmarks.

//excel VB

Sub proc1()
 
 Dim j As Integer
 Dim k As Integer
 k = 20
 For j = 9 To 14
 
    If Worksheets("Questions").Cells(j, 3) = "No" Then
        k = k - 1
        Worksheets("Questions").Cells(k, 1) = Worksheets("Questions").Cells(j, 4)
    End If
 Next j
 
Range("B20").Value = k
 
End Sub