Hi guys, wonder if you can help
I'm working on a project and am slightly out of my depth! I'm trying to create a "report" button which generates a word file with the contents of the current worksheet in a nice table
Currently the word file is created and the data is in there... From within Word, all that needs to happen is selecting all the text and then clicking Table>Convert>Text to Table
I borrowed some code from somebody elses example and modified it a bit
This bit
" .Range.ConvertToTable Separator:=wdSeparateByTabs"
is the part which should be converting the text into a table... But it isn't!
Anybody got any thoughts?
I'm working on a project and am slightly out of my depth! I'm trying to create a "report" button which generates a word file with the contents of the current worksheet in a nice table
Currently the word file is created and the data is in there... From within Word, all that needs to happen is selecting all the text and then clicking Table>Convert>Text to Table
I borrowed some code from somebody elses example and modified it a bit
Code:
Sub individualreport()
Dim c As Range
Dim lCount As Long
Dim MyDate As String
Dim MyDay As String
Dim MyMonth As String
Dim MyYear As String
MyYear = Year(Date)
MyMonth = Month(Date)
If Month(Date) < 10 Then MyMonth = "0" & Month(Date)
MyDay = Day(Date)
If Day(Date) < 10 Then MyDay = "0" & Day(Date)
MyDate = MyDay & "_" & MyMonth & "_" & MyYear
' J & R Excel Solutions
' Creates Word document of table in sheet1
Dim WordApp As Object
Dim LastRow As Integer, i As Integer, r As Integer, Records As Integer
Dim Wdoc As String
'get the name of the new document
Wdoc = InputBox("Please enter a name for the new document", "Enter name")
On Error Resume Next
Application.ScreenUpdating = False
' Start Word and create an new document
Set WordApp = CreateObject("Word.Application")
With WordApp
.documents.Add
With .Selection
For Each c In Worksheets("Appraisees List").Range("A12:F47") 'change to this range-Range("A1:F47")
.InsertAfter Text:=c.Value 'counts columns in range and adds a tab after each column
lCount = lCount + 1
If lCount Mod 6 = 0 Then 'determines last column (Mod number of columns)
.InsertAfter Text:=vbCr 'adds a carriage return
Else
.InsertAfter Text:=vbTab 'tab character
End If
Next c
[B] .Range.ConvertToTable Separator:=wdSeparateByTabs[/B]
End With
' Determine the file name NEED TO PUT IN DATE!
SaveAsName = ThisWorkbook.Path & "\" & MyDate & "_" & Wdoc & ".doc"
' Update status bar progress message
Application.StatusBar = "Saving data to Word Document " & Records
' Save the Word file and close it
With WordApp
.ActiveDocument.SaveAs Filename:=SaveAsName
.ActiveWindow.Close
' Kill the object
.Quit
End With
Set WordApp = Nothing
End With
' Reset status bar
Application.StatusBar = ""
Call MsgBox("Your document has been created successfully. & saved in " & ThisWorkbook.Path, vbInformation, "Success")
End Sub
" .Range.ConvertToTable Separator:=wdSeparateByTabs"
is the part which should be converting the text into a table... But it isn't!
Anybody got any thoughts?