0

Essentially I am trying use VBA to copy data from Excel and paste values in a new notepad file. I was able to create a successful macro using sendkeys, but am trying to avoid this I know it is not the most reliable. I have tried using createobject to create and write to temp files but no luck. I am getting a "permission denied" error. Once the Notepad file is generated, I do not want to save it down, just leave it open for the user to review the data and they can save as they need. Any suggestions on alternatives? Code I have been trying is below.

Application.ScreenUpdating = False
Dim formulasheet As Worksheet
Dim copysheet As Worksheet
Dim num As Integer
Dim valuecolumn As Range, cell As Object
Dim copycolumn As Range
Dim i As Range
Dim strTempFile As String
Dim strData As String

num = 0
Set formulasheet = ActiveWorkbook.Sheets("Template")
Set copysheet = ActiveWorkbook.Sheets("Copy")
Set valuecolumn = formulasheet.Range("B:B")
Set copycolumn = formulasheet.Range("A:A")

formulasheet.Visible = xlSheetVisible
copysheet.Visible = xlSheetVisible
copysheet.Cells.Clear
formulasheet.Select
For Each i In valuecolumn
  If i.Value > 0 Then
    i.Offset(0, -1).Copy
    copysheet.Select
    copysheet.Range("A1").End(xlUp).Offset(num, 0).PasteSpecial Paste:=xlPasteValues
    num = num + 1
  End If
Next i

copysheet.Cells.WrapText = False
If copysheet.Range("A1") = "" Then
  MsgBox "No transaction amounts, please review."
  copysheet.Visible = xlSheetHidden
  formulasheet.Visible = xlSheetHidden
  Exit Sub
Else
  copysheet.Select
  strData = copysheet.Range("A:A").SpecialCells(xlCellTypeConstants).Copy
  strTempFile = "C:\temp.txt"
  
  With CreateObject("Scripting.FileSystemObject")
    .CreateTextFile(strTempFile, False).Write strData
  End With
  Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide
End If

SendKeys "{NUMLOCK}", True
copysheet.Visible = xlSheetHidden
formulasheet.Visible = xlSheetHidden
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
arisophia
  • 61
  • 4
  • so you're trying to create a text file? see [How to create and write to a txt file using VBA](https://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba) and then you can open it in Notepad for the user with something like `Shell "notepad.exe C:\yourpath\filename.txt", vbNormalFocus` – ashleedawg May 05 '22 at 18:18
  • Also, remember to set the [`ScreenUpdating` property](https://learn.microsoft.com/office/vba/api/excel.application.screenupdating) back to True when your macro ends. – ashleedawg May 05 '22 at 18:28
  • @ashleedawg - `ScreenUpdating` isn't sticky - it will reset itself once the code finishes. – Tim Williams May 05 '22 at 20:12

0 Answers0