Export to Word Table

[gstylianou]

Dear friends,




I would like to have your help in the following:


I have a continuous form which i can create a weekly meal plan. Through the form I can select the day, the type of meal and then the portion size.


I want to export these data into a Word file and especially into a Word Table depending on the type of meal. Is that possible?

I attach an example of the database as well as the Word file


Thanks in advanced

[June7]

It's possible but because it is not a straightforward mailmerge (need to populate a Word table), code gets a little complicated. Review https://www.accessforums.net/access/...rds-20712.html

[gstylianou]

Hi June7,

I tried with the following code but i'm getting errors...i'm not so familar with vba.. Can you help me please to build the code?

Private Sub cmdWord_Click()
'ÃÝìéóìá ðßíáêá ôïõ Word


Dim appWord As Object, D As String, K As String
Dim Col As Integer, Row As Integer, DocName As String
Dim doc As Object, j As Long



DocName = "DietPlan.dot"


If Me.RecordsetClone.RecordCount Then

On Error Resume Next
Err.Clear


Set appWord = GetObject(, "Word.Application")


If Err.Number <> 0 Then


Set appWord = CreateObject("Word.Application")


End If


On Error GoTo errHandler


appWord.Documents.Add CurrentProject.Path & "\" & DocName
Set doc = appWord.Documents(appWord.Documents.Count)


With Me.RecordsetClone
.MoveFirst
Do Until .EOF
For j = 1 To .Fields.Count - 2
D = .Fields(j).Name
K = !MealCode
Row = Switch(K = "Breakfast", 2, K = "Snak1", 3, K = "Lunch", 4, K = "Snak2", 5, K = "Dinner", 6, K = "Snak3", 7, K = "Night", 8)
Col = Switch(D = "Monday", 2, D = "Tuesday", 3, D = "Wednesday", 4, D = "Thursday", 5, _
D = "Friday", 6, D = "Saturday", 7, D = "Sunday", 8)


doc.Tables(1).Cell(Row, Col).Range = Nz(.Fields(j).Value, "")
Next
.MoveNext
Loop
End With


appWord.Visible = True
appWord.Activate


End If


Set doc = Nothing


Set appWord = Nothing


Exit Sub


errHandler:


MsgBox Err.Number & ": " & Err.Description


End Sub

[June7]

What are the errors - exact message? I've never tried to do this before.

Why do you need to use Word? Why not Access report?

[gstylianou]

I need to export data into a word table because the issue is to merge data from word with the data from access. Believe me is a complicated story..


I know about access report but its not a good solution from this case. Anyway, i'v tried many things so i god many errors such as the attached

[June7]

What line throws that error?

I tried running the procedure and get error about 'communicating with OLE Server or ActiveX control'. Can't even get as far as you do.

[gstylianou]

Told you June7, i'm not familiar with vba... Could you help in order to build a new code in order to do that?

Thanks again

[June7]

Since I can't work with your db, I would have to work with mine and anything I could do with my own db probably won't be very helpful to you.

Even the expression in textbox to concatenate first and last names is not working. This is all very odd. Maybe it has something to do with the foreign language (Russian, Greek?) characters.

Suggest you get rid of the error handler lines and let the debugger stop on the line that triggers the error message and tell me what line that is.

If you are trying to export data from the subform, the code won't do that. It is using RecordsetClone of the main form.



Advise that you not use spaces and special characters/punctuation in naming convention. Using %, (, ), etc in names can cause issues.

[gstylianou]

Good morning my friend June7,

Yes, i'm using Greek lang and maybe that will be problem on your pc. You wrote: Suggest you get rid of the error handler lines and let the debugger stop on the line that triggers the error message and tell me what line that is.
How can i do that?

Thanks in advanced

[June7]

You can comment out the lines that deal with error handler.

Review link at bottom of my post for debugging guidelines.

[gstylianou]

Thanks June7, i will let you know

[June7]

Maybe this will help you. I did a very simple test of VBA code opening your Word document and populating cells of table.

Private Sub testWord()
Dim appWord As Object, doc As Object
Set appWord = CreateObject("Word.Application")
Set doc = appWord.Documents.Add CurrentProject.Path & "\" & DocName
doc.Tables(1).Cell(2, 2).Range = "ABC"
doc.Tables(1).Cell(2, 3).Range = "DEF"
doc.Tables(1).Cell(2, 4).Range = "GHI"
appWord.Visible = True
appWord.Activate
Set doc = Nothing
Set appWord = Nothing
End Sub

Get that much to work then move on to using looping and recordset.

[gstylianou]

Good morning my friend June7,

I tried to check last night the vba code but i'm getting the attached error. Is it possible for you to prepare a sample please ?

Thanks in advance

[June7]

No, not going to build a db for this simple code

I didn't actually use that line in my testing. I used:

Set doc = appWord.Documents.Open("C:\Users\June\Weekly Diet Plan.docx")

You can try:

Set doc = appWord.Documents.Open(CurrentProject.Path & "\Weekly Diet Plan.docx")

[gstylianou]

Ok, now is working.! Thanks.

But, how can i collect the data from each day and meal type and then export then to word? As i saw, i must do something with the code in order to do that.
Eg.
doc.Tables(1).Cell(2, 2).Range = "ABC"doc.Tables(1).Cell(2, 3).Range = "DEF"
doc.Tables(1).Cell(2, 4).Range = "GHI"

Is it possible to give me a "hand" for that bro?

Thanks in advanced