Export to Excel (rich Text) column not formatting correctly (1 Viewer)

Keevin77

Registered User.
Local time
Today, 05:05
Joined
May 2, 2014
Messages
13
Export to Excel column not formatting correctly, need HTML Tags removed help.

Good afternoon, I have a process that is getting data from sharepoint page, containing 2 list. Both list contain one field I am having issues in my export, a 'Multiple Lines of Text' type field which is Rich Text in sharepoint. I have an access DB with a linked table to those 2 list. Access show the one field I am having problems with as Memo.

The Linked table pulls/display the rich text data field correctly. My access query pulls/displays the data correctly.

My export procedure does not, the cell it exports to includes the HTML Tags. How can i modify my VBA export procedure to export to excel in plain text, i dont need the same format, just want to drop the html tags. Any help is appreciated.

Code:
Public Sub StatusReportExport()
On Error Resume Next
 
    ' Test to see if the file currently exist, if so, delete file, so new file can be written.
    Kill ("L:\Connect Site\Status Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls")
    If Dir("L:\Connect Site\Status Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls") <> "" Then
        MsgBox "Status Report.xls File already in use!" & vbNewLine & "Please Close File, then rerun Report."
    Else
        x = 0
        Dim strOutputFile As String
        Dim strTemplateFile As String
        Dim intCells As Long
        Dim xlApp As Excel.Application
        Dim xlWorkbook As Workbook
 
        'M = Month(Date)
        'Y = Year(Date)
        'D = Day(Date)
        strTemplateFile = strHmtTemplateFile
        strOutputFile = "L:\Connect Site\Status Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ""
        Saveitas = "L:\Connect Site\Status Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls"
        DoEvents
 
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Workbooks.Open strTemplateFile
        xlApp.Visible = False
 
        DoEvents
        'Export routines
        'Store Data
 
        'Access Querys, data pulled from linked table to sharepoint
        QueryName = "Complete6"
        Export_Data_To_Excel xlApp, QueryName, 5, 256, "Complete"
 
        QueryName = "Project6"
        Export_Data_To_Excel xlApp, QueryName, 4, 256, "Project Status"
 
        If Len(Dir$(Saveitas)) > 0 Then
            SetAttr Saveitas, vbNormal
            VBA.Kill Saveitas
        End If
 
        xlApp.Sheets("Complete").Activate
        Debug.Print
        xlApp.ActiveWorkbook.SaveAs Saveitas
        xlApp.Visible = False
        xlApp.Quit
        Debug.Print
 
        Set xlApp = Nothing
        DoCmd.SetWarnings False
    End If
StatusReportExport_Exit:
    Exit Sub
End Sub
Public Function Export_Data_To_Excel(xlApp As Excel.Application, Source_Table As String, Field_Count As Long, Initial_Cell As Long, Workbook_tab As String)
    Dim db1 As Database
    Dim rst1 As Recordset
    Dim fld1 As Field
    Dim intRow As Long
    Dim intColumn As Long
 
    intCells = Initial_Cell
    Set db1 = CurrentDb()
    Set rst1 = db1.OpenRecordset(QueryName)
 
    xlApp.Sheets(Workbook_tab).Activate
    If x = 0 Then
    intRow = 4
    intColumn = 1
    End If
 
    Do Until rst1.EOF
        With xlApp.Sheets(Workbook_tab)
            For intFields = 0 To Field_Count - 1
                .cells(intRow, intColumn) = rst1.Fields(intFields)
                intColumn = intColumn + 1
            Next intFields
        End With
        intRow = intRow + 1
        intCells = intCells + 1
        intColumn = 1
        rst1.MoveNext
    Loop
 
    'Test HTML Tag Fix here
 
    'End HTML Tag Test Fix
 
    Set rst1 = Nothing
    Set db1 = Nothing
    'x = x + 1
    x = 0
 
End Function
 
Last edited:

Keevin77

Registered User.
Local time
Today, 05:05
Joined
May 2, 2014
Messages
13
Access Query Result
Complete6
Project Description
Test Project Phase 1 in progress


Excel export using access query above
<div class="ExternalClass8A13BE74CB1A417988BCA0FA2D0219DA"><p>​
Test Project Phase 1 in progress </p></div>
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:05
Joined
Feb 19, 2013
Messages
16,553
try

me.textfield=plaintext(me.textfield)
 

Keevin77

Registered User.
Local time
Today, 05:05
Joined
May 2, 2014
Messages
13
try

me.textfield=plaintext(me.textfield)

Thanks, my access query field is called ProjectDescription, I replaced textfield with ProjectDescription, and inserted that between 'Test HTML Tag Fix here 'End HTML Tag Test Fix.

I get error 'Invalid use of Me keyword' on PlainText(Me.ProjectDescription). I also tried replacing me with rst1 as thats my query's recordset.

any suggestions?
 

Keevin77

Registered User.
Local time
Today, 05:05
Joined
May 2, 2014
Messages
13
problem fixed.

I put plaintext( in front of the fieldname in the access query select statement.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:05
Joined
Feb 19, 2013
Messages
16,553
the principle is using the plaintext function

if the code is used in a form or report (which I had assumed since you showed vba code), then you would (or can) use me, although on closer inspection I see you open a recordset based on a query.

in a query would be something like

PTProjectDescription: plaintext(ProjectDescription)

or the SQL

plaintext(ProjectDescription) AS PTProjectDescription

which I would put in your queries referenced by the function.

Alternatively, within the function, I would change this line

.cells(intRow, intColumn) = rst1.Fields(intFields)
to

Code:
 iif rst1.Fields(intFields).name="ProjectDescription" then
     .cells(intRow, intColumn) = plaintext(rst1.Fields(intFields))
else
     .cells(intRow, intColumn) = rst1.Fields(intFields)
 end if
 

Keevin77

Registered User.
Local time
Today, 05:05
Joined
May 2, 2014
Messages
13
Thanks CJ, I have tried the last option and the too works. Appreciated the advice.
 

Users who are viewing this thread

Top Bottom