Need VBA code snippet help...

by 5 replies
7
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
#programming #code #snippet #vba
  • 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 ","?
    • [ 1 ] Thanks
  • do you have a before spreadsheet and an after spreadsheet? so I can see what you want.
  • 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.
    • [ 1 ] Thanks
  • 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
    • [ 1 ] Thanks
  • #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
    • [ 1 ] Thanks
Join the discussion

Next Topics on Trending Feed