7 {{ upvoteCount | shortNum }}
7 {{ upvoteCount | shortNum }}

Need VBA code snippet help...

by astaga
5 replies
I have a datafile with 16 char in 16 lines that I need to get into excel (16 column by 16 rows).

The sample code I have doesn't do what I want (I'm sure it is not correct code):
Sub Sample()
Dim MyData As String
Dim lineData() As String, strData() As String, myFile As String
Dim i As Long, rng As Range
myFile = Application.GetOpenFilename("RawData1 (*.txt), *.txt")

Open myFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
' Split into wholes line
lineData() = Split(MyData, vbNewLine)
Set rng = Range("A1")
' For each line
For i = 0 To UBound(lineData)
' Split the line
strData = Split(lineData(i), "|")
' Write to the sheet
rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
Next
End Sub

Appreciate any and all help please.
The datafile contents is like this (note . means a blank cell):
*notice #char (including the . adds up to 16 per line)
.2A.....5C.9..6G
6..G.5471A82.D..
.8...B2...E7...A
....6AE..4GF3.87
1E7.C8..G.46FBA9
.D.....4.E.3.CG.
.F.C.2A571B.683E
A.5..9...2.C7.D.
..3.A..9.8.4.EBD
..E6.G1D.....3..
C.....7..D1..A4.
DA.....63.7EC...
3...F.9....85G..
........EB..DF98
EB.A5..2...1....
F.9.B.8..75GA.2.

Many thanks,
Tim
#code #snippet #vba
Avatar of Unregistered
  • Profile picture of the author codexmonkey
    Hi Tim. I don't know VBA, but this looks like something I might just use a good text editor for instead of writing a script. Are you just trying to convert to csv so you can open in excel? If so, could you just find/replace all "." with ","?
    {{ DiscussionBoard.errors[11268971].message }}
  • Profile picture of the author tryinhere
    do you have a before spreadsheet and an after spreadsheet? so I can see what you want.
    Signature
    | > Choosing to go off the grid for a while to focus on family, work and life in general. Have a great 2020 < |
    {{ DiscussionBoard.errors[11269295].message }}
  • Profile picture of the author Mike Anthony
    Also don't know VBA as early on I went with C# in that area (and Pyhon for most text manipulation). Question like this however are much better for Stackoverflow.
    Signature

    {{ DiscussionBoard.errors[11269734].message }}
  • Profile picture of the author tryinhere
    Whipped this up real quick for you, see spread sheet attached you will just need to copy the data the orange section and select to run the macro, it is basic but without knowing exactly what you want this will do of sorts for you.

    Sub Sort16()
    ' Keyboard Shortcut: Ctrl+Shift+X
    '
    Application.ScreenUpdating = False
    Range("A1").Select
    Do Until ActiveCell.Value = ""
    Range("B17").Value = 1
    Do Until Range("B17").Value = 17
    ActiveCell.Offset(0, Range("c17")).Value = ActiveCell.Offset(0, 2).Value
    Range("B17").Value = Range("B17").Value + 1
    Loop
    ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = False
    End Sub

    https://screencast-o-matic.com/watch/cbl2QC2d5z

    *** EDIT *** In the last line of the sub I want you to change the code from

    Application.ScreenUpdating = False

    to

    Application.ScreenUpdating = True

    oversight / sorry
    Signature
    | > Choosing to go off the grid for a while to focus on family, work and life in general. Have a great 2020 < |
    {{ DiscussionBoard.errors[11270349].message }}
  • Profile picture of the author astaga
    #codexmonkey,
    don't understand your reply?
    If I replace all "." (a period) with "," (a comma) that defines/separates where blanks are to go (as a cell entry), but I'll have problems in places that have a character/value in them, since there are no commas to define these being a cell entry.

    #tryinhere,
    Thank you for your code.
    Yes, that does the job beautifully.

    Regards,
    Tim
    {{ DiscussionBoard.errors[11271965].message }}
Avatar of Unregistered

Trending Topics