Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel VBAWriting a .txt file to an Array or Wrksht

  1. Aug 4, 2009 #1
    Go To Post #7 for new Question :smile:

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


    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. jcsd
  3. Aug 4, 2009 #2
    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.
  4. Aug 4, 2009 #3


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    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?
  5. Aug 4, 2009 #4
    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)\)\)\)
  6. Aug 4, 2009 #5
    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.
  7. Aug 4, 2009 #6
    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.
  8. Aug 4, 2009 #7
    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

        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

        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??
  9. Aug 4, 2009 #8
    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.
  10. Aug 4, 2009 #9


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    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.
  11. Aug 5, 2009 #10
    Oh yeah....VBA uses "Next j" ...I keep forgetting. Thanks :smile: 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! :smile:
  12. Aug 5, 2009 #11


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    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. :approve:
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Similar Discussions: Excel VBAWriting a .txt file to an Array or Wrksht