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.
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: