# Excel VBAWriting a .txt file to an Array or Wrksht

1. Aug 4, 2009

Go To Post #7 for new Question

So let's that I have a .txt file called input.txt that simply contains the following:

abc+123

I want to load input.txt using VBA into a 1-dim array called MyArray. I want each character to be an element of the array. That is a is MyArray(1), c is MyArray(3) "+" is MyArray(4) etc.

I am not sure about whitespace. If it is easier, whitespace could be considered a character.

Any ideas?

Edit: ALternatively, each charecter could got to a cell...I just cant figure out how to set the dilmeter since there is not one...

Last edited: Aug 4, 2009
2. Aug 4, 2009

### John Creighto

If, you need a delimiter then you are probably using some kind of split function which is not what you are looking for. Try a different text function.

3. Aug 4, 2009

### Integral

Staff Emeritus
Where are you have problems? Opening and reading the file or dealing with the string after it is read?

VB help has lots of information, look up "read from file" for help on opening and reading.

You can read in file contents 1 character at time or line at a time. Can you tell us more about where you are with VB? How familiar are you with programming in general?

4. Aug 4, 2009

Okay. I Got it.

I am trying to create a Tool in VBA that reads the nonsense that you get when you save a file from Mathematica as .txt and rewrites it into a format that excel can more readily use.

I am having difficulty "decoding" Mathematica's syntax. For example:

This is what I have as an expression:

and this is how Mathematica outputs it to a .txt file
Code (Text):
\!$$G\/K - J\/K - \(H\ \((\(-B$$\ G\
M + D\ F\ G\ M + B\ J\ M - D\ F\ J\ M - A\ B\ F\ N1 + A\ B\ D\ F\ N1 \
- B\^2\ G\ N1 + B\ C\ G\ N1 + B\ F\ G\ N1 - C\ D\ F\ G\ N1 + B\^2\ J\ N1 - B\ \
C\ J\ N1 - B\ F\ J\ N1 + C\ D\ F\ J\ N1 - B\ F\ M\ N2 + B\ D\ F\ M\ N2)\)\)\/\
$$K\ \((B\ D\ F\ M - D\ F\^2\ M - B\ H\ M + D\ F\ H\ M + B\ L\ M - D\ F\ L\ M \ + B\^2\ D\ F\ N1 - B\ C\ D\ F\ N1 - B\ F\^2\ N1 + C\ D\ F\^2\ N1 - B\^2\ H\ \ N1 + B\ C\ H\ N1 + B\ F\ H\ N1 - C\ D\ F\ H\ N1 + B\^2\ L\ N1 - B\ C\ L\ N1 - \ B\ F\ L\ N1 + C\ D\ F\ L\ N1)$$\)\)

5. Aug 4, 2009

### John Creighto

It looks tricky. I'm wondering if this is the best approach. I'm not sure what capabilities mathematical has but maple I belie writes you choose to write an expression as latex or mathml. I'm wondering if the processing of the file should be done in mathamatica instead of externally though visual basic. However, I guess this wouldn't work so well, if you were trying to read other peoples files and you didn't have access to Mathamatica.

6. Aug 4, 2009

It's not tooo bad..... It uses a "\" before any kind of operation except for + and - as well as a space-"\" for a line continuation.

It's just the seemingly arbitrary use of parenthesis.

7. Aug 4, 2009

Okay New Question

Here is the code I am using:

This writes each character of the text file into a cell in the first column of my worksheet.
Code (Text):
Option Explicit

Sub OpenFile()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Text As String
Dim NumberCharacters As Integer
Dim MyArray() As String

'***************************************************************

'Clear ActiveSheet

Sheets("Sheet1").Range("1:65536").ClearContents
Open "Z:\Casey B\Short.txt" For Input As #1

'   Fills Column with Individual Characters Read From .txt File

i = 1
Do While Not EOF(1)
Text = Input(1, #1)
Cells(i, 1) = Text
i = i + 1

Loop

Close #1

NumberCharacters = i - 1
Now here is the part that I do not understand. If I have excel output anyone of these cells to a Message Box, they show up just fine.

But if store each element from the cells in an Array like this:

Code (Text):
''   Fill a 1-Dim Array~Each Character is an element
'
'    ReDim MyArray(NumberCharacters)
'    MsgBox "MyArray contains " & _
'           UBound(MyArray) & " elements."
'
'    For j = 1 To NumberCharacters
'        MyArray(j) = Cells(j, 1)
'        j = j + 1
'
'    Next j
And I have excel output the elements to a Message Box, it will not output an exclamation point "!" or a "(" or ")" and one of the slashes.

For example: if cell 1,1 is a "!" and I store it in the first element of MyArray --> MyArray(1)=Cell(1,1)

And then I send MyArray(1) to a message box....nothing shows up...just blank space.

i want to handle everything using arrays, but can't seem to get around this??

8. Aug 4, 2009

### John Creighto

Can you show us the code where you send the array to the message box because as far as I know the input to the message box function is a string and not an array of strings.

9. Aug 4, 2009

### Integral

Staff Emeritus
You should not increment j inside of the For loop. j is your loop counter, changing it in the loop will decrease the number of times your loop executes.

10. Aug 5, 2009

Oh yeah....VBA uses "Next j" ...I keep forgetting. Thanks Here is the whole code (w/out the j+1 correction)

EDIT: Nevermind! Integral....somehow that j+1 removal has fixed my problem. It's too early :yawn: for me to see why.... but I think I get it. I think it was only storing every-other character from the Cells...which just happened to be different characters, leading me to believe that it was something unique about those characters.

Thank you guys!

11. Aug 5, 2009

### Integral

Staff Emeritus
Yep, the way you had it only the odd numbered characters would have been written to a cell. I'm glad that fixed your problem.