Excel range to Word table

[brent.fraser]

Happy Monday everyone.
I am importing Excel data into a Word document (where this code resides)and trying to place it into a table in word using the following VBA code:
Dim wdCell As Word.Cell
Dim lnCountItems As Long
Dim wdDoc As Word.Document
For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <>&quot;&quot; Then
tableData = tableData & r.Value
MsgBox r.Value
End If
Next
lnCountItems = 2
For Each wdCell In wdDoc.Tables(11).Columns(2).Cells
wdCell.Range.Text = tableData(lnCountItems, 1)
'wdCell.Range.Text = Left(tableData, Len(tableData) - 1)
lnCountItems = lnCountItems + 1
Next
wdCell
The data is being imported since it is showing up in the MsgBox but I get an error on the following line:
wdCell.Range.Text = tableData(lnCountItems, 1)So I am trying to get this information to be placed in the 11th table (existing already) in the document. It is 2 columns wide. Also, if I use the following code:wdCell.Range.Text = Left(tableData, Len(tableData) - 1)will it make the table as long as the filled in range in the excel sheet? Meaning if 2 rows of the 10 row range has information, will the word table only be 2 rows long?
Thanks everyone.
brent.
P.S. the vba and paragraph formatting wasn't working at the time of this post and that's why this post looks bad.

[fumei]

Could you please declare your variables? For example what - exactly - is tabledata?

[brent.fraser]

Hi Fumei,
The variables are as follows:
For Each r In workbook.Worksheets(&quot;Technical Writing&quot;).Range(&quot;issues&quot;)
If r.Value &quot;&quot; Then
tableData = tableData & r.Value
tableData is each value/cell in the range &quot;issues&quot; in the Excel spreadsheet.

If the cell is empty, it doesn't take it into account when it sends it to Word.

The values are coming across fine since when I call r.value or tableData in a message box, the information in the excel spreadsheet range appears.

It's just trying to put those in a table format in Word.

Hope that makes sense.

[fumei]

The variables are NOT declared. Please declare what they are. Are you using Option Explicit?

What is tabledata? A String? A Long?
What is r?

For Each r In Workbook.Worksheets

So... is r a cell? A row? A range?

You are NOT declaring your variables.

[brent.fraser]

the variables are as follows:

Dim workbook As workbook
Dim r As Excel.Range

The entire code is listed below:


Public strExcelFile As String, workbook As String, sFileName As String
'############## Where you browse to the Excel File ###############
Public Sub GetFilePath()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the weekly report to use"
fd.Filters.Add "Excel Files", "*.xls; *.xlsx", 1
If fd.Show Then
strExcelFile = fd.SelectedItems(1)
End If
sFileName = Dir(strExcelFile)
End Sub
Public Sub importExcelData()
Dim workbook As workbook
Dim r As Excel.Range
Application.ScreenUpdating = False
GetFilePath
iReturnValue = MsgBox("Is this the correct file?" & vbLf + sFileName, vbYesNo + vbQuestion, "Is this the Correct File?")
If iReturnValue = vbYes Then
Set workbook = Workbooks.Open(strExcelFile, True, True)
Else
MsgBox "Select the correct file to use."
GetFilePath
End If

'############ Manpower Information ####################
For Each r In workbook.Worksheets("Project Controls").Range("manpower")
If r.Value <> "" Then
projectControlsManpower = projectControlsManpower & r.Value & vbLf
End If
Next
Dim oCC_projectControlsManpower As ContentControl
Set oCC_projectControlsManpower = ActiveDocument.SelectContentControlsByTitle("projectControlsManpower").Item(1)
oCC_projectControlsManpower.Range.Text = Left(projectControlsManpower, Len(projectControlsManpower) - 1)
'############ Issues Table ####################
Dim wdCell As Word.Cell
Dim lnCountItems As Long
Dim wdDoc As Word.Document
For Each r In workbook.Worksheets("Project Controls").Range("issues")
If r.Value <> "" Then
tableData = tableData & r.Value
MsgBox tableData
End If
Next
lnCountItems = 2
For Each wdCell In wdDoc.Tables(11).Columns(2).Cells
wdCell.Range.Text = tableData(lnCountItems, 1)
wdCell.Range.Text = Left(tableData, Len(tableData) - 1)
lnCountItems = lnCountItems + 1
Next wdCell
workbook.Close False
Set workbook = Nothing
Application.ScreenUpdating = True
End Sub

The variables seem to be declared in this code.

Everything is working except putting the tableData into a table. I call it to display in a msgBox and it appears so I know it is coming over from the Excel file.

Hope all the code helps with this.

[fumei]

You really should be using Option Explicit

iReturnValue is NOT declared
projectControlsManpower is NOT declared
tableData is NOT declared

You say tabledata is displayed in the MsgBox. You have that as:

tableData = tableData & r.Value
MsgBox tableData

OK, that looks like it is a string. But who knows as you do NOT declare tabledata as a variable type.

On the other hand, going into the table cell, you have:

tableData(lnCountItems, 1)

which does NOT look like a string.

So, tell me, what do you get if you messagebox

tableData = tableData & r.Value
MsgBox tableData(lnCountItems, 1)

I suspect you may not get what you think. I suspect that you are not getting the correct content going into the table cell because tableData(lnCountItems, 1) does not make sense to VBA. You REALLY should use Option Explicit.

[fumei]

Everything is working except putting the tableData into a table. I call it to display in a msgBox and it appears so I know it is coming over from the Excel file.

This is incorrect. You are NOT putting tableData into a table. You are putting something quite different. You are putting tableData(lnCountItems, 1)

These are quite different. I think VBA has no idea what tableData(lnCountItems, 1) means....nor do I.

wdCell.Range.Text = tableData(lnCountItems, 1)
wdCell.Range.Text = Left(tableData, Len(tableData) - 1)

Why do you have TWO instructions for the cell. What happens if you just do

wdCell.Range.Text = Left(tableData, Len(tableData) - 1)

[brent.fraser]

This is incorrect. You are NOT putting tableData into a table. You are putting something quite different. You are putting tableData(lnCountItems, 1)

These are quite different. I think VBA has no idea what tableData(lnCountItems, 1) means....nor do I.

wdCell.Range.Text = tableData(lnCountItems, 1)
wdCell.Range.Text = Left(tableData, Len(tableData) - 1)

Why do you have TWO instructions for the cell. What happens if you just do

wdCell.Range.Text = Left(tableData, Len(tableData) - 1)

I had two instructions because one of them was a comment that the ' was left off to make it not run. I was experimenting on things to see if they worked or not.

I tried

wdCell.Range.Text = Left(tableData, Len(tableData) - 1)

and I get the following error
Run-Time error '91':
Object variable or With block variable not set

when I do

MsgBox tableData(lnCountItems, 1)
it stops working at that point with the following error:
Compile Error:
Expected array

When I do

MsgBox tableData
I get the information that is in the range in the excel spreadsheet so I know it's working.


projectControlsManpower is NOT declared
this is a content control in the word document that gets populated with the information in Excel. I got the code from gmaxey and it's working fine since it places the information in the Excel range to the word document. I just tried something and it still worked. I took the "projectControlsManpower" off of "projectControlsManpower = projectControlsManpower & r.Value & vbLf" so it is "projectControlsManpower = r.Value & vbLf" and it still works.


OK, that looks like it is a string. But who knows as you do NOT declare tabledata as a variable type.

I declared tableData as a string but I was still getting the tableData in the MsgBox so I am unclear why it matters if it is declared as a string or not. The information was coming over to the MsgBox.


You really should be using Option Explicit
I researched Option Explicit and why should I be using this and on what?

All in all, I am not a programmer, I am an english major that has been asked to make this happen and I am trying to get it to work. It is all working except for getting the tableData information to be placed in a table in the word document.

Still playing around with it.

Thanks for your time in looking it over.

[fumei]

The only reason some of what you wrote works is because you are NOT using Option Explicit. Option Explicit foces you to declare variables properly. If you do not use it VBA goes ahead and does the best it can and does not gives errors when it gets messed up. It ignores them

when I do

Code:
MsgBox tableData(lnCountItems, 1) it stops working at that point with the following error:
Compile Error:
Expected array

Yes, of course it does...because it IS an array...and you do not have an array.

Please post what you get with the messagebox of tableData. What does tableData look like.

[brent.fraser]

I have so far two cells in the "issues" range in the excel spreadsheet and I call them here:
For Each r In workbook.Worksheets("Technical Writing").Range("issues")

The values in the excel are "what the hell" and "is going on" in two separate cells within the 10 row range called "issues"

When I do the messagebox tableData, I get:
"what the hell" in a message box with an OK button. I click on the OK button and I get another message box with "is going on" with an OK button.

So, the information is coming over. I am just figuring out how to put that information into a table.

[fumei]

You have conceptual problem. And please please start using Option Explicit.

So...you have TWO cells in the range "issues". Ok, lets run through the value of tableData in your code. First, there is:

For Each r In workbook.Worksheets("Project Controls").Range("issues")
If r.Value <> "" Then
tableData = tableData & r.Value
MsgBox tableData
End If
Next

What should happen is you get "what the hell" (the first cell), and you press OK, the code loops back and you should then get
"what the hell is going on". ONE string with the text of BOTH cells.

Please confirm.

So tableData (still not declared) should = "what the hell is going on". Now you have:

For Each wdCell In wdDoc.Tables(11).Columns(2).Cells
wdCell.Range.Text = tableData(lnCountItems, 1)
wdCell.Range.Text = Left(tableData, Len(tableData) - 1)
lnCountItems = lnCountItems + 1
Next wdCell

Because you do not have Option Explicit you do not get an error for wdDoc.Tables(11).Columns(2).Cells You do declare wdDoc as Document...but you never actually set the object.

Get rid of the instruction for the fake array tableData(InCountItems, 1). There is no array.

I am not sure why you are doing a For Each wdCell. There is only ONE value coming from Excel. So try this. Pick a cell (any cell) and put tableData in it. Does it work?

[brent.fraser]

Hi Fumei,


What should happen is you get "what the hell" (the first cell), and you press OK, the code loops back and you should then get
"what the hell is going on". ONE string with the text of BOTH cells.

No, when I get the message box, I get "what the hell" first. I click on OK and get "is going on" in the second one. This is kind of what I want to get since I want each cell in excel to be a cell in the word table. I don't want to be combining them. Basically the table that's in excel, I want to replicate in word (2 columns and multiple rows of data).


I am not sure why you are doing a For Each wdCell. There is only ONE value coming from Excel.

I am getting multiple values from excel and not just one.

I am going to try something like placing the values in this block of code and see if it works:

For Each r In workbook.Worksheets("Project Controls").Range("issues")
If r.Value <> "" Then
tableData = tableData & r.Value
MsgBox tableData
End If
Next

Since it appears in the MsgBox, I can place it in a table and then go on to the next one.

It's worth a try.

Also, I will look at Option Explicit and see how that works.

Thanks again for the assistance.

Hope all is well in B.C.

[brent.fraser]

I have turned on Option Explicit and declared all variables.


Dim workbook As workbook
Dim r As Excel.Range
Dim iReturnValue As String
Dim wdCell As Word.Cell
Dim lnCountItems As Long
Dim tableData As String
Dim wdDoc As Word.Document


I ran in debug mode and it stops working here:

For Each wdCell In wdDoc.Tables(11).Columns(1).Cells

I don't get any variable errors since everything is declared.

[brent.fraser]

well, I am getting closer (sort of).

Some information is coming into the table using the following:


For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
tableData = r.Value
For Each wdCell In ActiveDocument.Tables(4).Rows(2).Cells
wdCell.Range.Text = tableData
Next wdCell
MsgBox tableData
End If
Next

But it is the same information that goes into both columns. Also, I have now two rows/two columns of information in the excel document (so 4 cells of information) and it is posting only the last cell of information from the excel to the word document.

I know it has something to do with a count of items in excel (4) and a count of cells in the word table so the 4th item goes into the 4th cell in the table.

getting there....

[brent.fraser]

I think I might have to put the tableData into an array and then put the information into the table.

I am getting the correct information in the MsgBox so I know it is pulling it from the excel sheet.

Maybe the array is the way to go.

[fumei]

Yes, changing

tableData = tableData & r.Value

to

tableData = r.Value

means that tableData gets a different value each iteration, rather than r.Value being ADDED to tableData. OK, so that is that. Let me get this straight. You have FOUR cells in the excel range, and you want each of those to go into separate four cells in Word.

There are two ways to go about this.

1. For each r in Excel, pass its value to a specific cell in the Word table.

2. For each r in Excel build an array of those values. THEN take that array and put the values into the Wod cells. In this case you still need to know the specific cells in the Word table the values must go into. You could use an X number of table cells, but they would most likely have to be IN ORDER.

[fumei]

Ah, did not see your previous post. Yes, either an array of tableData, or post each r.Value into a table cells at the time you get the r.Value.

[brent.fraser]

Ah, did not see your previous post. Yes, either an array of tableData, or post each r.Value into a table cells at the time you get the r.Value.

thanks for the reply.

i am thinking it might be easiest to post the value into the table cell at the time I get r.value since building an array seems daunting in this end.

how is the best way to put the information into the table cells? I have tried


For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
tableData = r.Value
For Each wdCell In ActiveDocument.Tables(4).Rows(2).Cells
wdCell.Range.Text = tableData
Next wdCell
MsgBox tableData
End If
Next


and it hasn't worked out like I was hoping to. I am getting the last text of the 4 cells coming over to the word table and repeating them in both columns so it is missing placing the first 3 entries.

so close yet so far....

still playing around with it. I will also see if I can get an array figured out too.

thanks again.

[fumei]

Try using some debugging tools. SEE what the value of tableData is before it is placed in the cells. Let's say your four values in Excel are:

One
Two
Three
Four

And assuming (you have not stated) there are four cells in Row2, I am assuming what you want is
Cell1 = One, Cell2 = Two, Cell3 = Three, Cell4= Four

I may be wrong about that, but here is what the code does.

For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
tableData = r.Value
For Each wdCell In ActiveDocument.Tables(4).Rows(2).Cells
wdCell.Range.Text = tableData
Next wdCell
MsgBox tableData
End If
Next

For each r.....(r = One)
tableData = One
For each wdCell in row
wdCell text = One

This means ALL of the cells = One.

This repeats for all the r, thus you end up - as you note - I am getting the last text of the 4 cells coming over

I ask again, why are you doing a for each cell.

[fumei]

BTW, if there is a one-to-one relationship between the number of r, and the number of cells in the table row, you can try

Dim i As Long
i = 1
For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
ActiveDocument.Tables(4).Rows(2).Cells(i).Range.Text = r.Value
End If
i = i + 1
Next

Of course if the number of r is greater than the number of cells in the row, you will get an error.

[brent.fraser]

I found some code that seemed to do what I needed and it contained "for each." I was thinking that the code will go through each cell in the table (for each) and put the information in it. Guess that was wrong.

for the table, it is actually two columns (#1 is "issues" and #2 is "solutions") worth of data but in the excel, I have 2 columns by 10 rows as the range.

Issue. Solution
one. Two
three. Four
five. Six

And so on......

I used a message box to display what tableData is after getting r.value. If we use your example, I see (in 4 separate message boxes), one, two, three and four so it is taking the data from excel correctly.

Maybe it would be easier to take each column as a range then put those I to the word table. Might make it easier in the end.

and I didn't think this was going to be too hard when my boss asked me to do this....LOL

[brent.fraser]

Erased post since previous post didn't show up so I re-wrote it and then it appeared.....

[brent.fraser]

Hi Fumei,

your suggestion worked perfectly. I used the following and it is working very well:


Dim i As Long
i = 2
For Each R In workbook.Worksheets("Technical Writing").Range("issues")
If R.Value <> "" Then
ActiveDocument.Tables(4).Rows.Add.Shading.BackgroundPatternColor = wdColorWhite
ActiveDocument.Tables(4).Columns(1).Cells(i).Range.Text = R.Value
End If
i = i + 1
Next

I broke up the range in the Excel sheet into two separate columns (I had the range as two columns together and it made this more difficult). Now I call one range into column 1 and the other range into column 2 and it all works.

Thanks for the guidance and pointing me in the right direction. I learned a lot.

B.

[fumei]

Glad you got things to move forward. I have some final comments.

Move the instruction: ActiveDocument.Tables(4).Rows.Add.Shading.BackgroundPatternColor = wdColorWhite

into its own line BEFORE the For Each R. Why do it every time? In the loop it is executed for every R.

If you do want to keep the original ranges in Excel, you can do something like this:



Dim oCell As Cell
Set oCell = ActiveDocument.Tables(1).Rows(2).Cells(1)

ActiveDocument.Tables(4).Rows.Add.Shading.BackgroundPatternColor = wdColorWhite

For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
oCell.Range.Text = r.Value
Set oCell = oCell.Next
End If
Next This makes a cell OBJECT for the start of row(2). If R <>"", then R.Value goes into that cell, and the object is Set for the next cell. This will be the cell in the next column. If the next R <> "", its value goes into that cell. The cell object is set for the next cell. This will be row(3), col(1).

And so on.

This uses OBJECTS and is really the best way to use VBA.

[fumei]

Oh, and if you are satisfied with the thread please mark it as Solved.

[brent.fraser]

Hey Fumei,

I have marked the thread as solved. Thank you.

I am investigating the OBJECTS you are talking about since I want to learn the best way to do VBA.

I am trying to use some of the code you have above. I still would like to have the ranges separated (to solve some possible user issues).

When I use

Dim oCell As Cell
Set oCell = ActiveDocument.Tables(4).Columns(1).Cells(2)

ActiveDocument.Tables(4).Rows.Add.Shading.BackgroundPatternColor = wdColorWhite

For Each r In workbook.Worksheets("Technical Writing").Range("issues")
If r.Value <> "" Then
oCell.Range.Text = r.Value
Set oCell = oCell.Next
End If
Next


When I change it to only go on the first column, second cell (leaving the heading row alone), I get the first value in column 1, row 2 (which is what I want) but the next value goes into column 2, row 2 and not column 1 row 3.

I am assuming it is because of the
Dim oCell As Cell


Or is it because of the
Set oCell = oCell.Next

[fumei]

Cell.Next does not work for the next cell in columns. It only works for rows. That is, Next is the nextcell of a row. So you can ignore the header rows by doing what I coded. ROW2, col1

Not sure why you wanted to start with columns.

Row2, Col1 is the same as col1, row2...EXCEPT Word works with rows first, so use it that way.

"When I change it to only go on the first column, second cell (leaving the heading row alone), ". So use Row2, Col1. NEXT then becomes Row2,Col2. Mind you that means you are not going into Row3, Col1. However, the way you described it was:

One, Two
Three, Four.

Not:

One Three
Two Four.

[fumei]

Perhaps I should explain further. Next is a property of Range. The default range in a Word table is always ROW. Although it is not hugely significant in that you should be able to do what you say you want...insert ranges from Excel (IN ORDER) to a Word table IN THE SAME ORDER.

[brent.fraser]

I think I will leave it the way it is then. I will just put the first column down then go to the next. I have run into times where people put a space in a cell and it looks blank but it has a character so
r <>"" will not work and it will throw my entire thing off if I do .next.

I had just thought I could use OBJECTS in the colum by column scenario.

Thanks again for your help.

[fumei]

Please explain why you need to do it using columns. As described I fail to see the need.

R <> "" means that the range IN EXCEL is not blank. Not Word.

If someone has something IN EXCEL that is inappropriate, this does not affect how things work IN WORD. If you need to validate say a single character in Excel, then you should do that validation in Excel before trying to pass it over to Word.

[fumei]

If Len(R) > 1 Then

would mean if r is AT LEAST 2 characters. If R is a single character it is NOT passed to Word.

[fumei]

You possibly use:

Dim sngHeight As Single
Dim sngWidth As Single
Dim sngCropTop As Single
Dim sngCropBottom As Single
Dim var

sngCropTop = 0.154
sngCropBottom = 0.05


With ActiveDocument.InlineShapes(1)
sngHeight = .Height
sngWidth = .Width
With .PictureFormat
.CropTop = sngHeight * sngCropTop
.CropBottom = sngHeight * sngCropBottom
End With
.Height = .Height
.Width = .Width
End With

sngCropTop = 0.014
sngCropBottom = 0.069

For var = 2 To ActiveDocument.InlineShapes.Count
With ActiveDocument.InlineShapes(var)
sngHeight = .Height
sngWidth = .Width
With .PictureFormat
.CropTop = sngHeight * sngCropTop
.CropBottom = sngHeight * sngCropBottom
End With
.Height = .Height
.Width = .Width
End With
Next

This uses the first values for the FIRST inlineshape, the second set of values for all the rest, regardless of how many more there are. It avoids using any moving of Selection...or Selection at all.

[brent.fraser]

Hi Fumei,

I ended up going with your suggestion and I am using the coding you have provided. I will just explain to the people that if they have information in the first column, they need SOMETNING in the next one.

It definately cuts down on the amount of code and that's a great thing.

Thanks once again for your assistance.