1

I am using a VBA sub which works inconsistently. The sub is based on a previous Stack Overflow thread answer I found which when it works is perfect. Copy Data from Excel to Notepad.

Sub CopyEventtoNotepad()

    'Dim rngData As Range
    Dim strData As String
    Dim strTempFile As String
    Dim strPath As String
    
strPath = Environ("USERPROFILE") & "\Desktop\HBT.txt"
    

Set Meeting = Worksheets("Patient").Range("CN21:CO55")
Set MeetingComments = Worksheets("Patient").Range("CN21:CO58")
Set Phone = Worksheets("Patient").Range("CS21:CT33")
Set PhoneComments = Worksheets("Patient").Range("CS21:CT36")
    
If Worksheets("Patient").Range("BB9").Value = 1 Then MeetingComments.Copy
If Worksheets("Patient").Range("BB9").Value = 2 Then Meeting.Copy
If Worksheets("Patient").Range("BB9").Value = 3 Then PhoneComments.Copy
If Worksheets("Patient").Range("BB9").Value = 4 Then Phone.Copy

    ' get the clipboard data
    ' magic code for is for early binding to MSForms.DataObject
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        strData = .GetText
    End With

    ' write to temp file
    strTempFile = strPath
    With CreateObject("Scripting.FileSystemObject")
        ' true to overwrite existing temp file
        .CreateTextFile(strTempFile, True).Write strData
    End With

    ' open notepad with tempfile
    Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide

End Sub

The runtime error occurs at the following line:

.CreateTextFile(strTempFile, True).Write strData

I have been trying to identify what the variable factor is that triggers the runtime error 5 for weeks. I can't find a pattern.

Thanks

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
p-we
  • 43
  • 5
  • 2
    Maybe don't copy and store the data from the range directly into `strData` instead? – findwindow Mar 30 '22 at 22:12
  • I follow your logic on this. I will try to amend the VB code to do just that – p-we Mar 30 '22 at 23:05
  • I was on my way to code in this direction but I think the answer from @VBasic2008 below is doing what you are recommending here. Has not solved it. It's beginning to look like there is an influence outside of the sub itself triggering this inconsistency – p-we Mar 31 '22 at 20:28

1 Answers1

1

Copy Range to String

  • The other day, someone mentioned in the comments that Dao is not working supposedly due to a Windows or Office update.
  • Anyways, here's a simple workaround that may serve you well for such small ranges.
Option Explicit

Sub CopyEventToNotepad()

    Dim rgAddresses As Variant
    rgAddresses = VBA.Array("CN21:CO55", "CN21:CO58", "CS21:CT33", "CS21:CT36")

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Patient")
    Dim rg As Range: Set rg = ws.Range(rgAddresses(ws.Range("BB9").Value - 1))

    Dim FilePath As String
    FilePath = Environ("USERPROFILE") & "\Desktop\HBT.txt"
    
    Dim RangeString As String: RangeString = StringRangeRows(rg)

    With CreateObject("Scripting.FileSystemObject")
        .CreateTextFile(FilePath, True).Write RangeString
    End With

    Shell "cmd /c ""notepad.exe """ & FilePath & """", vbHide

End Sub

Function StringRangeRows( _
    ByVal rg As Range, _
    Optional ByVal CellDelimiter As String = vbTab, _
    Optional ByVal LineDelimiter As String = vbLf) _
As String
    
    Dim cLen As Long: cLen = Len(CellDelimiter)
    
    Dim arg As Range, rrg As Range, rCell As Range, RangeString As String
    
    For Each arg In rg.Areas
        For Each rrg In arg.Rows
            For Each rCell In rrg.Cells
                RangeString = RangeString & CStr(rCell.Value) & CellDelimiter
            Next rCell
            RangeString = Left(RangeString, Len(RangeString) - cLen) _
                & LineDelimiter
        Next rrg
    Next arg
    
    StringRangeRows = Left(RangeString, Len(RangeString) - Len(LineDelimiter))

End Function
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Wow, thanks for that. I noticed you also added an alignment part for the exported text. I have aligned the 2nd column of text using Excel itself. Unfortunately what I think is the same error is still there at: .CreateTextFile(FilePath, True).Write RangeString` BTW, I put ThisWorkbook as the Workbook. I don't think that is causing this – p-we Mar 31 '22 at 06:13
  • If by *"alignment part"* you mean `vbTab`, you can change it to e.g. a comma and a space with `RangeString = StringRangeRows(rg, ", ")` or to an empty string with `RangeString = StringRangeRows(rg, "")`. it works correctly on my end. Maybe try changing the path to e.g. `C:\Text\HBT.txt` and see what happens. My `DeskTop` path is `Environ("USERPROFILE") & "\OneDrive\DeskTop"`. – VBasic2008 Mar 31 '22 at 06:30
  • I tried, 1) several explicit paths 2) another source range of dummy text with no blank cells or references to other cells with your code but still getting runtime #5 at the .CreateTextFile command. And it works for you consistently. I can't imagine any other part of the workbook can influence this, or can it? Is there possibly a global setting that is causing this? For example, I noticed you have "option Explicit" enabled which I don't here. I can't use this as is because it effects some other subs that are working fine. Setting dims in VBA to work well is not so simple I have discovered. – p-we Mar 31 '22 at 20:07
  • I tried putting your code into it's own module using Option Explicit but still same issue. Just trying things at this stage not really knowing what I'm doing. – p-we Mar 31 '22 at 20:14