BOM EXCEL - VBA Macro to transfer content of a cell to a *.lst file, for OrCAD processing

Attribute VB_Name = "Module1"
' This macro is provided to you free of charge by Andak Consulting, Jun 2025.
' No warranty is provided, and Andak Consulting cannot be held liable for any damage caused by the use of this script, in any form.
' You may redistribute this script, and you may even use it for commercial purposes.
' The only requirement is to keep the header of this file.

' usage
' we use this with BOM files, when manually soldering components
' it comes in handy to pass the selected cell content to OrCAD PCB/Allegro for processing (we use add color in shadow mode).
' open your BOM in EXCEL. The refdes column should contain comma separated values like this: R8,R11,R13,R15,R24,R29
' select one cell and run this macro
' the macro creates a file under your %HOME%\pcbenv folder, named excelTransfer.lst
' the macro replaces the comma with white spaces and also removed the background color of the excel line (we use this colors in our process)
' we recommend customizing your ribbon to quickly acces this the macro command. there's plenty documentation on the web on how to do this and preferably save this under personal.xlsb
' use the generated file (lst) in OrCAD PCB/ Allegro. We recommend either recoding a script or writing a SKILL, whichever comes in handy for you.

' rev history
' 1.0, original release, Andak Consulting, 02 July 2025

Sub ExportSelectedCellToFile()
    Dim cellContent As String
    Dim modifiedContent As String
    Dim filePath As String
    Dim homePath As String

    ' Check if a single cell is selected
    If TypeName(Selection) <> "Range" Or Selection.Cells.Count > 1 Then
        MsgBox "Please select a single cell.", vbExclamation
        Exit Sub
    End If

    ' Get the HOME environment variable
    homePath = Environ("HOME")
    If homePath = "" Then
        MsgBox "The HOME environment variable is not set.", vbCritical
        Exit Sub
    End If

    ' Construct the full file path
    filePath = homePath & "\pcbenv\excelTransfer.lst" ' You can change the filename here

    ' Read the content from the selected cell
    cellContent = Selection.Value

    ' Replace commas with spaces
    modifiedContent = Replace(cellContent, ",", " ")

    ' Write to file, overwriting existing content
    Dim fileNum As Integer
    fileNum = FreeFile
    Open filePath For Output As #fileNum
    Print #fileNum, modifiedContent
    Close #fileNum

    ' Remove fill color from the entire row of the selected cell
    selectedRow = Selection.Row
    Rows(selectedRow).Interior.ColorIndex = xlNone
End Sub