VB forms freezing (using a differenct computer then before)

[Pegasus0990]

i have not touched this code in a couple of months, but now i am on a different computer. 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. i can tell that values are being passed to the Word VB by using 'watches.' however, nothing is outputted into the Word file, and the form doesnt dissapear.

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

//the following code is in my Word BF//

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

 

[fumei]

and the form doesnt dissapear.

Why would it? There is no instruction for it to do so.

If you are finished with the instance of Excel (oExcel) you should get rid of it. You do get rid of the workbook object (myWB).

As for nothing going into the Word document...I don't know. If the bookmark exists, then it should. However, you do not really state what does happen. Is the bookmark range text blank?

Gerry
My paintings and sculpture

 

[Pegasus0990]

Gerry, thanx for the reply. i am new to VB and slowly learning the ways.

what is the proper code to make the form and the instance of Excel to end?

 

[fumei]

To make a userform unload, you use Unload. Commonly it is done with the Me object. When run within the userform, Me is the userform. So:

Unload Me

Put at the end of a procedure, the procedure instructions will action, and the userform unloads.

To quit the instance of Excel at the end of the procedure (after presumably you want to close the Excel file):

myWB.Close
Set myWB = Nothing
Set oExcel = Nothing
End Sub

However, this does not solve the issue that you say the data is not getting into Word. What IS happening?

Gerry
My paintings and sculpture

 

[Pegasus0990]

when running the macro and having watches, the value of "B20" is exactly what it should be (from excel). and the value for both of my variables ("i" and "b") are exactly what they are suppose to be.

now i had this exact code working a few months ago. i swapped computers, now it just freezes when i press the commandbutton. it use to not freeze, and would correctly output data to the bookmark.

 

[fumei]

OK, I am going to ask for the third time.

What IS happening? Is anything getting into the document at all? Does the code run with no errors, but there is NO text in the document? Does it run with any errors?

Please also state what version you are using. This is generally a good idea when you post.

Hve you changed versions?

Gerry
My paintings and sculpture

 

[Pegasus0990]

i am using version 6.3 and have not changed versions.

//here is the code for my Excel file//when the Word Macro is ran, it is suppose to grab data from Excel and insert it into the "bookmark1" location//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.

there is no issue with my Excel code, but when i press the command button in Word, it just freezes up. no errors are present, and the values of "i" and "b" are being updated from the information from Excel like they should be.

//my word VB i posted in my first message.

//excel VB

visual basic code:--------------------------------------------------------------------------------
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

 

[fumei]

Sorry, but I am leaving this thread as you will not answer a direct question. I have asked what IS happening - I know what is supposed to happen.

I asked - very clearly I think -

Is anything getting into the document at all? Does the code run with no errors, but there is NO text in the document?

You will not answer if there is anything in the document at all; you do not mention what IS in the document (blank? one entry? not as many entries as there should be?)

I am not a mind reader, and I can not look over your shoulder to see myself what IS happening. All I can do is ask. Since you will not answer, even after three requests, I give up. I am sure someone else will be able to help you.

Two final comments before I leave this one.

1. I hope you realize that

oDoc.Bookmarks("bookmark1").Range.Text = myWB.Sheets("Sheet1").Cells(i, 2)

will insert those values in reverse order

Adding Range.Text will put that text at the start each time. Order of items = "one, "two", "three" , will result in: "threetwoone"

2. Your code:

For i = 20 To b
    oDoc.Bookmarks("bookmark1").Range.Text = _
        myWB.Sheets("Sheet1").Cells(i, 2)
Next i

From your Excel code, it seems that b could very well be < 20.

Try doing a simple test like:

Dim i
For i = 20 To 17
    Selection.TypeText Text:=i
Next i

See what happens. Do you think it will put in 20, 19, 18, 17? Ooooooooooooooooooooooooh. Nothing. No errors, but no text. Unless explicitly instructed to go in reverse order, For i = X to Y increments from X To Y. The assumption is Y is > X.

That is why I was asking if there was anything at all, as text. I wanted to know, to have confirmed, there was nothing at all. It would have then been an easy question to ask - although I am not sure you would actually answer - is it possible that b is < 20? But you could not, would not, answer.

Good luck.

Gerry
My paintings and sculpture

 

[fumei]

Just to reiterate from your OP, your form is NOT "freezing", it is doing precisely what you tell it to do.

Gerry
My paintings and sculpture

 

[Pegasus0990]

i thought i was answering your question, my apoligies for not stating the info that you requested. i do appreciate your attempt though.

i understood it was outputting my data in reverse order. this is intended.

when i go into my Word VB, and click "run sub/userform" then my form pops up. when i press the command button, the form never dissapears. and then no text is getting added to my Word file like it is suppose to. i had this working two months ago, and changed zero code.

so zero changes are made.

what use to happen. when i would press the command button, the userform would dissapear, and my Word file would be updated with text being inserted into the Bookmark location.

i understand that my "b" could be < "20." i was just using the number "20" to test.

 

[Pegasus0990]

solved

 

[fumei]

While it is good to know it is "solved", it is bad form to not tell us what that is.

Please post what you did to solve it.

Gerry
My paintings and sculpture

 

[Pegasus0990]

apoligies fumei

i avoided the obvious plan of attack because i swore my code was correct/unchanged since a few months ago.

i simplied the code by first taking out the loop and it worked fine. my loop was my issue. when i changed it from "20 to b" to "b to 20" the loop worked fine.

my best guess is that the "20 to b" worked earlier but i had some arthimic in there as well that i must have deleted tweaking with it, and simply forgot about the tweak. now my code is getting the data from Excel like it should and inserting it into my bookmarks.