Link to home
Start Free TrialLog in
Avatar of Elmo Erasmus
Elmo ErasmusFlag for Namibia

asked on

Need help inserting code to fix a excel bug

Hi All
I have some code that runs through a excel workbook and copies certain bits to a word document. This all works fine but it will randomly add two ?? to one or more places it was supposed to copy the information to. This does not always happen and it is not always in the same place. Trying to find what the cause is i came across this article
https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard 

The problem i have now is that i'm not sure of how to add this to my code can someone please assist me with this see code below
Public Declare Function CountClipboardFormats Lib "user32" () As Long

Dim appWd As Word.Application
Dim wdFind As Object
Dim ClipEmpty As New MSForms.DataObject
Dim ClipT As String

Function IsClipboardEmpty() As Boolean
    IsClipboardEmpty = (CountClipboardFormats() = 0)
End Function

Sub CheckClipBrd()

If IsClipboardEmpty() = True Then
ClipEmpty.PutInClipboard
End If
End Sub

Sub FormatPaste()

    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    Call CheckClipBrd
    appWd.Selection.Paste
    CutCopyMode = False

End Sub

Sub NoFormatPaste()
On Error GoTo myLine

    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    Call CheckClipBrd
myline2:
    appWd.Selection.PasteSpecial DataType:=wdPasteText
    CutCopyMode = False
Exit Sub
myLine:
If Err.Number = 4198 Then
    GoTo myline2
End If
End Sub

Sub CopyDatatoWord()

Dim docWD As Word.Document
Dim rngTable As Range
Dim cell As Range
Dim rngChart As Range
Dim rngPChart As Range
Dim PCell As Range
Dim CCell As Range
Dim sh As Worksheet
Dim cht As ChartObject
Dim x
Dim y
Dim strPath As String
Dim sNow As String
Dim strSPath As String

strPath = Sheets("Sheet1").Range("A2").Value
strSPath = Sheets("Sheet1").Range("A4").Value
Application.ScreenUpdating = False


    Set appWd = CreateObject("Word.Application")
    appWd.Visible = True

'Need to change this to look at a cell value

    Set docWD = appWd.Documents.Open(strPath)


    Set wdFind = appWd.Selection.Find
    ClipT = "  "
    ClipEmpty.SetText ClipT
    
Set rngTable = Sheets("Sheet1").Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)
'MsgBox Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row

    For Each cell In rngTable
        If cell.Value = "" Then
            GoTo myExit
        Else
        y = 1
            Application.Goto Reference:=cell.Value
            Selection.Copy
            wdFind.Text = cell.Value
            
            Dim MyDoc As String, txt As String, t As String
            MyDoc = docWD.Range.Text
            txt = cell.Value
            t = Replace(MyDoc, txt, "")
            x = (Len(MyDoc) - Len(t)) / Len(txt)
 Do While x >= y
            Call NoFormatPaste
            y = y + 1
Loop
        End If
    Next cell
myExit:

'This does non Pivot type charts
Set rngChart = Sheets("Sheet1").Range("F2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
    For Each CCell In rngChart
        If CCell.Value = "" Then
            GoTo MyExit2
        Else
            Application.Goto Reference:=CCell.Value
            Selection.Copy
            wdFind.Text = CCell.Value
            
            Call FormatPaste
        End If
    Next CCell

MyExit2:

Set rngPChart = Sheets("Sheet1").Range("H2:H" & Cells(Rows.Count, 8).End(xlUp).Row)

For Each PCell In rngPChart
    If PCell.Value = "" Then
        GoTo MyExit3
    Else
        For Each sht In ActiveWorkbook.Worksheets
            For Each cht In sht.ChartObjects
                If PCell.Value = cht.Name Then
                    cht.Activate
                    cht.Copy
                    wdFind.Text = PCell.Value
                    Call FormatPaste
                End If
            Next cht
        Next sht
    End If
Next PCell

MyExit3:
sNow = Format((Now), "yyyy_mm_dd_hh_mm_ss")
'docWD.SaveAs ("C:\Users\ElmoJ\Documents\Aaron\From Client\Test2.docx")
docWD.SaveAs strSPath & "\Output_" & sNow & ".docx"
    'appWD.Quit

Set appWd = Nothing
Set docWD = Nothing
Set appXL = Nothing
Set wbXL = Nothing
Set wdFind = Nothing
Set rngTable = Nothing
Set rngChart = Nothing
Set rngPChart = Nothing
 ClipT = Empty
Set ClipEmpty = Nothing
Application.ScreenUpdating = True

End Sub

Open in new window

Thanks in advance
Elmo
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Try using this function written by Rick Rothstein of http://www.mrexcel.com to remove non printable characters from you string.
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

Open in new window

Avatar of Elmo Erasmus

ASKER

Hi
It is not that the characters are unprintable. I will run the code and might copy paste everything correctly. The next time i run exactly the same thing on the same data there might be one or more instances where instead of the data the are two small question marks. These are completely random.
Those non-printable characters have nothing to do with your data, it is a clipboard quirk. When you paste the string copied to the clipboard, if you remove the non-printable characters, you probably won't get the question marks.
Ok i will give it a try but why would it paste correctly once and then paste the ?? when i run it again?
So if i understand you correctly these unprintable characters are added to the data in the clipboard. How will i then apply the CleanTrim to the data if it is in the clipboard?
I would do it like this:
Public Declare Function CountClipboardFormats Lib "user32" () As Long

Public Function GetClipBoardText() As Variant
    Dim DataObj As Object
    Set DataObj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    If (CountClipboardFormats() = 0) = False Then
        DataObj.GetFromClipboard
        GetClipBoardText = CleanTrim(DataObj.GetText(1))
    End If
End Function

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
    Dim X As Long, CodesToClean As Variant
    CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                         21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
    If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
        If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    CleanTrim = WorksheetFunction.Trim(S)
End Function

Sub NoFormatPaste()
    On Error GoTo myLine

    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    Call CheckClipBrd
myline2:
    GetClipBoardText
    CutCopyMode = False
    Exit Sub
myLine:
    If Err.Number = 4198 Then
        GoTo myline2
    End If
End Sub

Open in new window

Just to make sure i would still need to paste after GetClipBoardText. Is this correct?
so something like this
Sub NoFormatPaste()
    On Error GoTo myLine

    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    Call CheckClipBrd
myline2:
    GetClipBoardText
    appWd.Selection.PasteSpecial DataType:=wdPasteText
    CutCopyMode = False
    Exit Sub
myLine:
    If Err.Number = 4198 Then
        GoTo myline2
    End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial