Question

I need this problem to be solved in Excel VBA code.

Need full code in VBA. Please solve 1st Task : '5 or 0'. Please help me urgently with proper answer and coding solutions which I can run in Excel/vba. Thanks

Course: Programming languages Second laboratory work File and string processing Create a program that processes text file according to task variant. Requirements: 1. Create text file (use notepad) with some lines of data according to task variant. 2. Ask the user to select a text file (and variable for task nr. 1 and 5). 3. Output processed data to Excel row by row. Task variants Last digit of number of student card Task 5 or 0 Given an integer K and a text file, remove K leading characters from each line of the file. If the length of some line is less than k then remove all characters from the line. 6 or 1 Given a text file, replace all its Latin capital letters with the corresponding small letters, and all Latin small letters with the capital ones. 7 or 2 Given a text file, replace its successive blank characters with the single blank character. 8 or 3 Given a text file, insert after each dot one blank character. 9 or 4 Given a string S and a text file, find the specified string and, if such string exists in file, return its position (line number and position in this line).

Answer 1

For the 1st task Excel VBA code, please open a new Excel workbook and press "ALt+F11" to open Visual Basic Editor. In the windows insert a new module as shown below:

Microsoft Visual Basic for Applications - Book1 Eile Edit View Insert Format Debug Run Tools Add-Ins Window Help Project - VBAProject X VBAProject (Book1 Microsoft Excel Ot Sheet1 ThisWor e View Code 53 View Object Toggle Folders VBAProject Properties... Insert Import File... Export File... Remove Sheet1... Print... Properties - Sheet1 Dockable Sheet1 Worksheet Alphabetic Categorized (Name) Sheet1 DisplayPageBre False DisplayRightTo False e * UserForm Module Class Module Hide

Create/define a new sub routine in the module where we'll write our code as shown below:

Microsoft Visual Basic for Applications - Book1 - [Module 1 (Code)] Eile Edit View Insert Format Debug Run Tools Add-ins Window Help E U $ Ln 3, Col 5 Project - VBAProject X e neral) Sub trim_text() B VBAProject (Bookl) OG Microsoft Excel Objects Sheet1 (Sheet1) End Sub ThisWorkbook * Module1 Properties - Module 1 Module1 Module Alphabetic Categorized (Name) Module1

Add the VBA code for the sub routine:

Sub trim_text()
  
'Define variables
Dim K As Integer, textFilePath As String, textline As String, i As Long
'i is the mapping of row number for output, we'll start with row 1
i = 1
'Get value of K from user by a prompt
K = InputBox("Please specify value of K:", "Value of K")
'Get file path of text file from user by a prompt
textFilePath = InputBox("Please specify text file path:", "Text File Path")
'Open textFilePath for input
Open textFilePath For Input As #1
'Run a Do loop till the end of lines in text file, basically read complete file line by line
Do Until EOF(1)
'Read one line and store in variable textline
Line Input #1, textline
'If condition to check if length of leading characters to be removed is greater than or equal to K
If (Len(textline) - K) >= 0 Then
'Store the output in Column A Row i
Range("A" + CStr(i)).Value = Right(textline, Len(textline) - K)
Else
'Store the output in Column A Row i
Range("A" + CStr(i)).Value = ""
End If
'Increment row count to store next value in next row on next iteration
i = i + 1
'End of loop code
Loop
'Close text file
Close #1
  
End Sub

Here's the screenshot:

Microsoft Visual Basic for Applications - Book1 - [Module 1 (Code)] Eile Edit View Insert Format Debug Run Tools Add-Ins Window Help 2 - % K * * Ln 33, Col 1 E B A O Bo to you Project - VBAProject X (General) trim_text Sub trim_text() VBAProject (Booki) 'Define variables Microsoft Excel Objects Dim K As Integer, textFilePath As String, textline As String, i As Long Sheet1 (Sheeti) 'i is the mapping of row number for output, we'll start with row 1 ThisWorkbook i = 1 Ó- Modules 'Get value of K from user by a prompt Modulei K = InputBox("Please specify value of K:", "Value of K") 'Get file path of text file from user by a prompt textFilePath = InputBox("Please specify text file path:", "Text File Path") 'Open textFilePath for input Open textFilePath For Input As #1 'Run a Do loop till the end of lines in text file, basically read complete file line by line Do Until EOF (1) 'Read one line and store in variable textline Line Input #1, textline Properties - Module 1 'If condition to check if length of leading characters to be removed is greater than or equal to K If (Len (textline) - K) >= 0 Then Module1 Module 'Store the output in Column A Row i Alphabetic Categorized Range ("A" + CStr(i)).Value = Right (textline, Len(textline) - K) (Name) Module1 Else 'Store the output in Column A Row i Range("A" + cstr(i)).Value = "" End If 'Increment row count to store next value in next row on next iteration i = i + 1 'End of loop code Loop 'Close text file Close #1 End Sub

Now we can run this subroutine to test our code. We can execute it by assigning it to a shortcut in Excel or for testing directly from VBE by pressing "F5" or Run button:

(General) Sub trim_text()

On execution it'll prompt user for value of K (leading characters to remove from each line) and full path of text file whose data will be processed. Below is my sample text file:

B Sample Text.txt X Enim curae vehicula non lobortis donec iaculis pharetra vestibulum himenaeos commodo praesent nulla 2 Etiam tellus purus 3 curabitur dolor tellus aliquet 4 Ultrices nam 5 iaculis tristique congue dolor ligula Donec curabitur nam torquent risus taciti, gravida condimentum congue gravida justo, bibendum habitant purus sagittis 8 Per suspendisse curabitur aptent porta tellus bibendum vestibulum ut donec tempor netus 9 Cubilia luctus sollicitudin gravida imperdiet dolor himenaeos quis 10 Est aenean aliquam tristique curae 11 donec vestibulum 12 Fermentum pulvinar fusce euismod 14 porta eros egestas bibendum tempus 15 ipsum orci vulputate odio pharetra

SampleText.txt

Enim curae vehicula non lobortis donec iaculis pharetra vestibulum himenaeos commodo praesent nulla
Etiam tellus purus
curabitur dolor tellus aliquet
Ultrices nam
iaculis tristique congue dolor ligula
Donec curabitur nam torquent risus
taciti, gravida condimentum congue gravida justo, bibendum habitant purus sagittis
Per suspendisse curabitur aptent porta tellus bibendum vestibulum ut donec tempor netus
Cubilia luctus sollicitudin gravida imperdiet dolor himenaeos quis
Est aenean aliquam tristique curae
donec vestibulum
Fermentum pulvinar fusce
euismod
porta eros egestas bibendum tempus
ipsum orci vulputate odio pharetra

On execution of the subroutine, you'll be shown prompts as shown in below screenshots:

First specify the value of K.

General EL Wrap Text Merge & Center - $ - % Insert Delete Fo - Conditional Format as Formatting Table Cell Styles Alignment Number Styles Cells G H I K L M N O P Q J Value of K Please specify value of K: OK Cancel

Specify the text file path in next prompt (it'll need full path of the file, no quotes enclosing the path):

1 2 E 3 EX 2 E Insert Delete Format a Wrap Text General s Merge & Center - $ - % , Alignment al Number OO Conditional Format as Cell Formatting Table Styles ol Styles Cells G H I J K L M N O P Q Text File Path Х Please specify text file path: ок Cancel C:\Users\abhi1578\Desktop\Sample Text.txt

Once you click OK, it'll read the text file line by line and remove leading K characters from each line. If number of characters in a line is less than K, all characters will be removed. Then the trimmed line will be copied to Excel sheet row by row. Here's the output of my code for SampleText.txt file shown earlier:

A1 X ✓ fica non lobortis donec iaculis pharetra vestibulum himenaeos commodo praesent nulla A B C D E F G H 1 la non lobortis donec iaculis pharetra vestibulum himenaeos commodo praesent nulla 2 3 llus aliquet 5 congue dolor ligula 6 m torquent risus 7 ndimentum congue gravida justo, bibendum habitant purus sagittis 8 rabitur aptent porta tellus bibendum vestibulum ut donec tempor netus 9 licitudin gravida imperdiet dolor himenaeos quis 10 tristique curae 11 12 fusce 13 14 bibendum tempus 15 te odio pharetra 16 17

Please do note that this code doesn't implement any error checks, so incorrect input like specifying some string instead of a number for K or specifying incorrect file path will lead to run-time error. You can always re-run the code with correct inputs.