I am working on a problem generator for my students. I am controlling Word from Excel which doesn't seem to be a problem. I have a spreadsheet which generates random problems using Randbetween(). My VBA function, in a loop, sends a problem to the Word document and then refreshes and loops again. There is a TRUE/FALSE value in a cell which changes based on the selected problem. I know it changes when I manually do F9 on the sheet. The entire sheet recalculates. According to my VBA function the value of the cell (T4) stays the same (TRUE). I've attached the Workbook. I would appreciate any help. Thanks in advance.
Your webpage doesn't make it very clear about how to use xl2bb. I tried
I am working on a problem generator for my students. I am controlling Word from Excel which doesn't seem to be a problem. I have a spreadsheet which generates random problems using Randbetween(). My VBA function, in a loop, sends a problem to the Word document and then refreshes and loops again. There is a TRUE/FALSE value in a cell which changes based on the selected problem. I know it changes when I manually do F9 on the sheet. The entire sheet recalculates. According to my VBA function the value of the cell (T4) stays the same (TRUE). I've attached the Workbook. I would appreciate any help. Thanks in advance.
Your webpage doesn't make it very clear about how to use xl2bb. I tried
Here's the VBA code
Option Explicit
Public TheresNoPicture As String
Public Sheetcode As String
Public NumberofQuestions As Integer
Function OpenWordTempandInsertTable(Sheetcode)
Dim intNoOfRows As Integer
Dim intNoOfColumns
Dim objWord As Word.Application
Dim wordapp As Word.Application
Dim objDoc As Document
Dim objRange
Dim objTable As Table
Dim strFile As String
Dim i As Integer
Dim xlsheet As Worksheet
Dim wb As Workbook
Dim Question As String
Dim Answer As Long
Dim TorF As String
Dim PicPath As String
Dim Place As Integer
Dim Pic As Picture
Dim DestinationCell As Range
Dim vlookuprange As Range
NumberofQuestions = UserForm2.TextBox1
intNoOfRows = 2 * NumberofQuestions + 6
intNoOfColumns = 3
i = 1
'Opens a Word template I created for this project.
strFile = "C:\Users\Mark\Desktop\VBA-Python\Worksheet Template.docm"
Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open strFile
wordapp.Visible = True
wordapp.Activate
'Inserts a Word table with 3 columns and intNoOfRows plus 2 more rows for possible instructions.
Set objDoc = wordapp.Documents("Worksheet Template")
Set objRange = objDoc.Range
objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns
Set objTable = objDoc.Tables(1)
objTable.Borders.Enable = True
objTable.Columns(1).Width = InchesToPoints(0.5)
objTable.Columns(2).Width = InchesToPoints(2.8)
objTable.Columns(3).Width = InchesToPoints(2.8)
Set wb = ThisWorkbook
Set xlsheet = wb.Sheets(Sheetcode)
Do Until i = NumberofQuestions + 1
TorF = xlsheet.Range("T4").Value
Question = xlsheet.Range("Q4").Value
Answer = xlsheet.Range("R4").Value
'MsgBox "T4"
If TorF = "True" Then
With objTable
.Cell(i + 2, 2).Range.InsertAfter Question
.Cell(i + 2, 1).Range.InsertAfter (i & "U")
.Cell(i + NumberofQuestions + 4, 2).Range.InsertAfter Answer
.Cell(i + NumberofQuestions + 4, 1).Range.InsertAfter (i & "U")
End With
Else
With objTable
.Cell(i + 2, 2).Range.InsertAfter Question
.Cell(i + 2, 1).Range.InsertAfter (i & "K")
.Cell(i + NumberofQuestions + 4, 2).Range.InsertAfter Answer
.Cell(i + NumberofQuestions + 4, 1).Range.InsertAfter (i & "K")
End With
End If
xlsheet.Calculate
xlsheet.Range("T4").Dirty
i = i + 1
Loop
End Function
