Excel VBAWriting a .txt file to an Array or Wrksht

[Saladsamurai]

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 can't figure out how to set the dilmeter since there is not one...

 

[John Creighto]

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 can't figure out how to set the dilmeter since there is not one...

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.

 

[Integral]

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?

 

[Saladsamurai]

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

\!\(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)\)\)\)

 

[John Creighto]

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

\!\(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)\)\)\)

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.

 

[Saladsamurai]

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.

 

[Saladsamurai]

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.

Option ExplicitSub 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:

''   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??

 

[John Creighto]

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??

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.

 

[Integral]

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.

 

[Saladsamurai]

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.

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!

 

[Integral]

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!

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.