| "I need to write a text file into one row of my Excel spreadsheet, cell by cell, 20 characters at a time. It's urgent. Can you help?" -- Kumar |
Kumar,
It's sort of a strange request. But it sounds like fun. And it demonstrates how we can read text files into Excel using VBA.
By the way, the following code illustrates the various points I discussed in Corporate VBA Standards For Excel Users Who Program.
To begin, create a new workbook with two sheets. Name one sheet Control and the other Target.
In the Control worksheet, enter the text shown in column A:
Assign the range names shown in column A to cells in column B. To do so, select the range A4:B9. Choose Insert, Name, Create. In the Create Names dialog, ensure that only Left Column is checked. Then choose OK.
Enter the path and name for your text file. (I used File Explorer to navigate to its directory and then copied the path text from the Address bar.) Enter the other information shown.
Leave the Target sheet blank.
Press Alt+F11 to bring up the Visual Basic editor. To create a new module, right-click your workbook's name in the editor's project window and choose Insert, Module.
Then copy and paste the following code to your module.
Example
1
''Require that all variables be declared
2Option Explicit
3
4''======================================================
5'' Program: ParseText
6'' Desc: Reads a text file into a variable then
7'' writes it into a row, n chars at a time
8'' Called by: user
9'' Call:
10'' Arguments:
11'' Comments: Written quickly. No error-checking.
12'' Changes----------------------------------------------
13'' Date Programmer Change
14'' 6/14/06 Charley Kyd Written
15''======================================================
16Sub ParseText()Sub ParseText()
17
Dim sText As String, sFile As String
18
19 ''Get the full path to the source file
20 With ThisWorkbook
21 sFile = .Names("SourcePath").RefersToRange
22 If Left(sFile, 1) <> "\" Then sFile = sFile & "\"
23 sFile = sFile & .Names("SourceFile").RefersToRange
24 End With
25
26 ''Get the full text string from the text file
27 sText = GetText(sFile)
28
29 ''Remove all nonprintable characters from the text
30 ''Comment out if the characters are wanted
31 sText = Excel.WorksheetFunction.Clean(sText)
32
33 ''Write to the workbook
34 WriteToSheet sText
35
End Sub
36
37''======================================================
38'' Program: GetText
39'' Desc: Read a text file into a string and then
40'' return the string
41'' Called by: ParseText
42'' Call: GetText(sFile)
43'' Arguments: sFile--The full path to the text file
44'' Comments:
45'' Changes----------------------------------------------
46'' Date Programmer Change
47'' 6/14/06 Charley Kyd Written
48''======================================================
49Function GetText()Function GetText(sFile As String) As String
50 Dim nSourceFile As Integer, sText As String
51
52 ''Close any open text files
53 Close
54
55 ''Get the number of the next free text file
56 nSourceFile = FreeFile
57
58 ''Write the entire file to sText
59 Open sFile For Input As #nSourceFile
60 sText = Input$(LOF(1), 1)
61 Close
62
63 GetText = sText
64End Function
65
66''======================================================
67'' Program: WriteToSheet
68'' Desc: Writes a text string to one row of a
69'' worksheet, n characters per column
70'' Called by: ParseText
71'' Call: WriteToSheet sText
72'' Arguments: sText--String with text from text file
73'' Comments: This routine will throw an error if the
74'' text is too long to fit within 256 columns
75'' Changes----------------------------------------------
76'' Date Programmer Change
77'' 6/14/06 Charley Kyd Written
78''======================================================
79Sub WriteToSheet()Sub WriteToSheet(sText As String)
80 Dim sTgtSheet As String, nTgtRow As Integer
81 Dim nColCount As Integer, sChunk As String
82 Dim nIncrement As Integer, rngRef As Range
83
84 ''Get the controlling variables
85 With ThisWorkbook
86 sTgtSheet = .Names("TargetSheet").RefersToRange
87 nTgtRow = .Names("TargetRow").RefersToRange
88 nIncrement = .Names("Increment").RefersToRange
89
90 Set rngRef = Worksheets(sTgtSheet).Cells(nTgtRow, 1)
91 End With
92
93 ''Erase any previous entries
94 rngRef.EntireRow.ClearContents
95
96 ''Initialize the column counter
97 nColCount = 0
98
99 ''Loop thru the string, grabbing text of specified
100 ''length, writing the text to the spreadsheet
101 Do
102 ''Increment the column count
103 nColCount = nColCount + 1
104 ''Grab the current chunk of text
105 sChunk = Mid$(sText, 1 + (nColCount - 1) * _
106 nIncrement, nIncrement)
107 ''Write it to the spreadsheet
108 rngRef.Cells(1, nColCount) = sChunk
109
110 ''Stop after writing a partial chunk
111 Loop Until Len(sChunk) < nIncrement
112End Sub
113
Finally, set up a button in your Control sheet to run the macro easily. To do so, first activate your Control sheet. Right-click any toolbar. Click on Forms if its not already checked.
Click on the Button icon in the Forms toolbar. Doing so turns your pointer into a cross. Use the cross to draw the outline of a button on your worksheet.
When you release your left mouse button, Excel draws the button and launches the Assign Macro dialog. Choose the ParseText macro, then choose OK. Select the text "Button 1" in the button and then type any text you want, like "Parse Text". Then click on any cell to deselect the button.
Now, when you click on the button, Excel should run your macro and write your text to your Target worksheet.
