submain macro

Sub MainMacro()

    Dim bulkData As String
    Dim lines() As String
    Dim i As Long

    ' Get large input text from the user
    bulkData = GetLargeInput("Paste your bulk data below:")

    ' Split data by line breaks
    lines = Split(bulkData, vbCrLf)

    ' Clear existing data on the active sheet
    Cells.Clear

    ' Insert each line into a new row in column A
    For i = LBound(lines) To UBound(lines)
        Cells(i + 1, 1).Value = lines(i)
    Next i

    MsgBox "All data has been successfully added to the sheet!" & vbCrLf & _
           "Total lines: " & (UBound(lines) - LBound(lines) + 1), vbInformation

End Sub


'---------------------------------------
' Function to get large input via Notepad
'---------------------------------------
Function GetLargeInput(prompt As String) As String
    Dim tempFile As String
    Dim fileNum As Integer
    Dim userInput As String

    ' Create a temporary text file in the user's temp folder
    tempFile = Environ("TEMP") & "\LargeInput.txt"

    ' Open Notepad with the temp file
    Shell "notepad.exe " & tempFile, vbNormalFocus

    MsgBox prompt & vbCrLf & vbCrLf & _
           "Paste your text in Notepad, save (Ctrl+S), close Notepad, then click OK here.", vbInformation

    ' Wait until user closes Notepad
    Do While FileLocked(tempFile)
        DoEvents
    Loop

    ' Read the file content
    fileNum = FreeFile
    Open tempFile For Input As #fileNum
    userInput = Input$(LOF(fileNum), #fileNum)
    Close #fileNum

    ' Delete the temp file
    Kill tempFile

    ' Return the data
    GetLargeInput = userInput

End Function


'---------------------------------------
' Helper function to check if a file is locked
'---------------------------------------
Function FileLocked(filename As String) As Boolean
    On Error Resume Next
    Dim fileNum As Integer
    fileNum = FreeFile
    Open filename For Binary Access Read Write Lock Read Write As #fileNum
    Close fileNum
    If Err.Number <> 0 Then
        FileLocked = True
        Err.Clear
    Else
        FileLocked = False
    End If
    On Error GoTo 0
End Function

Comments

Post a Comment

Popular posts from this blog

fixed

Sub Select_Bulk_Data_InputBox_Final2()     Dim sheetName As String     sheetName = "Data 1" ' <<-- Change the sheet name here only (exact name)     Dim wb As Workbook     Dim ws As Worksheet     Dim rng As Range     Dim inputData As String     Dim arrInput As Variant     Dim searchValues() As String     Dim cleanedValues() As String     Dim removeChars As String     Dim arrRemove As Variant     Dim removeProvided As Boolean     Dim colInput As String     Dim arrCols As Variant     Dim validCols As Collection     Dim foundSheet As Worksheet, notFoundSheet As Worksheet     Dim i As Long, j As Long, k As Long     Dim cell As Range     Dim cellValue As String     Dim lastCol As Long     Dim foundCount As Long, notFoundCount As Long     Dim isFound As Boolean   ...
Read more

fixed 😃😃😃

Sub Select_Bulk_Data_InputBox_Final3()     Dim sheetName As String     sheetName = "Data 1" ' <<-- Change the sheet name here only (exact name)          Dim wb As Workbook     Dim ws As Worksheet     Dim rng As Range     Dim inputData As String     Dim arrInput As Variant     Dim searchValues() As String     Dim cleanedValues() As String     Dim removeChars As String     Dim arrRemove As Variant     Dim removeProvided As Boolean     Dim colInput As String     Dim arrCols As Variant     Dim validCols As Collection     Dim resultSheet As Worksheet     Dim i As Long, j As Long, k As Long     Dim cell As Range     Dim cellValue As String     Dim lastCol As Long     Dim foundCount As Long     Dim isFound As Boolean     Dim msgText As String ...
Read more

babyGirl

 Sub Select_Bulk_Data_InputBox()     Dim ws As Worksheet     Dim rng As Range     Dim cell As Range     Dim inputData As String     Dim searchValues() As String     Dim i As Long, j As Long     Dim cellValue As String     Dim selRange As Range     Dim lastCol As Long     Dim searchText As String     Dim colInput As String     Dim colNumbers() As String     Dim colNum As Long     Dim validCols As Collection     Dim removeChars As String     Dim removeList() As String     Dim k As Long     ' Sheet reference     Set ws = ThisWorkbook.Sheets("Data 1")     Set rng = ws.UsedRange          ' Clear previous selection     ws.Cells(1, 1).Select          ' Step 1: Get bulk data from user     inputData = InputBox("Paste your bul...
Read more