Export to Word Table

[June7]

Now incorporate the RecordsetClone code. I presume you want data from the subform. I have not tested this so you will have to debug. Refer to guidelines in link at bottom of my post.

Dim j As Integer, Col As Integer, Row As Integer, D As String, K As String
        With Me.subfrmDietPlanDetails.Form.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

[gstylianou]

Thanks June7

Give me time to check it when i will be home and i will let you know

Thanks a lot my friend and sorry if you spent time for me. As i told, i don't know about vba...

[gstylianou]

Good morning June7,

Following i'm attaching the modified code but still having problems....Please your help..

Private Sub cmdWord_Click()
Dim appWord As Object, doc As Object
Set appWord = CreateObject("Word.Application")
Set doc = appWord.Documents.Open(CurrentProject.Path & "\Weekly Diet Plan.docx")


Dim j As Integer, Col As Integer, Row As Integer, D As String, K As String
With Me.subfrmDietPlanDetails.Form.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


'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

[June7]

What is the problem - error message, wrong results, nothing happens? Have you step debugged?

[gstylianou]

What is the problem - error message, wrong results, nothing happens? Have you step debugged?

The bold part of the code its error message..

[June7]

That is the line that triggers bugger. But what is the error message.

[gstylianou]

June7 i'm not getting any error message. Access just stuck and need to make End Task to terminate the program.... Really don't know..
Can you try it with the attached sample?

[June7]

Remember - I can't run your db. I can look at the design again later but without being able to run the code, makes it hard to analyse issue.

[June7]

The MealCode is a number field but you are checking for text values in the Switch() function.

Use an expression in the textbox:=[Type of Meal].[Column](1)

Actually, don't need the MealCode textbox or field. There is no need for both [Type of Meal] and MealCode fields in tblDietDetails.

Change the VBA to pull meal code from the combobox:
Row = Me.[Type of Meal]
Then would not need the Switch function at all nor the K variable.


Also, you have lookups in tables. Advise not to do that http://access.mvps.org/access/lookupfields.htm

[gstylianou]

The MealCode is a number field but you are checking for text values in the Switch() function.

Use an expression in the textbox:=[Type of Meal].[Column](1)

Actually, don't need the MealCode textbox or field. There is no need for both [Type of Meal] and MealCode fields in tblDietDetails.

Change the VBA to pull meal code from the combobox:
Row = Me.[Type of Meal]
Then would not need the Switch function at all nor the K variable.


Also, you have lookups in tables. Advise not to do that http://access.mvps.org/access/lookupfields.htm

Good Morning my friend June7,

Really its amazing..!! I have tried so many things but still having problems... I changed the Row but nothing...
Please do something... Tell me a way in order to run my dbase so to fix the problem. What are you need to do that my friend?

The modified code again..

Private Sub cmdWord_Click()
Dim appWord As Object, doc As Object
Set appWord = CreateObject("Word.Application")
'i' m getting the attached picture message
Set doc = appWord.Documents.Open(CurrentProject.Path & "\Weekly Diet Plan.docx")


Dim j As Integer, Col As Integer, Row As Integer, D As String, K As String
With Me.subfrmDietPlanDetails.Form.RecordsetClone
.MoveFirst
Do Until .EOF
For j = 1 To .Fields.Count - 2
D = .Fields(j).Name
Row = Me.[Type of Meal]
' 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


''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



The database stack again and needs Ctr - Alt - Delete. When i'm doing this i saw that the "File Running" (i mean the Word)

[June7]

There is no image attached to your post. Didn't you have the code to open Excel file working before?

Sorry, forgot something in the revised code:

Row = Me.[Type of Meal] + 1

[gstylianou]

There is no image attached to your post. Didn't you have the code to open Excel file working before?

Sorry, forgot something in the revised code:

Row = Me.[Type of Meal] + 1

.................................................. .................................................. .................................................. ...........................................

Good morning my friend June7,

June7, really is very frustrating ...


Nothing happened ... I tried to modified the code so can get value from subfrmDietPlanDetails where is the field [Type Of Meal] but again nothing happened. In my effort choosing the command to make export the data to Word, again blocked the database and nor the Word file opens. Therefore i must do Ctr-Alt - Delete in order to disengage the file.


Please your help.! It's very importand for me to solve this issue in order to complete the database design. I think should be better if we can find a solution so you will be able to run my database



Private Sub cmdWord_Click()
Dim appWord As Object, doc As Object
Set appWord = CreateObject("Word.Application")
Set doc = appWord.Documents.Open(CurrentProject.Path & "\Weekly Diet Plan.docx")


Dim j As Integer, Col As Integer, Row As Integer, D As String, K As String
With Me.subfrmDietPlanDetails.Form.RecordsetClone
.MoveFirst
Do Until .EOF
For j = 1 To .Fields.Count - 2
D = .Fields(j).Name
Row = subfrmDietPlanDetails.[Type_of_Meal] + 1
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


''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

[June7]

I will work on this over weekend.

[gstylianou]

Thank you my friend.
Lets do it!!☺

[June7]

I disabled the Load and Open events for the form and subform because of the error. I don't know why those procedures are causing issue.

Also don't know why the Food combobox RowSource cannot find the [Type of Meal] field so I removed the parameter from the RowSource.

Now the form opens without errors. Unfortunately, none of the event code will run for me, not even button Click. I even imported to a new db and still won't run. So I built a new table in a new database and very simple forms to emulate your db.

Just as the MealCode is a number field, so is the DayCode. The Switch function looking for day names won't work.

This code works:

Dim appWord As Object
Dim Col As Integer, Row As Integer, DocName As String
Dim doc As Object, j As Long
DocName = "Weekly Diet Plan.docx"
With Me.subfrmDietPlanDetails.Form.RecordsetClone
    If .RecordCount > 0 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)
            .MoveFirst
            Do Until .EOF
                For j = 1 To .Fields.Count - 2
                    Row = !MealCode + 1
                    Col = !DayCode + 1
                    doc.Tables(1).Cell(Row, Col).Range = Nz(.Fields(j), "")
                Next
                .MoveNext
            Loop
        appWord.Visible = True
        appWord.Activate
    End If
End With
ExitProc:
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitProc

The DietPlan.dot document opens non-editable so I used the WeeklyDietPlan.docx file (I removed spaces from file name).


Did I already mention best not to use spaces and special characters/punctuation (underscore is exception) in naming convention? Better would be: Alcohol_Pct