5 Attachment(s)
Text Files , Excel Files , Excel VBA Array
In Avinash’s world are two types of files. Only two types , Text files and Excel files. There are no other types of file. There will probably never be any other types of files.
All Files , text files, excel files and all other file types , are held in a computer as just a long string of Text. Even an Excel File is just a long piece of text inside a computer. But it is hard to understand. The software that is Excel decodes the long text and tries to make it appear in values and formats that we can see in a worksheet
Excel Files ( Excel Worksheet spreadsheet )
.xls .xlsm .xlsx .xlsb
An Excel file is very complicated. It can have values and lots of cell formatting.
Because of all cell formatting, it can be very slow in working. Excel is not an efficient thing to use if you only have values
We can open an Excel File manually, using Excel or Notepad
ExcelFileOpenInNotepad.JPG : https://imgur.com/bdym9Lc ExcelFileOpenInExcel.JPG : https://imgur.com/gwOtksS
Attachment 3298Attachment 3296
In Excel it may looks like this
ExcelFile.JPG : https://imgur.com/8xaZihR
Attachment 3297
_____ Workbook: ExcelFile.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
|---|
| 1 |
A |
B |
|
| 2 |
C |
D |
|
| 3 |
|
|
|
In Notepad it looks like this:
ExcelFileInNotepad.JPG : https://imgur.com/wHTPbO6
Attachment 3295
Code:
PK ! U6»+w ( Ø [Content_Types].xml ¢Ô ( ÌTËNÃ0 ¼#ñ ‘¯(q[ B¨i <ŽP ø co «ŽmyÝÒþ=›¤* Ѩ¥=pI %;3žÌp¼¬L²€€ÚÙœõ³ KÀJ§´æìíõ1½a Fa•0ÎBÎV€l<:? ¾®<`BÓ sVÆèo9GYB%0s ,½)\¨D¤Ç0å^È™˜ -ôz×\: ÁÆ4Ö l4|& A+H&"Ä'Q _ Úk?#<–ܵƒ5w΄÷FK I9_Xõƒ5uE¡%('ç qe ØEÂw b\ À£©Ð
K€X™¬ Ý0ßC!æ&& Kr 5=€ÁÃŽ¶63£ÉæøXj
ÝÞu{òáÂìݹ٩]©ÝÉ*¡íF÷Ž Ôü•È›ÛÕ‰…lñ»tPŠ&Áy䔹£ù¡^½ •z‚„ 5lw•§ƒ£5|è^ Ð ¹ýn½‹Ë¢ãøvø›¬XŠ ê% jË“×ÆWì½²)]€Ã ²é’zú—Dò¦çGŸ ÿÿ PK ! µU0#õ L
Î _rels/.rels ¢Ê (
That text above of a simple Excel File is very complicated because it has all the information needed by Excel to make all the cells and formatting
Making an Excel File with Excel VBA
We can make that Excel File using Excel VBA
Code:
Sub MakeExcelFile()
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)
Let Ws.Range("A1") = "A": Let Ws.Range("B1") = "B"
Let Ws.Range("A2") = "C": Let Ws.Range("B2") = "D"
Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFile.xlsx"
End Sub
We usually open Excel files with Excel. So that is why the files with the extensions of .xls .xlsm .xlsx .xlsb are called Excel Files. Such files were designed to be opened in Excel
If we are using an Excel file to store simple data values, then the values are usually divided up so that when opened in Excel the data is shown in cells in rows and columns
Text Files
.csv .txt
Text files are very simple. They only have values and sometimes , if it is being used to store data values, it may have separators**. ( Sometimes we call the seperators delimiters ).
A B
C D
A,B
C,D
We usually open text files with a text editor. For example Notepad.
TextFileOpenInNotepad.JPG : https://imgur.com/zzRAVIN
Attachment 3299
Because Text files are not complicated, we can see them easily in Notepad. Because we sometimes open files with the extension of .csv .txt in Notepad , they may be called Notepad files, and sometimes files with the extension of .csv may be called a comma separated values text values file or “csv file”. But really they are both Text files
**If we want to store simple data values in a text file, then we have the problem that we have no way to make the data appear in cells, because a text file has no cell information and also no other formatting information.
So we typically separate data on a line with something like, _ ; , vbTab | _ etc…
A Line is separated from the next line by an “invisible” character which tells a computer to make a new line, for example
vbCr & vbLf
Make a Text file using Excel VBA
We can make a text file using Excel VBA
Code:
Sub MakeTextFile()
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open ThisWorkbook.Path & "\" & "TextFile.txt" For Output As #FileNum ' Will be made if not there
Print #FileNum, "A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D"
Close #FileNum
End Sub
We can try to .Open a text file in Excel. Excel will try to show us the values from it in cells. It may work. It may not work. There will always be problems.
But we may want to see the values in Excel
Because there are always problems .Opening a text file in Excel, we import the values into an Excel worksheet instead
The text file made in that last macro , TextFile.txt , can be seen in Notepad to look like this:
TextFile_txtInNotepad.JPG : https://imgur.com/0B2BQpK
( We can represent that file as a simple string in VBA coding, thus:
"A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D"
( We might sometimes call this a Tab separated values or Tab delimited values text file ) )
The following macro is the best way to put the values from that text file into a worksheet. This may typically be called importing a text file into Excel. It does not convert a text file to an Excel File.
Code:
Sub ImportTextFileValuesIntoExcelWorksheet()
Rem 1 Add a workbook to display the values from a Tab delimited values text file
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)
Rem 2 Put the entire text file into a single string, TotalFile
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & "\" & "TextFile.txt" '
Open PathAndFileName For Binary As #FileNum ' Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum ' I need to do this, or there may be problems with my computer as I have an open route which may interact badly with something else
If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Sometimes an extra line seperator gets added, so I remove it if that is the case
Rem 3 Loop through the lines of the text file and paste each line to a row in the worksheet
Dim Rws() As String ' I want to get an array of all my rows
Let Rws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' I Split by the row seperator which is usualy vbCr & vbLf but note that it might sometimes be something else
Dim Cnt As Long ' I wat to loop for all the lines in the text file
For Cnt = 1 To UBound(Rws()) + 1 ' I need to use +1 because the one dimensional array returned by Split starts at 0
Dim Clms() As String ' I want an array of just the values. I can easily achieve this by spliting by the value seperator
Let Clms() = Split(Rws(Cnt - 1), vbTab, -1, vbBinaryCompare) ' I now split the row into columns using the value seperator, which in this case is vbTab
Let Ws.Range("A" & Cnt & "").Resize(1, UBound(Clms()) + 1).Value = Clms() ' I can assign my 1 dimensional array to a worksheet range, and Excel will accept it conventionally as a row of data
Next Cnt
End Sub
It is better to use text files and manipulate text files with Excel VBA if we are only looking at values
VBA Arrays
It is better to use text files and manipulate text files with Excel VBA if we are only looking at values.
Excel is very slow and inefficient if we are only looking at values
But we can make Macros for Excel using Excel VBA a little better if we use VBA arrays.
Instead of putting values in an Excel worksheet, one value at a time, we can put all values into an array, and then at the end of the macro we can put all the values into the worksheet in one go. This makes the macro quicker
Code:
Sub MakeExcelFileUsingVBAArrays()
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)
' Make array
Dim arr1(1 To 2, 1 To 2) As String
Let arr1(1, 1) = "A": Let arr1(1, 2) = "B"
Let arr1(2, 1) = "C": Let arr1(2, 2) = "D"
' Paste entire array into worksheet in one go
Let Ws.Range("A1:B2").Value = arr1()
Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFileMadeUsingVBAArrays.xlsx"
End Sub
The array, arr1() , can be considered to look like this:
But we cannot easily see this array, as it is just inside the computer in memory. But we can paste the array into a worksheet in one go using a code line like:
Let1 Ws.Range("A1:B2").Value = arr1()
For Avinash it is better to use as much manipulation of text files using VBA and VBA arrays as possible
You must not learn any VBA coding if you do not want to.
But you must try to understand the difference in text files and excel files
If you cannot or will not learn this, then there is no point in anyone trying to help you further. You will get nowhere. You will waste everybody’s time, including your own
