Putting Cell Value Into Variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IWT - 03 Jan 2006 11:58 GMT

Hi,

I've searched these forums but can't seem to find an answer to this
question, although it seems fairly trivial (I hope).

When running a macro, how do you put a value in a table cell into a
varibale?
I've got a table like this in Word:

Row | Column | Data
0 | 1 | TestData1
2 | 2 | TestData2

and what I want to do is extract the row & column numbers from this
table, then place the data in the corresponding position in an Excel
spreadsheet.

Many thanks,

Tom

Reply to this Message

Flemming Dahl - 03 Jan 2006 12:24 GMT

Hi Tom

myString = ActiveDocument.Table(1).Cell(2,2).Range.Text

Search this group and you will find lots of qustion about tables.

Hope this startes you,
Flemming

> Hi,
>
[quoted text clipped - 16 lines]
>
> Tom

Reply to this Message

Helmut Weber - 03 Jan 2006 12:45 GMT

Hi Tom,

like this:

Sub test1234()
' excel already running
' reference to excel set ' = early binding
' workbook already open
Dim oExc As Excel.Application
Dim oWrk As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim oTbl As Table
Dim sTmp As String

Set oExc = GetObject(, "excel.application")
Set oWrk = oExc.ActiveWorkbook
Set oSht = oWrk.ActiveSheet
Set oTbl = ActiveDocument.Tables(1)
Dim r As Long
Dim c As Long
r = ActiveDocument.Tables(1).Rows.Count
c = ActiveDocument.Tables(1).Columns.Count

For c = 1 To oTbl.Columns.Count
For r = 1 To oTbl.Columns.Count
sTmp = oTbl.Cell(r, c).Range.Text
sTmp = Left(sTmp, Len(sTmp) - 2)
oSht.Cells(r, c).Value = sTmp
Next
Next
' some more code depending on what you want to do
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

Reply to this Message

IWT - 03 Jan 2006 14:06 GMT

Thank you both for your responses.

Helmut, your code is helpful, but what I want to is place "TestData1"
in row 0, column 1 of the spreadsheet and "TestData2" in row 2, column
2, rather than just replicating the whole table in Excel.

I was thinking something like:

....
Set oTbl = ActiveDocument.Tables(1)
...

For r = 1 To oTbl.Rows.Count

// get numeric value of "Row" column
// get numeric value of "Column" column
// get value of "Data" column
oSht.Cells(rowNum,colNum).Value = data

Next r

Would appreciate your help in filling in the blanks,

Thanks,

Tom

Reply to this Message

Helmut Weber - 03 Jan 2006 14:56 GMT

Hi Tom,
like this:

Sub test1235()
' excel already running
' reference to excel set ' = early binding
' workbook already open
Dim oExc As Excel.Application
Dim oWrk As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim oTbl As Table
Dim sTmp As String

Set oExc = GetObject(, "excel.application")
Set oWrk = oExc.ActiveWorkbook
Set oSht = oWrk.ActiveSheet
Set oTbl = ActiveDocument.Tables(1)
Dim rWrd As Long ' Word row
Dim cExc As Long ' Excel column
Dim rExc As Long ' Excel cell

For r = 1 To oTbl.Rows.Count
sTmp = oTbl.Cell(r, 1).Range.Text
sTmp = Left(sTmp, Len(sTmp) - 2)
cExc = CLng(sTmp)
sTmp = oTbl.Cell(r, 2).Range.Text
sTmp = Left(sTmp, Len(sTmp) - 2)
rExc = CLng(sTmp)
sTmp = oTbl.Cell(r, 3).Range.Text
sTmp = Left(sTmp, Len(sTmp) - 2)
oSht.Cells(rExc, cExc).Value = sTmp
Next
' some more code depending on what you want to do

End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

Reply to this Message

IWT - 03 Jan 2006 15:22 GMT

Thanks for your help.

I managed to get it working using:

....
' Get row/column position of data and set Excel cell to that value
For r = 1 To oTbl.Rows.Count

row = oTbl.Cell(r, 1).Range.Text
column = oTbl.Cell(r, 2).Range.Text

rowNum = Val(row)
columnNum = Val(column)

data = oTbl.Cell(r, 3).Range.Text
data = Left(data, Len(data) - 2)

oSht.Cells(rowNum, columnNum).Value = data

Next r
....

Tom

Reply to this Message